<?php

namespace FleekDash\Services;

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

use Exception;
use RuntimeException;
use WP_Error;

class QueryOptimizationException extends RuntimeException
{
}

class QueryOptimizer
{
    private AnalyticsCacheService $cacheService;
    private PerformanceAuditService $performanceService;
    private array $indexRecommendations = [];

    const MAX_QUERY_TIME = 0.3; // 300ms soft ceiling
    const CACHE_TTL_QUERY = 600; // 10 minutes for query plans
    const CACHE_TTL_RESULT = 1800; // 30 minutes for result payloads

    // Suggested composite indexes (consult DBA before applying in production)
    const RECOMMENDED_INDEXES = [
        // Core WP tables (common filter combinations)
        'wp_posts' => [
            'idx_post_type_status_date' => 'CREATE INDEX idx_post_type_status_date ON wp_posts(post_type, post_status, post_date)'
        ],
        'wp_comments' => [
            'idx_approved_date' => 'CREATE INDEX idx_approved_date ON wp_comments(comment_approved, comment_date)'
        ],
        'wp_users' => [
            'idx_registered' => 'CREATE INDEX idx_registered ON wp_users(user_registered)'
        ],
        'wp_fleekdash_pageviews' => [
            'idx_date_user' => 'CREATE INDEX idx_date_user ON wp_fleekdash_pageviews(date, user_id)',
            'idx_post_date' => 'CREATE INDEX idx_post_date ON wp_fleekdash_pageviews(post_id, date)',
            'idx_session_date' => 'CREATE INDEX idx_session_date ON wp_fleekdash_pageviews(session_id, date)'
        ],
        'wp_fleekdash_sessions' => [
            'idx_user_date' => 'CREATE INDEX idx_user_date ON wp_fleekdash_sessions(user_id, created_at)',
            'idx_ip_date' => 'CREATE INDEX idx_ip_date ON wp_fleekdash_sessions(ip_address, created_at)'
        ],
        'wp_fleekdash_analytics_daily' => [
            'idx_date_metric' => 'CREATE INDEX idx_date_metric ON wp_fleekdash_analytics_daily(date, metric_name)',
            'idx_metric_value' => 'CREATE INDEX idx_metric_value ON wp_fleekdash_analytics_daily(metric_name, metric_value)'
        ],
        'wp_fleekdash_site_growth_events' => [
            'idx_event_type' => 'CREATE INDEX idx_event_type ON wp_fleekdash_site_growth_events(event_type, last_occurrence_at)',
            'idx_is_active' => 'CREATE INDEX idx_is_active ON wp_fleekdash_site_growth_events(is_active, total_occurrences)'
        ],
        'wp_fleekdash_site_growth_scores' => [
            'idx_period_date' => 'CREATE INDEX idx_period_date ON wp_fleekdash_site_growth_scores(period_type, period_date)',
            'idx_total_points' => 'CREATE INDEX idx_total_points ON wp_fleekdash_site_growth_scores(total_points, period_date)'
        ]
    ];

    public function __construct()
    {
        $this->cacheService = new AnalyticsCacheService();
        $this->performanceService = new PerformanceAuditService();
        // Important: do not run heavy DB analysis in the constructor.
        // Analysis will be performed lazily when needed.

        // Schedule automatic index analysis/creation in background
        \add_action('init', function () {
            if (!\wp_next_scheduled('fleekdash_optimize_indexes')) {
                \wp_schedule_event(time() + 120, 'daily', 'fleekdash_optimize_indexes');
            }
        });

        // Background job: analyze and create missing recommended indexes
        \add_action('fleekdash_optimize_indexes', function () {
            try {
                // Compute recommendations then create missing ones
                $this->analyzeExistingIndexes();
                $result = $this->createRecommendedIndexes();

                fleekdash_log_info('FleekDash Index Optimizer: created=' . json_encode($result['created']) . ' errors=' . json_encode($result['errors']));
            } catch (\Throwable $e) {
                fleekdash_log_error('FleekDash Index Optimizer error: ' . $e->getMessage());
            }
        });
    }

    /**
     * Inspect existing indexes and queue CREATE statements for gaps.
     */
    public function analyzeExistingIndexes(): void
    {
        global $wpdb;

        $recommended_with_prefix = $this->getRecommendedIndexesWithPrefix();
        $tables = array_keys($recommended_with_prefix);

        foreach ($tables as $table) {
            // Skip if table does not exist (graceful fallback)
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
            if (!$table_exists) {
                continue;
            }

            // Ensure associative arrays to safely use array_column
            $existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`", ARRAY_A);
            $existing_index_names = is_array($existing_indexes) ? array_column($existing_indexes, 'Key_name') : [];

            foreach ($recommended_with_prefix[$table] as $index_name => $create_statement) {
                if (!in_array($index_name, $existing_index_names, true)) {
                    $this->indexRecommendations[] = [
                        'table' => $table,
                        'index' => $index_name,
                        'statement' => $create_statement,
                        'priority' => $this->calculateIndexPriority($table)
                    ];
                }
            }
        }

        // Highest priority CREATE statements first
        usort($this->indexRecommendations, function ($a, $b) {
            return $b['priority'] - $a['priority'];
        });
    }

    /**
     * Numeric priority for scheduling index creation jobs.
     */
    private function calculateIndexPriority(string $table): int
    {
        $priorities = [
            'wp_fleekdash_pageviews' => 100, // Highest traffic table in FleekDash analytics
            'wp_fleekdash_sessions' => 90,
            'wp_fleekdash_analytics_daily' => 80,
            'wp_fleekdash_site_growth_events' => 70,
            'wp_fleekdash_site_growth_scores' => 60
        ];

        return $priorities[$table] ?? 50;
    }

    /**
     * Execute SQL with transient caching and lightweight rewriting.
     */
    public function optimizeQuery(string $query, array $params = [], ?string $cache_key = null): array
    {
        $start_time = microtime(true);

        // Derive cache key when caller omits it
        if (!$cache_key) {
            $cache_key = $this->generateQueryCacheKey($query, $params);
        }

        // Short-circuit on warm transient
        $cached_result = \get_transient($cache_key);
        if ($cached_result !== false) {
            $this->performanceService->trackCache(true);
            return $cached_result;
        } else {
            $this->performanceService->trackCache(false);
        }

        // Run rewritten SQL
        $optimized_query = $this->rewriteQuery($query, $params);
        $result = $this->executeOptimizedQuery($optimized_query);

        // Store result payload
        \set_transient($cache_key, $result, self::CACHE_TTL_RESULT);

        // Track endpoint timing for diagnostics
        $this->performanceService->trackEndpoint('query_optimizer_optimize_query', $start_time);

        return $result;
    }

    /**
     * Apply conservative SQL transforms (LIMIT, hints, WHERE reorder).
     */
    private function rewriteQuery(string $query, array $params): string
    {
        // Strip SQL block comments
        $query = preg_replace('/\/\*.*?\*\//s', '', $query);

        // Append LIMIT when missing
        if (!preg_match('/\bLIMIT\s+\d+/i', $query)) {
            $limit = $params['limit'] ?? 1000;
            $query .= " LIMIT {$limit}";
        }

        // Optional index hint for hot tables
        if (preg_match('/ORDER BY\s+[^,\s]+/i', $query) && strpos($query, 'wp_fleekdash_pageviews') !== false) {
            // Hint matching analytics indexes
            $query = str_replace('FROM wp_fleekdash_pageviews', 'FROM wp_fleekdash_pageviews USE INDEX (idx_date_user)', $query);
        }

        // Reorder WHERE for index-friendly predicates
        $query = $this->optimizeWhereClause($query);

        return $query;
    }

    /**
     * Reorder WHERE fragments to prefer indexed columns.
     */
    private function optimizeWhereClause(string $query): string
    {
        // Split predicates into indexed vs other
        if (preg_match('/WHERE\s+(.+)(?:\s+ORDER BY|\s+GROUP BY|\s+LIMIT|$)/is', $query, $matches)) {
            $where_clause = $matches[1];
            $conditions = explode('AND', $where_clause);

            // Indexed predicates first
            $indexed_conditions = [];
            $other_conditions = [];

            foreach ($conditions as $condition) {
                $condition = trim($condition);
                if (preg_match('/(date|user_id|post_id|session_id|metric_name)\s*[=<>]/i', $condition)) {
                    $indexed_conditions[] = $condition;
                } else {
                    $other_conditions[] = $condition;
                }
            }

            $optimized_where = implode(' AND ', array_merge($indexed_conditions, $other_conditions));
            $query = str_replace($where_clause, $optimized_where, $query);
        }

        return $query;
    }

    /**
     * Run SQL via $wpdb with SQL_NO_CACHE prefix (diagnostics).
     */
    private function executeOptimizedQuery(string $query): array
    {
        global $wpdb;

        // Force cold read for profiling (SQL_NO_CACHE)
        $query = $this->injectSqlNoCacheHint($query);

        $result = $wpdb->get_results($query);

        if ($wpdb->last_error) {
            throw new QueryOptimizationException("Query error: " . $wpdb->last_error);
        }

        return $result;
    }

    /**
     * Inject SQL_NO_CACHE for SELECT queries without assuming exact casing/spacing.
     */
    private function injectSqlNoCacheHint(string $query): string
    {
        $optimizedQuery = $query;
        if (\preg_match('/^\s*select\s+sql_no_cache\b/i', $query) === 1) {
            return $optimizedQuery;
        }

        if (\preg_match('/^\s*select\b/i', $query) === 1) {
            $optimizedQuery = (string) \preg_replace('/^\s*select\b\s*/i', 'SELECT SQL_NO_CACHE ', $query, 1);
        }

        return $optimizedQuery;
    }

    /**
     * Build deterministic cache key for a query + params.
     */
    private function generateQueryCacheKey(string $query, array $params): string
    {
        $query_hash = md5($query . serialize($params));
        return "fleekdash_qo_optimized_query_{$query_hash}";
    }

    /**
     * Apply queued CREATE INDEX statements.
     */
    public function createRecommendedIndexes(): array
    {
        global $wpdb;

        $created_indexes = [];
        $errors = [];

        // Ensure recommendations are computed
        if (empty($this->indexRecommendations)) {
            $this->analyzeExistingIndexes();
        }

        foreach ($this->indexRecommendations as $recommendation) {
            try {
                $wpdb->query($recommendation['statement']);
                if ($wpdb->last_error) {
                    $errors[] = "Failed to create index {$recommendation['index']}: " . $wpdb->last_error;
                } else {
                    $created_indexes[] = $recommendation['index'];
                }
            } catch (Exception $e) {
                $errors[] = "Exception while creating index {$recommendation['index']}: " . $e->getMessage();
            }
        }

        return [
            'created' => $created_indexes,
            'errors' => $errors,
            'total_recommended' => count($this->indexRecommendations)
        ];
    }

    /**
     * Run heuristics on a single SQL string.
     */
    public function analyzeQueryPerformance(string $query): array
    {
        return [
            'query' => $query,
            'estimated_cost' => $this->estimateQueryCost($query),
            'recommendations' => $this->getQueryRecommendations($query),
            'index_usage' => $this->analyzeIndexUsage($query)
        ];
    }

    /**
     * Rough numeric cost estimate (higher = heavier).
     */
    private function estimateQueryCost(string $query): int
    {
        $cost = 0;

        // Heuristic complexity scoring
        if (preg_match('/JOIN/i', $query)) {
            $cost += 50;
        }
        if (preg_match('/GROUP BY/i', $query)) {
            $cost += 30;
        }
        if (preg_match('/ORDER BY/i', $query)) {
            $cost += 20;
        }
        if (preg_match('/WHERE/i', $query)) {
            $cost += 10;
        }

        // Penalize large analytics tables
        if (preg_match('/wp_fleekdash_pageviews/i', $query)) {
            $cost += 40;
        }
        if (preg_match('/wp_fleekdash_sessions/i', $query)) {
            $cost += 30;
        }
        if (preg_match('/wp_fleekdash_analytics_daily/i', $query)) {
            $cost += 25;
        }

        return $cost;
    }

    /**
     * Human-readable recommendations for ad-hoc SQL review.
     */
    private function getQueryRecommendations(string $query): array
    {
        $recommendations = [];

        if (!preg_match('/LIMIT/i', $query)) {
            $recommendations[] = "Add LIMIT to cap result set size";
        }

        if (preg_match('/SELECT \*/', $query)) {
            $recommendations[] = "List explicit columns instead of SELECT *";
        }

        if (preg_match('/ORDER BY.*DESC/i', $query) && !preg_match('/INDEX/i', $query)) {
            $recommendations[] = "Consider an index that supports ORDER BY";
        }

        return $recommendations;
    }

    /**
     * Map known index definitions against tables referenced in SQL.
     */
    private function analyzeIndexUsage(string $query): array
    {
        $index_usage = [];

        foreach (self::RECOMMENDED_INDEXES as $table => $indexes) {
            if (strpos($query, $table) !== false) {
                foreach ($indexes as $index_name => $create_statement) {
                    $index_usage[$index_name] = [
                        'table' => $table,
                        'recommended' => true,
                        'created' => false // Populated by background index job
                    ];
                }
            }
        }

        return $index_usage;
    }

    /**
     * Aggregated metrics for admin diagnostics.
     */
    public function getOptimizationStats(): array
    {
        // Lazily compute recommendations once
        if (empty($this->indexRecommendations)) {
            $this->analyzeExistingIndexes();
        }

        return [
            'cache_hit_rate' => $this->performanceService->getCacheHitRate(),
            'avg_query_time' => $this->performanceService->getAverageQueryTime(),
            'slow_queries' => $this->performanceService->getSlowQueries(),
            'recommended_indexes' => count($this->indexRecommendations),
            'created_indexes' => $this->getCreatedIndexesCount()
        ];
    }

    /**
     * Count existing indexes on recommended tables.
     */
    private function getCreatedIndexesCount(): int
    {
        global $wpdb;

        $count = 0;
        $recommended_with_prefix = $this->getRecommendedIndexesWithPrefix();
        foreach (array_keys($recommended_with_prefix) as $table) {
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
            if (!$table_exists) {
                continue;
            }
            $indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`", ARRAY_A);
            if (is_array($indexes)) {
                $count += count($indexes);
            }
        }

        return $count;
    }

    /**
     * Build recommended indexes map using the current DB prefix.
     */
    private function getRecommendedIndexesWithPrefix(): array
    {
        global $wpdb;
        $pref = $wpdb->prefix;

        $with_prefix = [];
        foreach (self::RECOMMENDED_INDEXES as $table => $indexes) {
            // Replace leading wp_ with actual prefix if needed
            $prefixed_table = preg_replace('/^wp_/', $pref, $table);
            $with_prefix[$prefixed_table] = [];
            foreach ($indexes as $index_name => $create_statement) {
                // Replace table name inside CREATE INDEX statement as well
                $prefixed_statement = str_replace($table, $prefixed_table, $create_statement);
                $with_prefix[$prefixed_table][$index_name] = $prefixed_statement;
            }
        }

        return $with_prefix;
    }
}
