<?php

namespace FleekDash\Services\GrowthAchievements;

// Load debug helpers
if (!function_exists('fleekdash_log_error')) {
    require_once FLEEKDASH_PLUGIN_DIR . 'includes/Core/debug-helpers.php';
}

use FleekDash\Services\GrowthAchievements\GrowthAchievementsCalculator;

/**
 * Growth Achievements Dashboard
 *
 * Handles dashboard data aggregation and formatting
 * (v2.2) Uses WordPress-native $wpdb queries instead of Eloquent
 *
 * @package FleekDash\Services\GrowthAchievements
 * @since V2.2
 */
class GrowthAchievementsDashboard
{
    /**
     * Cache for achievements dashboard to prevent repeated database queries
     */
    private static $achievements_cache = [];

    /**
     * Maximum cache age in seconds (5 minutes)
     */
    private const CACHE_TTL = 300;

    /**
     * Get achievements dashboard data with caching
     * (v2.2) Converted from Eloquent to WordPress-native
     *
     * @return array Comprehensive dashboard data
     */
    public static function getDashboardData()
    {
        $cache_key = 'achievements_dashboard_' . date('Y-m-d-H');

        // Check cache first
        if (isset(self::$achievements_cache[$cache_key])) {
            $cached = self::$achievements_cache[$cache_key];
            if ((time() - $cached['timestamp']) < self::CACHE_TTL) {
                return $cached['data'];
            }
        }

        try {
            global $wpdb;
            $table = $wpdb->prefix . 'fleekdash_site_growth_achievements';

            $dashboard_data = [
                'summary' => self::getDashboardSummary($table),
                'categories' => [],
                'recent_achievements' => self::getRecentAchievements(7, 5),
                'close_to_completion' => self::getCloseToCompletion(5),
                'monthly_progress' => self::getCurrentMonthProgress()
            ];

            // Get achievements by category
            $categories = self::getCategories();
            foreach ($categories as $category_key => $category_meta) {
                $achievements = self::getByCategory($category_key, 10);

                $dashboard_data['categories'][$category_key] = [
                    'meta' => $category_meta,
                    'achievements' => array_map(function($achievement) {
                        return [
                            'id' => $achievement['id'],
                            'name' => $achievement['achievement_name'],
                            'description' => $achievement['description'],
                            'target_value' => $achievement['target_value'],
                            'current_record' => $achievement['current_record'],
                            'progress_percentage' => $achievement['target_value'] > 0
                                ? min(100, round(($achievement['current_record'] / $achievement['target_value']) * 100, 1))
                                : 0,
                            'is_achieved' => $achievement['is_achieved'],
                            'achieved_at' => $achievement['achieved_at'],
                            'times_achieved' => $achievement['times_achieved'],
                            'icon' => $achievement['achievement_icon'],
                            'color' => $achievement['achievement_color'],
                            'event_type' => $achievement['event_type'],
                            'event_config' => json_decode($achievement['event_config'], true)
                        ];
                    }, $achievements)
                ];
            }

            // Cache the result
            self::$achievements_cache[$cache_key] = [
                'data' => $dashboard_data,
                'timestamp' => time()
            ];

            return $dashboard_data;

        } catch (\Exception $e) {
            fleekdash_log_error("FleekDash: Error getting achievements dashboard data: " . $e->getMessage());
            return [
                'summary' => ['total' => 0, 'completed' => 0, 'active' => 0, 'avg_progress' => 0],
                'categories' => [],
                'recent_achievements' => [],
                'close_to_completion' => [],
                'monthly_progress' => []
            ];
        }
    }

    /**
     * Get dashboard summary statistics
     * (v2.2) WordPress-native $wpdb query
     *
     * @param string $table Table name
     * @return array Summary statistics
     */
    private static function getDashboardSummary($table)
    {
        global $wpdb;

        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE is_active = 1");
        $completed = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE is_achieved = 1 AND is_active = 1");

        $avg_progress = $wpdb->get_var(
            "SELECT AVG(
                CASE
                    WHEN target_value > 0
                    THEN (current_record / target_value) * 100
                    ELSE 0
                END
            ) FROM {$table} WHERE is_active = 1"
        );

        return [
            'total' => $total,
            'completed' => $completed,
            'active' => $total - $completed,
            'avg_progress' => round((float)$avg_progress, 1)
        ];
    }

    /**
     * Get achievements by category
     * (v2.2) WordPress-native $wpdb query
     *
     * @param string $category Category key
     * @param int $limit Maximum number of achievements
     * @return array Achievements
     */
    private static function getByCategory($category, $limit = 10)
    {
        global $wpdb;
        $table = $wpdb->prefix . 'fleekdash_site_growth_achievements';

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table}
             WHERE category = %s AND is_active = 1
             ORDER BY achievement_name ASC
             LIMIT %d",
            $category,
            $limit
        ), ARRAY_A);

        return $results ?: [];
    }

    /**
     * Get recent achievements with rich data
     * (v2.2) WordPress-native $wpdb query
     *
     * @param int $days Number of days to look back
     * @param int $limit Maximum number of achievements
     * @return array Recent achievements
     */
    public static function getRecentAchievements($days = 7, $limit = 5)
    {
        global $wpdb;
        $table = $wpdb->prefix . 'fleekdash_site_growth_achievements';

        $date_limit = date('Y-m-d H:i:s', strtotime("-{$days} days"));

        $achievements = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table}
             WHERE achieved_at IS NOT NULL
             AND achieved_at >= %s
             ORDER BY achieved_at DESC
             LIMIT %d",
            $date_limit,
            $limit
        ), ARRAY_A);

        return array_map(function($achievement) {
            return [
                'id' => $achievement['id'],
                'name' => $achievement['achievement_name'],
                'description' => $achievement['description'],
                'category' => $achievement['category'],
                'achieved_at' => $achievement['achieved_at'],
                'value_achieved' => $achievement['current_record'],
                'target_was' => $achievement['target_value'],
                'icon' => $achievement['achievement_icon'],
                'color' => $achievement['achievement_color']
            ];
        }, $achievements ?: []);
    }

    /**
     * Get achievements close to completion
     * (v2.2) WordPress-native $wpdb query
     *
     * @param int $limit Maximum number of achievements
     * @return array Close to completion achievements
     */
    private static function getCloseToCompletion($limit = 5)
    {
        global $wpdb;
        $table = $wpdb->prefix . 'fleekdash_site_growth_achievements';

        // Get achievements that are 80%+ complete but not achieved yet
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table}
             WHERE is_active = 1
             AND is_achieved = 0
             AND target_value > 0
             AND (current_record / target_value) >= 0.8
             ORDER BY (current_record / target_value) DESC
             LIMIT %d",
            $limit
        ), ARRAY_A);

        return $results ?: [];
    }

    /**
     * Get current month progress for all active achievements
     * (v2.2) WordPress-native $wpdb query + Calculator service
     *
     * @return array Monthly progress data
     */
    private static function getCurrentMonthProgress()
    {
        global $wpdb;
        $table = $wpdb->prefix . 'fleekdash_site_growth_achievements';

        $month_start = date('Y-m-01 00:00:00');
        $month_end = date('Y-m-t 23:59:59');

        // V2.2: Direct $wpdb query instead of Eloquent
        $achievements = $wpdb->get_results(
            "SELECT * FROM {$table}
             WHERE achievement_type = 'event_record'
             AND is_active = 1",
            ARRAY_A
        );

        $progress = [];

        foreach ($achievements as $achievement) {
            try {
                // V2.2: Use Calculator service
                $monthly_value = GrowthAchievementsCalculator::calculateMonthlyValue(
                    $achievement['event_type'],
                    json_decode($achievement['event_config'], true) ?: [],
                    $month_start,
                    $month_end
                );

                $progress[] = [
                    'id' => $achievement['id'],
                    'name' => $achievement['achievement_name'],
                    'current_month_value' => $monthly_value,
                    'target_value' => $achievement['target_value'],
                    'progress_percentage' => $achievement['target_value'] > 0
                        ? min(100, round(($monthly_value / $achievement['target_value']) * 100, 1))
                        : 0,
                    'is_on_track' => $monthly_value >= ($achievement['target_value'] * 0.8),
                    'category' => $achievement['category']
                ];

            } catch (\Exception $e) {
                fleekdash_log_error("FleekDash: Error calculating progress for achievement {$achievement['id']}: " . $e->getMessage());
            }
        }

        // Sort by progress percentage desc
        usort($progress, function($a, $b) {
            return $b['progress_percentage'] <=> $a['progress_percentage'];
        });

        return $progress;
    }

    /**
     * Get achievement categories
     *
     * @return array Categories with metadata
     */
    private static function getCategories()
    {
        return [
            'growth' => [
                'name' => 'Growth Milestones',
                'description' => 'Achievements related to site growth',
                'icon' => 'trending-up',
                'color' => '#10b981'
            ],
            'content' => [
                'name' => 'Content Creation',
                'description' => 'Achievements for creating content',
                'icon' => 'edit',
                'color' => '#3b82f6'
            ],
            'engagement' => [
                'name' => 'Community Engagement',
                'description' => 'Achievements for user engagement',
                'icon' => 'users',
                'color' => '#f59e0b'
            ],
            'commerce' => [
                'name' => 'E-commerce Success',
                'description' => 'Achievements for sales and products',
                'icon' => 'shopping-cart',
                'color' => '#8b5cf6'
            ]
        ];
    }

    /**
     * Clear dashboard cache
     *
     * @return void
     */
    public static function clearDashboardCache()
    {
        foreach (array_keys(self::$achievements_cache) as $key) {
            if (strpos($key, 'achievements_dashboard') === 0) {
                unset(self::$achievements_cache[$key]);
            }
        }
    }

    /**
     * Clear all cache
     *
     * @return void
     */
    public static function clearAllCache()
    {
        self::$achievements_cache = [];
    }
}
