<?php
// Blog Functions for Shubank
// Contains all blog-related database operations and utility functions

/**
 * Get a single blog post by ID
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @return array|false Blog post data or false if not found
 */
function getBlogById($connection, $blog_id) {
    $blog_id = (int)$blog_id;
    $query = "SELECT *, 'Admin' as author FROM blogs WHERE id = $blog_id AND status = 'published'";
    $result = mysqli_query($connection, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        return mysqli_fetch_assoc($result);
    }
    return false;
}

/**
 * Get a single blog post by slug
 * @param mysqli $connection Database connection
 * @param string $slug Blog post slug
 * @return array|false Blog post data or false if not found
 */
function getBlogBySlug($connection, $slug) {
    $slug = mysqli_real_escape_string($connection, $slug);
    $query = "SELECT *, 'Admin' as author FROM blogs WHERE slug = '$slug' AND status = 'published'";
    $result = mysqli_query($connection, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        return mysqli_fetch_assoc($result);
    }
    return false;
}

/**
 * Get all published blog posts with pagination
 * @param mysqli $connection Database connection
 * @param int $limit Number of posts per page
 * @param int $offset Starting offset
 * @param string $category Filter by category (optional)
 * @param string $search Search term (optional)
 * @return array Array of blog posts
 */
function getAllBlogs($connection, $limit = 10, $offset = 0, $category = '', $search = '') {
    $where_conditions = ["status = 'published'"];
    
    if (!empty($category)) {
        $category = mysqli_real_escape_string($connection, $category);
        $where_conditions[] = "category = '$category'";
    }
    
    if (!empty($search)) {
        $search = mysqli_real_escape_string($connection, $search);
        $where_conditions[] = "(title LIKE '%$search%' OR content LIKE '%$search%' OR tags LIKE '%$search%')";
    }
    
    $where_clause = implode(' AND ', $where_conditions);
    
    $query = "SELECT id, title, slug, excerpt, content, featured_image, category, tags, status, views, likes, created_at, 'Admin' as author 
              FROM blogs 
              WHERE $where_clause 
              ORDER BY created_at DESC 
              LIMIT $limit OFFSET $offset";
    
    $result = mysqli_query($connection, $query);
    $blogs = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $blogs[] = $row;
        }
    }
    
    return $blogs;
}

/**
 * Get all blogs for admin (including drafts)
 * @param mysqli $connection Database connection
 * @param int $limit Number of blogs to fetch
 * @param int $offset Starting offset
 * @param string $category Filter by category (optional)
 * @param string $search Search term (optional)
 * @return array Array of all blog posts
 */
function getAllBlogsAdmin($connection, $limit = 50, $offset = 0, $category = '', $search = '') {
    try {
        $where_conditions = ["1=1"]; // No status filter for admin
        
        if (!empty($category)) {
            $category = mysqli_real_escape_string($connection, $category);
            $where_conditions[] = "category = '$category'";
        }
        
        if (!empty($search)) {
            $search = mysqli_real_escape_string($connection, $search);
            $where_conditions[] = "(title LIKE '%$search%' OR content LIKE '%$search%' OR tags LIKE '%$search%')";
        }
        
        $where_clause = implode(' AND ', $where_conditions);
        
        $query = "SELECT id, title, slug, excerpt, featured_image, category, tags, status, views, likes, created_at, updated_at 
                  FROM blogs 
                  WHERE $where_clause 
                  ORDER BY created_at DESC 
                  LIMIT $limit OFFSET $offset";
        
        $result = mysqli_query($connection, $query);
        $blogs = [];
        
        if ($result) {
            while ($row = mysqli_fetch_assoc($result)) {
                // Ensure all required keys exist with default values
                $blog = array_merge([
                    'id' => 0,
                    'title' => 'Untitled',
                    'slug' => '',
                    'excerpt' => '',
                    'featured_image' => '',
                    'category' => 'general',
                    'tags' => '',
                    'status' => 'draft',
                    'views' => 0,
                    'likes' => 0,
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s')
                ], $row);
                
                $blogs[] = $blog;
            }
        }
        
        return $blogs;
    } catch (Exception $e) {
        error_log("Error in getAllBlogsAdmin: " . $e->getMessage());
        return [];
    }
}

/**
 * Get total count of published blogs
 * @param mysqli $connection Database connection
 * @param string $category Filter by category (optional)
 * @param string $search Search term (optional)
 * @return int Total number of blogs
 */
function getTotalBlogsCount($connection, $category = '', $search = '') {
    $where_conditions = ["status = 'published'"];
    
    if (!empty($category)) {
        $category = mysqli_real_escape_string($connection, $category);
        $where_conditions[] = "category = '$category'";
    }
    
    if (!empty($search)) {
        $search = mysqli_real_escape_string($connection, $search);
        $where_conditions[] = "(title LIKE '%$search%' OR content LIKE '%$search%' OR tags LIKE '%$search%')";
    }
    
    $where_clause = implode(' AND ', $where_conditions);
    
    $query = "SELECT COUNT(*) as total FROM blogs WHERE $where_clause";
    $result = mysqli_query($connection, $query);
    
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        return (int)$row['total'];
    }
    
    return 0;
}

/**
 * Get related blog posts by category
 * @param mysqli $connection Database connection
 * @param int $blog_id Current blog ID to exclude
 * @param string $category Category to match
 * @param int $limit Number of related posts to return
 * @return array Array of related blog posts
 */
function getRelatedBlogs($connection, $blog_id, $category, $limit = 3) {
    $blog_id = (int)$blog_id;
    $category = mysqli_real_escape_string($connection, $category);
    
    $query = "SELECT id, title, slug, excerpt, featured_image, category, created_at, 'Admin' as author 
              FROM blogs 
              WHERE id != $blog_id 
              AND category = '$category' 
              AND status = 'published' 
              ORDER BY created_at DESC 
              LIMIT $limit";
    
    $result = mysqli_query($connection, $query);
    $related_blogs = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $related_blogs[] = $row;
        }
    }
    
    return $related_blogs;
}

/**
 * Get featured/latest blog posts
 * @param mysqli $connection Database connection
 * @param int $limit Number of featured posts
 * @return array Array of featured blog posts
 */
function getFeaturedBlogs($connection, $limit = 3) {
    $query = "SELECT id, title, slug, excerpt, featured_image, category, views, created_at, 'Admin' as author 
              FROM blogs 
              WHERE status = 'published' 
              ORDER BY views DESC, created_at DESC 
              LIMIT $limit";
    
    $result = mysqli_query($connection, $query);
    $featured_blogs = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $featured_blogs[] = $row;
        }
    }
    
    return $featured_blogs;
}

/**
 * Get all blog categories
 * @param mysqli $connection Database connection
 * @return array Array of categories with post counts
 */
function getBlogCategories($connection) {
    $query = "SELECT category, COUNT(*) as count 
              FROM blogs 
              WHERE status = 'published' 
              GROUP BY category 
              ORDER BY count DESC";
    
    $result = mysqli_query($connection, $query);
    $categories = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $categories[] = $row;
        }
    }
    
    return $categories;
}

/**
 * Increment view count for a blog post
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @return bool Success status
 */
function incrementViews($connection, $blog_id) {
    $blog_id = (int)$blog_id;
    
    $query = "UPDATE blogs SET views = views + 1 WHERE id = $blog_id";
    return mysqli_query($connection, $query);
}

/**
 * Increment like count for a blog post
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @return bool Success status
 */
function incrementLikes($connection, $blog_id) {
    $blog_id = (int)$blog_id;
    
    $query = "UPDATE blogs SET likes = likes + 1 WHERE id = $blog_id";
    return mysqli_query($connection, $query);
}

/**
 * Get popular blog posts based on views
 * @param mysqli $connection Database connection
 * @param int $limit Number of popular posts
 * @return array Array of popular blog posts
 */
function getPopularBlogs($connection, $limit = 5) {
    $query = "SELECT id, title, slug, views, created_at, 'Admin' as author 
              FROM blogs 
              WHERE status = 'published' 
              ORDER BY views DESC 
              LIMIT $limit";
    
    $result = mysqli_query($connection, $query);
    $popular_blogs = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $popular_blogs[] = $row;
        }
    }
    
    return $popular_blogs;
}

/**
 * Get recent blog posts
 * @param mysqli $connection Database connection
 * @param int $limit Number of recent posts
 * @return array Array of recent blog posts
 */
function getRecentBlogs($connection, $limit = 5) {
    $query = "SELECT id, title, slug, created_at, 'Admin' as author 
              FROM blogs 
              WHERE status = 'published' 
              ORDER BY created_at DESC 
              LIMIT $limit";
    
    $result = mysqli_query($connection, $query);
    $recent_blogs = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $recent_blogs[] = $row;
        }
    }
    
    return $recent_blogs;
}

/**
 * Search blog posts
 * @param mysqli $connection Database connection
 * @param string $search_term Search term
 * @param int $limit Number of results
 * @param int $offset Starting offset
 * @return array Array of search results
 */
function searchBlogs($connection, $search_term, $limit = 10, $offset = 0) {
    $search_term = mysqli_real_escape_string($connection, $search_term);
    
    $query = "SELECT id, title, slug, excerpt, featured_image, category, created_at, 'Admin' as author 
              FROM blogs 
              WHERE status = 'published' 
              AND (title LIKE '%$search_term%' OR content LIKE '%$search_term%' OR tags LIKE '%$search_term%' OR excerpt LIKE '%$search_term%')
              ORDER BY 
                CASE 
                    WHEN title LIKE '%$search_term%' THEN 1
                    WHEN excerpt LIKE '%$search_term%' THEN 2
                    WHEN tags LIKE '%$search_term%' THEN 3
                    ELSE 4
                END,
                created_at DESC
              LIMIT $limit OFFSET $offset";
    
    $result = mysqli_query($connection, $query);
    $search_results = [];
    
    if ($result) {
        while ($row = mysqli_fetch_assoc($result)) {
            $search_results[] = $row;
        }
    }
    
    return $search_results;
}

/**
 * Create a new blog post
 * @param mysqli $connection Database connection
 * @param array $blog_data Blog post data
 * @return int|false Blog ID if successful, false if failed
 */
function createBlog($connection, $blog_data) {
    $title = mysqli_real_escape_string($connection, $blog_data['title']);
    $slug = mysqli_real_escape_string($connection, $blog_data['slug']);
    $content = mysqli_real_escape_string($connection, $blog_data['content']);
    $excerpt = mysqli_real_escape_string($connection, $blog_data['excerpt']);
    $featured_image = mysqli_real_escape_string($connection, $blog_data['featured_image'] ?? '');
    $category = mysqli_real_escape_string($connection, $blog_data['category']);
    $tags = mysqli_real_escape_string($connection, $blog_data['tags']);
    $status = mysqli_real_escape_string($connection, $blog_data['status']);
    $meta_title = mysqli_real_escape_string($connection, $blog_data['meta_title'] ?? '');
    $meta_description = mysqli_real_escape_string($connection, $blog_data['meta_description'] ?? '');
    
    $query = "INSERT INTO blogs (title, slug, content, excerpt, featured_image, category, tags, status, meta_title, meta_description, created_at, updated_at) 
              VALUES ('$title', '$slug', '$content', '$excerpt', '$featured_image', '$category', '$tags', '$status', '$meta_title', '$meta_description', NOW(), NOW())";
    
    if (mysqli_query($connection, $query)) {
        return mysqli_insert_id($connection);
    }
    
    return false;
}

/**
 * Update an existing blog post
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @param array $blog_data Blog post data
 * @return bool Success status
 */
function updateBlog($connection, $blog_id, $blog_data) {
    $blog_id = (int)$blog_id;
    $title = mysqli_real_escape_string($connection, $blog_data['title']);
    
    // Generate slug if not provided
    if (empty($blog_data['slug'])) {
        $base_slug = generateSlug($blog_data['title']);
        $slug = getUniqueSlug($connection, $base_slug, $blog_id);
    } else {
        $slug = mysqli_real_escape_string($connection, $blog_data['slug']);
    }
    
    $content = mysqli_real_escape_string($connection, $blog_data['content']);
    $excerpt = mysqli_real_escape_string($connection, $blog_data['excerpt']);
    $category = mysqli_real_escape_string($connection, $blog_data['category']);
    $tags = mysqli_real_escape_string($connection, $blog_data['tags']);
    $status = mysqli_real_escape_string($connection, $blog_data['status']);
    $meta_title = mysqli_real_escape_string($connection, $blog_data['meta_title'] ?? '');
    $meta_description = mysqli_real_escape_string($connection, $blog_data['meta_description'] ?? '');
    
    $query = "UPDATE blogs SET 
              title = '$title', 
              slug = '$slug', 
              content = '$content', 
              excerpt = '$excerpt', 
              category = '$category', 
              tags = '$tags', 
              status = '$status',
              meta_title = '$meta_title',
              meta_description = '$meta_description',
              updated_at = NOW()";
    
    // Add featured image update if provided
    if (isset($blog_data['featured_image']) && !empty($blog_data['featured_image'])) {
        $featured_image = mysqli_real_escape_string($connection, $blog_data['featured_image']);
        $query .= ", featured_image = '$featured_image'";
    }
    
    $query .= " WHERE id = $blog_id";
    
    return mysqli_query($connection, $query);
}

/**
 * Delete a blog post
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @return bool Success status
 */
function deleteBlog($connection, $blog_id) {
    $blog_id = (int)$blog_id;
    
    // Get featured image before deletion for cleanup
    $query = "SELECT featured_image FROM blogs WHERE id = $blog_id";
    $result = mysqli_query($connection, $query);
    
    if ($result && $row = mysqli_fetch_assoc($result)) {
        $featured_image = $row['featured_image'];
        
        // Delete the blog post
        $delete_query = "DELETE FROM blogs WHERE id = $blog_id";
        if (mysqli_query($connection, $delete_query)) {
            // Clean up image file if exists
            if (!empty($featured_image) && file_exists("../uploads/blog/$featured_image")) {
                unlink("../uploads/blog/$featured_image");
            }
            return true;
        }
    }
    
    return false;
}

/**
 * Generate SEO-friendly slug from title
 * @param string $title Blog post title
 * @return string SEO-friendly slug
 */
function generateSlug($title) {
    // Convert to lowercase
    $slug = strtolower($title);
    
    // Replace spaces with hyphens
    $slug = str_replace(' ', '-', $slug);
    
    // Remove special characters except hyphens
    $slug = preg_replace('/[^a-z0-9\-]/', '', $slug);
    
    // Remove multiple consecutive hyphens
    $slug = preg_replace('/-+/', '-', $slug);
    
    // Remove hyphens from start and end
    $slug = trim($slug, '-');
    
    return $slug;
}

/**
 * Check if slug already exists
 * @param mysqli $connection Database connection
 * @param string $slug Slug to check
 * @param int $exclude_id Blog ID to exclude from check (for updates)
 * @return bool True if slug exists, false otherwise
 */
function slugExists($connection, $slug, $exclude_id = 0) {
    $slug = mysqli_real_escape_string($connection, $slug);
    $exclude_id = (int)$exclude_id;
    
    $query = "SELECT COUNT(*) as count FROM blogs WHERE slug = '$slug'";
    if ($exclude_id > 0) {
        $query .= " AND id != $exclude_id";
    }
    
    $result = mysqli_query($connection, $query);
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        return $row['count'] > 0;
    }
    
    return false;
}

/**
 * Get unique slug by appending number if needed
 * @param mysqli $connection Database connection
 * @param string $base_slug Base slug
 * @param int $exclude_id Blog ID to exclude from check
 * @return string Unique slug
 */
function getUniqueSlug($connection, $base_slug, $exclude_id = 0) {
    $slug = $base_slug;
    $counter = 1;
    
    while (slugExists($connection, $slug, $exclude_id)) {
        $slug = $base_slug . '-' . $counter;
        $counter++;
    }
    
    return $slug;
}

/**
 * Get blog statistics
 * @param mysqli $connection Database connection
 * @return array Statistics array
 */
function getBlogStats($connection) {
    $stats = [];
    
    // Total published blogs
    $query = "SELECT COUNT(*) as total FROM blogs WHERE status = 'published'";
    $result = mysqli_query($connection, $query);
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $stats['total_published'] = $row['total'] ?? 0;
        $stats['total_blogs'] = $row['total'] ?? 0; // Add this for admin dashboard compatibility
    } else {
        $stats['total_published'] = 0;
        $stats['total_blogs'] = 0;
    }
    
    // Total draft blogs
    $query = "SELECT COUNT(*) as total FROM blogs WHERE status = 'draft'";
    $result = mysqli_query($connection, $query);
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $stats['total_drafts'] = $row['total'] ?? 0;
    } else {
        $stats['total_drafts'] = 0;
    }
    
    // Total views
    $query = "SELECT SUM(views) as total_views FROM blogs WHERE status = 'published'";
    $result = mysqli_query($connection, $query);
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $stats['total_views'] = $row['total_views'] ?? 0;
    } else {
        $stats['total_views'] = 0;
    }
    
    // Total likes
    $query = "SELECT SUM(likes) as total_likes FROM blogs WHERE status = 'published'";
    $result = mysqli_query($connection, $query);
    if ($result) {
        $row = mysqli_fetch_assoc($result);
        $stats['total_likes'] = $row['total_likes'] ?? 0;
    } else {
        $stats['total_likes'] = 0;
    }
    
    // Most popular blog
    $query = "SELECT title, views FROM blogs WHERE status = 'published' ORDER BY views DESC LIMIT 1";
    $result = mysqli_query($connection, $query);
    if ($result && mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        $stats['most_popular'] = $row;
    } else {
        $stats['most_popular'] = ['title' => 'No blogs yet', 'views' => 0];
    }
    
    return $stats;
}

/**
 * Format blog excerpt with proper length
 * @param string $content Full blog content
 * @param int $length Maximum length
 * @return string Formatted excerpt
 */
function formatExcerpt($content, $length = 150) {
    // Strip HTML tags
    $excerpt = strip_tags($content);
    
    // Trim to specified length
    if (strlen($excerpt) > $length) {
        $excerpt = substr($excerpt, 0, $length);
        
        // Cut at last complete word
        $last_space = strrpos($excerpt, ' ');
        if ($last_space !== false) {
            $excerpt = substr($excerpt, 0, $last_space);
        }
        
        $excerpt .= '...';
    }
    
    return $excerpt;
}

/**
 * Validate blog data
 * @param array $blog_data Blog post data
 * @return array Validation errors (empty if valid)
 */
function validateBlogData($blog_data) {
    $errors = [];
    
    // Required fields
    if (empty($blog_data['title'])) {
        $errors[] = 'Title is required';
    }
    
    if (empty($blog_data['content'])) {
        $errors[] = 'Content is required';
    }
    
    if (empty($blog_data['category'])) {
        $errors[] = 'Category is required';
    }
    
    if (empty($blog_data['excerpt'])) {
        $errors[] = 'Excerpt is required';
    }
    
    // Length validations
    if (!empty($blog_data['title']) && strlen($blog_data['title']) > 200) {
        $errors[] = 'Title must be less than 200 characters';
    }
    
    if (!empty($blog_data['excerpt']) && strlen($blog_data['excerpt']) > 300) {
        $errors[] = 'Excerpt must be less than 300 characters';
    }
    
    if (!empty($blog_data['meta_title']) && strlen($blog_data['meta_title']) > 60) {
        $errors[] = 'Meta title must be less than 60 characters';
    }
    
    if (!empty($blog_data['meta_description']) && strlen($blog_data['meta_description']) > 160) {
        $errors[] = 'Meta description must be less than 160 characters';
    }
    
    return $errors;
}

/**
 * Save a new blog post (wrapper for createBlog with slug generation)
 * @param mysqli $connection Database connection
 * @param array $blog_data Blog post data
 * @return int|false Blog ID if successful, false if failed
 */
function saveBlogPost($connection, $blog_data) {
    // Validate the blog data
    $errors = validateBlogData($blog_data);
    if (!empty($errors)) {
        error_log("Blog validation errors: " . implode(", ", $errors));
        return false;
    }
    
    // Generate slug from title if not provided
    if (empty($blog_data['slug'])) {
        $base_slug = generateSlug($blog_data['title']);
        $blog_data['slug'] = getUniqueSlug($connection, $base_slug);
    }
    
    // If no excerpt provided, generate from content
    if (empty($blog_data['excerpt']) && !empty($blog_data['content'])) {
        $blog_data['excerpt'] = formatExcerpt($blog_data['content'], 150);
    }
    
    return createBlog($connection, $blog_data);
}

/**
 * Handle image upload for blog posts
 * @param array $file $_FILES array element
 * @return array Result with success status and filename/error message
 */
function handleImageUpload($file) {
    $upload_dir = '../uploads/blog/';
    $allowed_types = ['image/jpeg', 'image/jpg', 'image/png', 'image/gif', 'image/webp'];
    $max_size = 5 * 1024 * 1024; // 5MB
    
    // Check if upload directory exists, create if not
    if (!file_exists($upload_dir)) {
        if (!mkdir($upload_dir, 0777, true)) {
            return ['success' => false, 'message' => 'Failed to create upload directory'];
        }
    }
    
    // Validate file
    if ($file['error'] !== UPLOAD_ERR_OK) {
        return ['success' => false, 'message' => 'File upload error: ' . $file['error']];
    }
    
    if ($file['size'] > $max_size) {
        return ['success' => false, 'message' => 'File too large. Maximum size: 5MB'];
    }
    
    // Check file type
    $file_info = getimagesize($file['tmp_name']);
    if (!$file_info || !in_array($file_info['mime'], $allowed_types)) {
        return ['success' => false, 'message' => 'Invalid file type. Only JPEG, PNG, GIF, and WebP images are allowed'];
    }
    
    // Generate unique filename
    $file_extension = pathinfo($file['name'], PATHINFO_EXTENSION);
    $filename = 'blog_' . time() . '_' . rand(1000, 9999) . '.' . strtolower($file_extension);
    $target_path = $upload_dir . $filename;
    
    // Move uploaded file
    if (move_uploaded_file($file['tmp_name'], $target_path)) {
        return ['success' => true, 'filename' => $filename];
    } else {
        return ['success' => false, 'message' => 'Failed to move uploaded file'];
    }
}

/**
 * Get blog by ID for admin (includes all statuses)
 * @param mysqli $connection Database connection
 * @param int $blog_id Blog post ID
 * @return array|false Blog post data or false if not found
 */
function getBlogByIdAdmin($connection, $blog_id) {
    $blog_id = (int)$blog_id;
    $query = "SELECT * FROM blogs WHERE id = $blog_id";
    $result = mysqli_query($connection, $query);
    
    if ($result && mysqli_num_rows($result) > 0) {
        return mysqli_fetch_assoc($result);
    }
    return false;
}
?>