<?php

namespace FleekDash\Core;

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

/**
 * Database Optimizer - Optimize database queries and reduce load
 * Provides query optimization, connection pooling, and query caching
 */
class DatabaseOptimizer
{
    /**
     * Query cache for repeated queries
     */
    private static $query_cache = [];
    private static $initialized = false;
    private static $slow_query_threshold = 1.0; // 1 second
    private static $performance_mode = false;

    /**
     * Initialize database optimizer
     */
    public static function init()
    {
        if (self::$initialized) {
            return;
        }

        self::$initialized = true;

        // Only enable performance monitoring in debug mode
        if (defined('WP_DEBUG') && WP_DEBUG) {
            \add_filter('query', [self::class, 'monitor_slow_queries'], 10, 1);
        }

        // Optimize WordPress queries only once per session
        \add_action('init', [self::class, 'optimize_wordpress_queries'], 1);

        // Add database cleanup on shutdown
        \add_action('shutdown', [self::class, 'cleanup_database_connections']);
    }

    /**
     * Monitor slow queries (only in debug mode)
     *
     * @param string $query SQL query
     * @return string
     */
    public static function monitor_slow_queries($query)
    {
        $start_time = microtime(true);

        // Let the query execute
        $result = $query;

        $execution_time = microtime(true) - $start_time;

        // Log slow queries
        if ($execution_time > self::$slow_query_threshold) {
            fleekdash_log_debug("SLOW QUERY DETECTED: {$execution_time}s - {$query}");
        }

        return $result;
    }

    /**
     * Optimize WordPress queries (runs only once per session)
     */
    public static function optimize_wordpress_queries()
    {
        global $wpdb;

        // Prevent multiple executions
        if (self::$performance_mode) {
            return;
        }

        try {
            // Only set SQL mode if not already set to avoid overhead
            $current_mode = $wpdb->get_var("SELECT @@sql_mode");
            if (strpos($current_mode, 'STRICT_TRANS_TABLES') === false) {
                $wpdb->query("SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO'");
            }

            // Set performance mode flag
            self::$performance_mode = true;
        } catch (\Exception $e) {
            // Log error but don't break the application
            fleekdash_log_error("FleekDash DatabaseOptimizer error: " . $e->getMessage());
        }
    }

    /**
     * Cleanup database connections
     */
    public static function cleanup_database_connections()
    {
        // Clear query cache
        self::$query_cache = [];
    }

    /**
     * Get cached query result
     *
     * @param string $query SQL query
     * @param string $cache_key Cache key
     * @param int $expiration Cache expiration in seconds
     * @return mixed
     */
    public static function get_cached_query($query, $cache_key = null, $expiration = 3600)
    {
        if (!$cache_key) {
            $cache_key = md5($query);
        }

        // Check memory cache first
        if (isset(self::$query_cache[$cache_key])) {
            return self::$query_cache[$cache_key];
        }

        // Check WordPress cache
        $cached_result = \wp_cache_get($cache_key, 'fleekdash_queries');
        if ($cached_result !== false) {
            self::$query_cache[$cache_key] = $cached_result;
            return $cached_result;
        }

        // Execute query and cache result
        global $wpdb;
        $result = $wpdb->get_results($query);

        // Cache the result
        \wp_cache_set($cache_key, $result, 'fleekdash_queries', $expiration);
        self::$query_cache[$cache_key] = $result;

        return $result;
    }

    /**
     * Batch database operations for better performance
     *
     * @param array $operations Array of database operations
     * @return array Results of operations
     */
    public static function batch_operations($operations)
    {
        global $wpdb;
        $results = [];

        // Start transaction for better performance
        $wpdb->query('START TRANSACTION');

        try {
            foreach ($operations as $operation) {
                $type = $operation['type'] ?? 'select';
                $query = $operation['query'] ?? '';
                $params = $operation['params'] ?? [];

                switch ($type) {
                    case 'select':
                        if (!empty($params)) {
                            $results[] = $wpdb->get_results($wpdb->prepare($query, $params));
                        } else {
                            $results[] = $wpdb->get_results($query);
                        }
                        break;
                    case 'insert':
                        if (!empty($params)) {
                            $results[] = $wpdb->insert($operation['table'], $operation['data'], $operation['format'] ?? null);
                        } else {
                            $results[] = $wpdb->query($query);
                        }
                        break;
                    case 'update':
                        if (!empty($params)) {
                            $results[] = $wpdb->update($operation['table'], $operation['data'], $operation['where'], $operation['format'] ?? null, $operation['where_format'] ?? null);
                        } else {
                            $results[] = $wpdb->query($query);
                        }
                        break;
                    case 'delete':
                        if (!empty($params)) {
                            $results[] = $wpdb->delete($operation['table'], $operation['where'], $operation['where_format'] ?? null);
                        } else {
                            $results[] = $wpdb->query($query);
                        }
                        break;
                }
            }

            // Commit transaction
            $wpdb->query('COMMIT');
        } catch (\Exception $e) {
            // Rollback on error
            $wpdb->query('ROLLBACK');
            fleekdash_log_error("Database batch operation failed: " . $e->getMessage());
            throw $e;
        }

        return $results;
    }

    /**
     * Optimize specific queries for FleekDash
     *
     * @param string $query_type Type of query to optimize
     * @param array $params Query parameters
     * @return mixed
     */
    public static function optimized_query($query_type, $params = [])
    {
        global $wpdb;

        switch ($query_type) {
            case 'get_posts_with_meta':
                return self::get_posts_with_meta_optimized($params);

            case 'get_user_stats':
                return self::get_user_stats_optimized($params);

            case 'get_integration_data':
                return self::get_integration_data_optimized($params);

            default:
                return false;
        }
    }

    /**
     * Optimized query for posts with meta
     *
     * @param array $params Query parameters
     * @return array
     */
    private static function get_posts_with_meta_optimized($params)
    {
        global $wpdb;

        $post_type = $params['post_type'] ?? 'post';
        $limit = $params['limit'] ?? 10;
        $offset = $params['offset'] ?? 0;

        // Use JOIN instead of separate queries for better performance
        $query = $wpdb->prepare(
            "SELECT p.*, pm.meta_key, pm.meta_value
             FROM {$wpdb->posts} p
             LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
             WHERE p.post_type = %s
             AND p.post_status = 'publish'
             ORDER BY p.post_date DESC
             LIMIT %d OFFSET %d",
            $post_type,
            $limit,
            $offset,
        );

        return self::get_cached_query($query, "posts_meta_{$post_type}_{$limit}_{$offset}", 1800);
    }

    /**
     * Optimized query for user statistics
     *
     * @param array $params Query parameters
     * @return array
     */
    private static function get_user_stats_optimized($params)
    {
        global $wpdb;

        $user_id = $params['user_id'] ?? \get_current_user_id();

        // Single query to get all user statistics
        $query = $wpdb->prepare(
            "SELECT
                COUNT(DISTINCT p.ID) as total_posts,
                COUNT(DISTINCT c.comment_ID) as total_comments,
                MAX(p.post_date) as last_post_date,
                AVG(LENGTH(p.post_content)) as avg_content_length
             FROM {$wpdb->users} u
             LEFT JOIN {$wpdb->posts} p ON u.ID = p.post_author
             LEFT JOIN {$wpdb->comments} c ON u.ID = c.user_id
             WHERE u.ID = %d
             AND p.post_status = 'publish'",
            $user_id,
        );

        return self::get_cached_query($query, "user_stats_{$user_id}", 3600);
    }

    /**
     * Optimized query for integration data
     *
     * @param array $params Query parameters
     * @return array
     */
    private static function get_integration_data_optimized($params)
    {
        global $wpdb;

        $integration_id = $params['integration_id'] ?? '';

        // Get integration data with options in single query
        $query = $wpdb->prepare(
            "SELECT option_name, option_value
             FROM {$wpdb->options}
             WHERE option_name LIKE %s
             ORDER BY option_name",
            "fleekdash_integration_{$integration_id}_%",
        );

        return self::get_cached_query($query, "integration_data_{$integration_id}", 1800);
    }

    /**
     * Get database performance statistics
     *
     * @return array
     */
    public static function get_performance_stats()
    {
        global $wpdb;

        $stats = [
            'query_cache_size' => count(self::$query_cache),
            'slow_queries' => 0,
            'total_queries' => $wpdb->num_queries ?? 0,
            'database_size' => 0,
            'table_stats' => []
        ];

        // Get database size
        $db_size_query = "SELECT
            ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size in MB'
            FROM information_schema.tables
            WHERE table_schema = DATABASE()";

        $db_size = $wpdb->get_var($db_size_query);
        $stats['database_size'] = $db_size;

        // Get table statistics
        $tables = ['posts', 'postmeta', 'users', 'usermeta', 'options', 'comments'];
        foreach ($tables as $table) {
            $table_name = $wpdb->prefix . $table;
            $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");
            $stats['table_stats'][$table] = $count;
        }

        return $stats;
    }

    /**
     * Optimize database tables
     *
     * @param array $tables Tables to optimize
     * @return bool
     */
    public static function optimize_tables($tables = [])
    {
        global $wpdb;

        if (empty($tables)) {
            $tables = ['posts', 'postmeta', 'users', 'usermeta', 'options', 'comments'];
        }

        foreach ($tables as $table) {
            $table_name = $wpdb->prefix . $table;
            $wpdb->query("OPTIMIZE TABLE {$table_name}");
        }

        return true;
    }

    /**
     * Clean up old data for better performance
     *
     * @param int $days_old Days old data to clean
     * @return bool
     */
    public static function cleanup_old_data($days_old = 30)
    {
        global $wpdb;

        // Clean up old transients
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->options}
                 WHERE option_name LIKE '_transient_%'
                 AND option_value < %d",
                time() - ($days_old * DAY_IN_SECONDS)
            ),
        );

        // Clean up old post revisions
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->posts}
                 WHERE post_type = 'revision'
                 AND post_date < %s",
                date('Y-m-d H:i:s', time() - ($days_old * DAY_IN_SECONDS)),
            ),
        );

        return true;
    }
}
