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

/**
 * Dashboard Widgets Class for Blogibot AI Blog
 * Handles all dashboard widget data and functionality
 */
class Blog82AI_Dashboard_Widgets {
    
    /**
     * Get dashboard metrics data
     */
    public static function get_metrics() {
        global $wpdb;
        
        // Get total blogs generated (posts with meta indicating AI generation)
        $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 p.post_status IN ('publish', 'draft', 'pending') 
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        // Get blogs this week
        $week_start = date('Y-m-d H:i:s', strtotime('-7 days'));
        $blogs_this_week = $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_status IN ('publish', 'draft', 'pending') 
            AND p.post_date >= %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", $week_start));
        
        // Get blogs this month
        $month_start = date('Y-m-01 00:00:00');
        $blogs_this_month = $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_status IN ('publish', 'draft', 'pending') 
            AND p.post_date >= %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", $month_start));
        
        // Get previous week for comparison
        $prev_week_start = date('Y-m-d H:i:s', strtotime('-14 days'));
        $prev_week_end = date('Y-m-d H:i:s', strtotime('-7 days'));
        $blogs_prev_week = $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_status IN ('publish', 'draft', 'pending') 
            AND p.post_date >= %s AND p.post_date < %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", $prev_week_start, $prev_week_end));
        
        // Get previous month for comparison
        $prev_month_start = date('Y-m-01 00:00:00', strtotime('-1 month'));
        $prev_month_end = date('Y-m-t 23:59:59', strtotime('-1 month'));
        $blogs_prev_month = $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_status IN ('publish', 'draft', 'pending') 
            AND p.post_date >= %s AND p.post_date <= %s
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ", $prev_month_start, $prev_month_end));
        
        // Calculate SEO score average (from Yoast meta if available)
        $seo_scores = $wpdb->get_results("
            SELECT pm.meta_value 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            LEFT JOIN {$wpdb->postmeta} ai_meta ON p.ID = ai_meta.post_id 
            WHERE p.post_type = 'post' 
            AND p.post_status = 'publish' 
            AND pm.meta_key = '_yoast_wpseo_content_score'
            AND (ai_meta.meta_key = '_blog82ai_generated' OR ai_meta.meta_key = '_blogibot_generated')
        ");
        
        $avg_seo_score = 0;
        if (!empty($seo_scores)) {
            $total_score = 0;
            $count = 0;
            foreach ($seo_scores as $score) {
                if (is_numeric($score->meta_value)) {
                    $total_score += intval($score->meta_value);
                    $count++;
                }
            }
            if ($count > 0) {
                $avg_seo_score = round($total_score / $count);
            }
        }
        
        // Get active schedules count (from options or custom table if exists)
        $active_schedules = 0;
        $paused_schedules = 0;
        
        // Check if schedule table exists
        $schedule_table = $wpdb->prefix . 'blog82ai_schedules';
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$schedule_table'") == $schedule_table;
        
        if ($table_exists) {
            $active_schedules = $wpdb->get_var("SELECT COUNT(*) FROM $schedule_table WHERE status = 'active'");
            $paused_schedules = $wpdb->get_var("SELECT COUNT(*) FROM $schedule_table WHERE status = 'paused'");
        } else {
            // Fallback: some installs store schedules in the options table (legacy behavior)
            // Use the ScheduledBlog Widgets helper to compute counts from the option
            if (file_exists(WP_PLUGIN_DIR . '/blogibot/src/AIBlog/ScheduledBlog/ScheduledBlogWidgets.php')) {
                require_once WP_PLUGIN_DIR . '/blogibot/src/AIBlog/ScheduledBlog/ScheduledBlogWidgets.php';
                $stats = Blog82AI_ScheduledBlog_Widgets::get_schedule_stats();
                $active_schedules = isset($stats['active_schedules']) ? intval($stats['active_schedules']) : 0;
                $paused_schedules = isset($stats['paused_schedules']) ? intval($stats['paused_schedules']) : 0;
            } else {
                // As a last resort, attempt to read option directly
                $scheduled = get_option('blog82ai_scheduled_blogs', []);
                $active_schedules = 0;
                $paused_schedules = 0;
                if (is_array($scheduled)) {
                    foreach ($scheduled as $s) {
                        $st = $s['status'] ?? 'active';
                        if ($st === 'active') $active_schedules++;
                        if ($st === 'paused') $paused_schedules++;
                    }
                }
            }
        }
        
        return [
            'total_blogs' => intval($total_blogs) ?: 0,
            'blogs_this_week' => intval($blogs_this_week) ?: 0,
            'blogs_this_month' => intval($blogs_this_month) ?: 0,
            'blogs_prev_week' => intval($blogs_prev_week) ?: 0,
            'blogs_prev_month' => intval($blogs_prev_month) ?: 0,
            'seo_score_avg' => $avg_seo_score ?: 87, // Default fallback
            'active_schedules' => intval($active_schedules) ?: 0,
            'paused_schedules' => intval($paused_schedules) ?: 0,
        ];
    }
    
    /**
     * Get categories with most blogs
     */
    public static function get_top_categories() {
        global $wpdb;
        
        $categories = $wpdb->get_results("
            SELECT t.name, t.slug, COUNT(p.ID) as post_count
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
            WHERE p.post_type = 'post'
            AND p.post_status = 'publish'
            AND tt.taxonomy = 'category'
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
            GROUP BY t.term_id
            ORDER BY post_count DESC
            LIMIT 5
        ");
        
        // If no AI-generated posts, get regular categories
        if (empty($categories)) {
            $categories = $wpdb->get_results("
                SELECT t.name, t.slug, COUNT(p.ID) as post_count
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
                WHERE p.post_type = 'post'
                AND p.post_status = 'publish'
                AND tt.taxonomy = 'category'
                GROUP BY t.term_id
                ORDER BY post_count DESC
                LIMIT 5
            ");
        }
        
        return $categories ?: [];
    }
    
    /**
     * Get most used focus keyphrases
     */
    public static function get_top_keyphrases() {
        global $wpdb;
        
        // Get focus keywords from Yoast or our custom meta
        $keyphrases = $wpdb->get_results("
            SELECT pm.meta_value, COUNT(*) as usage_count
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
            LEFT JOIN {$wpdb->postmeta} ai_meta ON p.ID = ai_meta.post_id 
            WHERE p.post_type = 'post'
            AND p.post_status = 'publish'
            AND pm.meta_key IN ('_yoast_wpseo_focuskw', '_blog82ai_focus_keywords', '_blogibot_focus_keywords')
            AND pm.meta_value != ''
            AND (ai_meta.meta_key = '_blog82ai_generated' OR ai_meta.meta_key = '_blogibot_generated')
            GROUP BY pm.meta_value
            ORDER BY usage_count DESC
            LIMIT 5
        ");
        
        // If no AI-generated keyphrases, get regular ones
        if (empty($keyphrases)) {
            $keyphrases = $wpdb->get_results("
                SELECT pm.meta_value, COUNT(*) as usage_count
                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 IN ('_yoast_wpseo_focuskw', '_blog82ai_focus_keywords', '_blogibot_focus_keywords')
                AND pm.meta_value != ''
                GROUP BY pm.meta_value
                ORDER BY usage_count DESC
                LIMIT 5
            ");
        }
        
        return $keyphrases ?: [];
    }
    
    /**
     * Get system status information
     */
    public static function get_system_status() {
        // Load license manager to get actual license data
        require_once WP_PLUGIN_DIR . '/blogibot/src/License/LicenseManager.php';
        
        // Check actual license data instead of non-existent options
        $license_data = Blog82AI_License_Manager::get_license_data();
        $is_valid = Blog82AI_License_Manager::is_license_valid();
        
        // API connection is active if we have a valid license with license key
        $api_status = ($is_valid && !empty($license_data['license_key'])) ? 'active' : 'inactive';
        
        // Domain is verified if we have a valid license with a domain
        $domain_status = ($is_valid && !empty($license_data['domain'])) ? 'verified' : 'unverified';
        
        return [
            'api_connection' => $api_status,
            'domain_status' => $domain_status
        ];
    }
    
    /**
     * Get recent blog posts for activity feed
     */
    public static function get_recent_blogs($limit = 5) {
        global $wpdb;
        
        $posts = $wpdb->get_results($wpdb->prepare("
            SELECT DISTINCT p.ID, p.post_title, p.post_date, p.post_status,
                (
                    SELECT t.name
                    FROM {$wpdb->term_relationships} tr2
                    INNER JOIN {$wpdb->term_taxonomy} tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
                    INNER JOIN {$wpdb->terms} t ON tt2.term_id = t.term_id
                    WHERE tr2.object_id = p.ID AND tt2.taxonomy = 'category'
                    LIMIT 1
                ) as category_name,
                gen_meta.meta_value as generated_at
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
            LEFT JOIN {$wpdb->postmeta} gen_meta ON p.ID = gen_meta.post_id AND gen_meta.meta_key = '_blog82ai_generated_at'
            WHERE p.post_type = 'post'
            AND p.post_status IN ('publish', 'draft', 'pending')
            AND (pm.meta_key IS NOT NULL)
            ORDER BY (CASE WHEN gen_meta.meta_value IS NOT NULL AND gen_meta.meta_value != '' THEN STR_TO_DATE(gen_meta.meta_value, '%%Y-%%m-%%d %%H:%%i:%%s') ELSE p.post_date END) DESC
            LIMIT %d
        ", $limit));
        
        return $posts ?: [];
    }
    
    /**
     * Format metric change indicator
     */
    public static function format_metric_change($current, $previous, $suffix = '') {
        $diff = $current - $previous;
        
        if ($diff > 0) {
            return "+{$diff}{$suffix} from last period";
        } elseif ($diff < 0) {
            return "{$diff}{$suffix} from last period";
        } else {
            return "No change from last period";
        }
    }
    
    /**
     * Get metric change class for styling
     */
    public static function get_metric_change_class($current, $previous) {
        $diff = $current - $previous;
        
        if ($diff > 0) {
            return 'positive';
        } elseif ($diff < 0) {
            return 'negative';
        } else {
            return 'neutral';
        }
    }
    
    /**
     * Create sample AI-generated posts for demonstration (if none exist)
     */
    public static function create_sample_data() {
        global $wpdb;
        
        // Check if we already have AI-generated posts
        $existing_count = $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 IN ('publish', 'draft') 
            AND (pm.meta_key = '_blog82ai_generated' OR pm.meta_key = '_blogibot_generated')
        ");
        
        if ($existing_count > 0) {
            return; // Already have sample data
        }
        
        // Create sample categories if they don't exist
        $sample_categories = [
            'AI & Technology' => 'Content about artificial intelligence and technology trends',
            'Web Development' => 'Web development tips and tutorials',
            'SEO Marketing' => 'Search engine optimization and digital marketing',
            'WordPress' => 'WordPress tutorials and best practices'
        ];
        
        $category_ids = [];
        foreach ($sample_categories as $cat_name => $cat_desc) {
            $term = get_term_by('name', $cat_name, 'category');
            if (!$term) {
                $result = wp_insert_term($cat_name, 'category', [
                    'description' => $cat_desc,
                    'slug' => sanitize_title($cat_name)
                ]);
                if (!is_wp_error($result)) {
                    $category_ids[] = $result['term_id'];
                }
            } else {
                $category_ids[] = $term->term_id;
            }
        }
        
        // Sample post data
        $sample_posts = [
            [
                'title' => 'The Future of AI in Content Creation',
                'content' => 'Artificial Intelligence is revolutionizing how we create and manage content. From automated blog writing to smart SEO optimization, AI tools are becoming essential for modern content creators...',
                'category' => 'AI & Technology',
                'focus_keyword' => 'AI content creation',
                'meta_description' => 'Discover how AI is transforming content creation with automated writing, SEO optimization, and smart content management tools.'
            ],
            [
                'title' => 'WordPress Security Best Practices 2025',
                'content' => 'Keeping your WordPress site secure is more important than ever. In this comprehensive guide, we\'ll cover the essential security measures every WordPress site owner should implement...',
                'category' => 'WordPress',
                'focus_keyword' => 'WordPress security',
                'meta_description' => 'Learn essential WordPress security best practices to protect your website from threats and vulnerabilities in 2025.'
            ],
            [
                'title' => 'Modern SEO Strategies That Actually Work',
                'content' => 'SEO is constantly evolving, and what worked yesterday might not work today. Here are the proven SEO strategies that are delivering real results in the current search landscape...',
                'category' => 'SEO Marketing',
                'focus_keyword' => 'SEO strategies 2025',
                'meta_description' => 'Discover effective SEO strategies that work in 2025. Learn modern techniques to boost your search rankings and drive organic traffic.'
            ]
        ];
        
        // Create sample posts
        foreach ($sample_posts as $index => $post_data) {
            $post_id = wp_insert_post([
                'post_title' => $post_data['title'],
                'post_content' => $post_data['content'],
                'post_status' => $index === 0 ? 'publish' : 'draft',
                'post_type' => 'post',
                'post_author' => get_current_user_id(),
                'post_date' => date('Y-m-d H:i:s', strtotime('-' . ($index + 1) . ' days'))
            ]);
            
            if ($post_id && !is_wp_error($post_id)) {
                // Mark as AI-generated
                add_post_meta($post_id, '_blog82ai_generated', true);
                add_post_meta($post_id, '_blog82ai_focus_keywords', $post_data['focus_keyword']);
                
                // Add Yoast meta if available
                add_post_meta($post_id, '_yoast_wpseo_focuskw', $post_data['focus_keyword']);
                add_post_meta($post_id, '_yoast_wpseo_metadesc', $post_data['meta_description']);
                add_post_meta($post_id, '_yoast_wpseo_content_score', rand(75, 95));
                
                // Assign category
                $category_term = get_term_by('name', $post_data['category'], 'category');
                if ($category_term) {
                    wp_set_post_terms($post_id, [$category_term->term_id], 'category');
                }
            }
        }
    }
}
