<?php
if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly

/**
 * Generated Blog History Widgets Class for Blogibot AI
 * Handles all generated blog data, statistics, and SEO analysis
 */
class Blog82AI_GeneratedBlogHistory_Widgets {
    
    /**
     * Get blog generation statistics
     */
    public static function get_blog_statistics() {
        global $wpdb;
        
        // Get all generated blogs (both scheduled and manual)
        $total_blogs = $wpdb->get_var("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        // Get published blogs
        $published_blogs = $wpdb->get_var("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_status = 'publish'
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        // Get draft blogs
        $draft_blogs = $wpdb->get_var("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_status = 'draft'
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        // Get scheduled blogs
        $scheduled_blogs = $wpdb->get_var("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_status = 'future'
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        // Calculate average SEO score
        $avg_seo_score = self::calculate_average_seo_score();
        
        // Calculate trends (compared to previous month)
        $current_month_blogs = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_date >= %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", date('Y-m-01')));
        
        $previous_month_blogs = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(DISTINCT p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_date >= %s
            AND p.post_date < %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", date('Y-m-01', strtotime('-1 month')), date('Y-m-01')));
        
        $trend_percentage = $previous_month_blogs > 0 
            ? round((($current_month_blogs - $previous_month_blogs) / $previous_month_blogs) * 100, 1)
            : 0;
        
        // Calculate publish rate
        $publish_rate = $total_blogs > 0 ? round(($published_blogs / $total_blogs) * 100) : 0;
        $draft_rate = $total_blogs > 0 ? round(($draft_blogs / $total_blogs) * 100) : 0;
        
        return [
            'total_blogs' => intval($total_blogs) ?: 0,
            'published_blogs' => intval($published_blogs) ?: 0,
            'draft_blogs' => intval($draft_blogs) ?: 0,
            'scheduled_blogs' => intval($scheduled_blogs) ?: 0,
            'average_seo_score' => $avg_seo_score,
            'trend_percentage' => $trend_percentage,
            'publish_rate' => $publish_rate,
            'draft_rate' => $draft_rate,
            'current_month_blogs' => intval($current_month_blogs) ?: 0
        ];
    }
    
    /**
     * Get all generated blogs with enhanced data
     */
    public static function get_all_generated_blogs($limit = 20, $offset = 0, $filters = []) {
        global $wpdb;
        
        $where_conditions = [];
        $where_conditions[] = "p.post_type = 'post'";
        $where_conditions[] = "(pm_generated.meta_key = '_blog82ai_generated' OR pm_generated.meta_key = '_blogibot_generated')";
        
        // Apply filters
        if (!empty($filters['category'])) {
            $where_conditions[] = $wpdb->prepare("t.slug = %s", $filters['category']);
        }
        
        if (!empty($filters['status'])) {
            $where_conditions[] = $wpdb->prepare("p.post_status = %s", $filters['status']);
        }
        
        if (!empty($filters['date_range'])) {
            $date_condition = self::get_date_range_condition($filters['date_range']);
            if ($date_condition) {
                $where_conditions[] = $date_condition;
            }
        }
        
        if (!empty($filters['search'])) {
            $search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
            $where_conditions[] = $wpdb->prepare("(p.post_title LIKE %s OR p.post_content LIKE %s)", $search_term, $search_term);
        }
        
        $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
        
        $query = "
            SELECT DISTINCT 
                p.ID,
                p.post_title,
                p.post_status,
                p.post_date,
                p.post_modified,
                t.name as category_name,
                t.slug as category_slug,
                pm_focus.meta_value as focus_keyword,
                pm_generated.meta_value as generation_method,
                pm_seo_score.meta_value as seo_score
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm_generated ON p.ID = pm_generated.post_id 
                AND (pm_generated.meta_key = '_blog82ai_generated' OR pm_generated.meta_key = '_blogibot_generated')
            LEFT JOIN {$wpdb->postmeta} pm_focus ON p.ID = pm_focus.post_id 
                AND pm_focus.meta_key = '_blog82ai_focus_keyword'
            LEFT JOIN {$wpdb->postmeta} pm_seo_score ON p.ID = pm_seo_score.post_id 
                AND pm_seo_score.meta_key = '_blog82ai_seo_score'
            LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
                AND tt.taxonomy = 'category'
            LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            {$where_clause}
            ORDER BY p.post_date DESC
            LIMIT %d OFFSET %d
        ";
        
        $results = $wpdb->get_results($wpdb->prepare($query, $limit, $offset));
        
        $enhanced_blogs = [];
        foreach ($results as $blog) {
            // Calculate or get SEO score
            $seo_score = self::get_blog_seo_score($blog->ID, $blog->seo_score);
            
            // Get focus keyword (from our meta or extract from content)
            $focus_keyword = self::get_focus_keyword($blog->ID, $blog->focus_keyword);
            
            // Get Yoast data if available
            $yoast_data = self::get_yoast_data($blog->ID);
            
            $enhanced_blogs[] = [
                'id' => $blog->ID,
                'title' => $blog->post_title,
                'status' => $blog->post_status,
                'category' => $blog->category_name ?: 'Uncategorized',
                'category_slug' => $blog->category_slug ?: 'uncategorized',
                'focus_keyword' => $focus_keyword,
                'generated_date' => $blog->post_date,
                'modified_date' => $blog->post_modified,
                'generation_method' => $blog->generation_method ?: 'manual',
                'seo_score' => $seo_score,
                'yoast_data' => $yoast_data,
                'edit_link' => get_edit_post_link($blog->ID),
                'view_link' => get_permalink($blog->ID),
                'preview_link' => get_preview_post_link($blog->ID)
            ];
        }
        
        return $enhanced_blogs;
    }
    
    /**
     * Calculate or retrieve SEO score for a blog post
     */
    private static function get_blog_seo_score($post_id, $stored_score = null) {
        // If we have a stored score, use it
        if ($stored_score !== null && is_numeric($stored_score)) {
            return intval($stored_score);
        }
        
        // Check Yoast SEO score first
        $yoast_score = get_post_meta($post_id, '_yoast_wpseo_linkdex', true);
        if ($yoast_score && is_numeric($yoast_score)) {
            // Yoast scores are typically 0-100, but displayed differently
            $converted_score = round($yoast_score);
            // Store our calculated score
            update_post_meta($post_id, '_blog82ai_seo_score', $converted_score);
            return $converted_score;
        }
        
        // Calculate our own SEO score
        $seo_score = self::calculate_internal_seo_score($post_id);
        
        // Store the calculated score
        update_post_meta($post_id, '_blog82ai_seo_score', $seo_score);
        
        return $seo_score;
    }
    
    /**
     * Calculate internal SEO score based on various factors
     */
    private static function calculate_internal_seo_score($post_id) {
        $post = get_post($post_id);
        if (!$post) return 0;
        
        $score = 0;
        $max_score = 100;
        
        // Title optimization (20 points)
        $title = $post->post_title;
        if (strlen($title) >= 30 && strlen($title) <= 60) {
            $score += 20;
        } elseif (strlen($title) >= 20 && strlen($title) <= 70) {
            $score += 15;
        } elseif (strlen($title) >= 10) {
            $score += 10;
        }
        
        // Content length (20 points)
        $content_length = str_word_count(strip_tags($post->post_content));
        if ($content_length >= 1500) {
            $score += 20;
        } elseif ($content_length >= 1000) {
            $score += 15;
        } elseif ($content_length >= 500) {
            $score += 10;
        } elseif ($content_length >= 300) {
            $score += 5;
        }
        
        // Meta description (15 points)
        $meta_desc = get_post_meta($post_id, '_yoast_wpseo_metadesc', true) ?: 
                    get_post_meta($post_id, '_blog82ai_meta_description', true);
        if ($meta_desc && strlen($meta_desc) >= 120 && strlen($meta_desc) <= 160) {
            $score += 15;
        } elseif ($meta_desc && strlen($meta_desc) >= 100) {
            $score += 10;
        }
        
        // Focus keyword presence (15 points)
        $focus_keyword = self::get_focus_keyword($post_id);
        if ($focus_keyword) {
            $title_has_keyword = stripos($title, $focus_keyword) !== false;
            $content_has_keyword = stripos($post->post_content, $focus_keyword) !== false;
            
            if ($title_has_keyword && $content_has_keyword) {
                $score += 15;
            } elseif ($title_has_keyword || $content_has_keyword) {
                $score += 10;
            }
        }
        
        // Images optimization (10 points)
        $image_count = substr_count($post->post_content, '<img');
        $has_featured_image = has_post_thumbnail($post_id);
        
        if ($has_featured_image && $image_count > 0) {
            $score += 10;
        } elseif ($has_featured_image || $image_count > 0) {
            $score += 5;
        }
        
        // Headings structure (10 points)
        $h2_count = substr_count($post->post_content, '<h2');
        $h3_count = substr_count($post->post_content, '<h3');
        
        if ($h2_count >= 2 && $h3_count >= 1) {
            $score += 10;
        } elseif ($h2_count >= 1) {
            $score += 7;
        }
        
        // Internal/External links (10 points)
        $internal_links = preg_match_all('/href=["\']https?:\/\/' . preg_quote(get_site_url(), '/') . '/', $post->post_content);
        $external_links = preg_match_all('/href=["\']https?:\/\/(?!' . preg_quote(get_site_url(), '/') . ')/', $post->post_content);
        
        if ($internal_links > 0 && $external_links > 0) {
            $score += 10;
        } elseif ($internal_links > 0 || $external_links > 0) {
            $score += 5;
        }
        
        return min($score, $max_score);
    }
    
    /**
     * Get focus keyword for a post
     */
    private static function get_focus_keyword($post_id, $stored_keyword = null) {
        // Check if we have a stored focus keyword
        if ($stored_keyword) {
            return $stored_keyword;
        }
        
        // Check our meta field
        $our_keyword = get_post_meta($post_id, '_blog82ai_focus_keyword', true);
        if ($our_keyword) {
            return $our_keyword;
        }
        
        // Check Yoast focus keyword
        $yoast_keyword = get_post_meta($post_id, '_yoast_wpseo_focuskw', true);
        if ($yoast_keyword) {
            // Store it in our field for faster access
            update_post_meta($post_id, '_blog82ai_focus_keyword', $yoast_keyword);
            return $yoast_keyword;
        }
        
        // Extract potential keywords from title and content
        $extracted_keyword = self::extract_focus_keyword($post_id);
        if ($extracted_keyword) {
            update_post_meta($post_id, '_blog82ai_focus_keyword', $extracted_keyword);
            return $extracted_keyword;
        }
        
        return '';
    }
    
    /**
     * Extract focus keyword from content analysis
     */
    private static function extract_focus_keyword($post_id) {
        $post = get_post($post_id);
        if (!$post) return '';
        
        // Simple keyword extraction - get most common 2-3 word phrases
        $text = strtolower(strip_tags($post->post_title . ' ' . $post->post_content));
        $text = preg_replace('/[^a-z0-9\s]/', ' ', $text);
        $words = array_filter(explode(' ', $text), function($word) {
            return strlen($word) > 3; // Filter out short words
        });
        
        // Count 2-word phrases
        $phrases = [];
        for ($i = 0; $i < count($words) - 1; $i++) {
            $phrase = $words[$i] . ' ' . $words[$i + 1];
            $phrases[$phrase] = ($phrases[$phrase] ?? 0) + 1;
        }
        
        // Get most common phrase
        if (!empty($phrases)) {
            arsort($phrases);
            return array_key_first($phrases);
        }
        
        return '';
    }
    
    /**
     * Get Yoast SEO data if available
     */
    private static function get_yoast_data($post_id) {
        if (!class_exists('WPSEO_Options')) {
            return null;
        }
        
        return [
            'focus_keyword' => get_post_meta($post_id, '_yoast_wpseo_focuskw', true),
            'seo_score' => get_post_meta($post_id, '_yoast_wpseo_linkdx', true),
            'readability_score' => get_post_meta($post_id, '_yoast_wpseo_content_score', true),
            'meta_description' => get_post_meta($post_id, '_yoast_wpseo_metadesc', true),
            'title' => get_post_meta($post_id, '_yoast_wpseo_title', true)
        ];
    }
    
    /**
     * Calculate average SEO score across all generated blogs
     */
    private static function calculate_average_seo_score() {
        global $wpdb;
        
        $avg_score = $wpdb->get_var("
            SELECT AVG(CAST(pm.meta_value AS UNSIGNED))
            FROM {$wpdb->posts} p
            JOIN {$wpdb->postmeta} pm_generated ON p.ID = pm_generated.post_id 
                AND (pm_generated.meta_key = '_blog82ai_generated' OR pm_generated.meta_key = '_blogibot_generated')
            JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
                AND pm.meta_key = '_blog82ai_seo_score'
            WHERE p.post_type = 'post' 
            AND pm.meta_value REGEXP '^[0-9]+$'
        ");
        
        return $avg_score ? round($avg_score) : 0;
    }
    
    /**
     * Get date range SQL condition
     */
    private static function get_date_range_condition($range) {
        global $wpdb;
        
        switch ($range) {
            case 'today':
                return $wpdb->prepare("DATE(p.post_date) = %s", date('Y-m-d'));
            case 'week':
                return $wpdb->prepare("p.post_date >= %s", date('Y-m-d', strtotime('-7 days')));
            case 'month':
                return $wpdb->prepare("p.post_date >= %s", date('Y-m-d', strtotime('-30 days')));
            case 'quarter':
                return $wpdb->prepare("p.post_date >= %s", date('Y-m-d', strtotime('-90 days')));
            default:
                return null;
        }
    }
    
    /**
     * Get total count of generated blogs for pagination
     */
    public static function get_total_blogs_count($filters = []) {
        global $wpdb;
        
        $where_conditions = [];
        $where_conditions[] = "p.post_type = 'post'";
        $where_conditions[] = "(pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')";
        
        // Apply same filters as main query
        if (!empty($filters['category'])) {
            $where_conditions[] = $wpdb->prepare("t.slug = %s", $filters['category']);
        }
        
        if (!empty($filters['status'])) {
            $where_conditions[] = $wpdb->prepare("p.post_status = %s", $filters['status']);
        }
        
        if (!empty($filters['date_range'])) {
            $date_condition = self::get_date_range_condition($filters['date_range']);
            if ($date_condition) {
                $where_conditions[] = $date_condition;
            }
        }
        
        if (!empty($filters['search'])) {
            $search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
            $where_conditions[] = $wpdb->prepare("(p.post_title LIKE %s OR p.post_content LIKE %s)", $search_term, $search_term);
        }
        
        $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
        
        $query = "
            SELECT COUNT(DISTINCT p.ID)
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
                AND tt.taxonomy = 'category'
            LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            {$where_clause}
        ";
        
        return intval($wpdb->get_var($query));
    }
    
    /**
     * Get all categories used by generated blogs
     */
    public static function get_generated_blog_categories() {
        global $wpdb;
        
        $categories = $wpdb->get_results("
            SELECT DISTINCT t.name, t.slug
            FROM {$wpdb->posts} p
            JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
                AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
            JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
                AND tt.taxonomy = 'category'
            JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE p.post_type = 'post'
            ORDER BY t.name
        ");
        
        return $categories ?: [];
    }
    
    /**
     * Mark a post as AI generated
     */
    public static function mark_as_generated($post_id, $method = 'manual', $focus_keyword = '') {
        update_post_meta($post_id, '_blog82ai_generated', $method);
        update_post_meta($post_id, '_blog82ai_generation_date', current_time('mysql'));
        
        if ($focus_keyword) {
            update_post_meta($post_id, '_blog82ai_focus_keyword', $focus_keyword);
        }
        
        // Calculate initial SEO score
        self::get_blog_seo_score($post_id);
        
        return true;
    }
    
    /**
     * Update SEO score for a post
     */
    public static function update_seo_score($post_id, $score = null) {
        if ($score === null) {
            // Recalculate score
            $score = self::calculate_internal_seo_score($post_id);
        }
        
        update_post_meta($post_id, '_blog82ai_seo_score', intval($score));
        return intval($score);
    }
}
