<?php

/**
 * Dashboard REST API Controller
 *
 * @package ProRank\SEO\Core\RestApi
 * @since   0.1.0
 */

declare(strict_types=1);

namespace ProRank\SEO\Core\RestApi;

use ProRank\SEO\Core\Config\Settings;
use ProRank\SEO\Core\ModuleManager;
use ProRank\SEO\Core\LicenseManager;
use WP_REST_Controller;
use WP_REST_Server;
use WP_REST_Request;
use WP_REST_Response;
use WP_Error;
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}


/**
 * Class DashboardController
 * 
 * Handles REST API endpoints for dashboard data
 */


class DashboardController extends WP_REST_Controller {
    /**
     * Namespace
     *
     * @var string
     */
    protected $namespace = 'prorank-seo/v1';

    /**
     * Rest base
     *
     * @var string
     */
    protected $rest_base = 'dashboard';

    /**
     * Module manager instance
     *
     * @var ModuleManager
     */
    private ModuleManager $module_manager;

    /**
     * License manager instance
     *
     * @var LicenseManager
     */
    private LicenseManager $license_manager;

    /**
     * Constructor
     *
     * @param ModuleManager  $module_manager  Module manager instance
     * @param LicenseManager $license_manager License manager instance
     */
    public function __construct(ModuleManager $module_manager, LicenseManager $license_manager) {
        $this->module_manager = $module_manager;
        $this->license_manager = $license_manager;
    }

    /**
     * Register routes
     *
     * @return void
     */
    public function register_routes(): void {
        // Get dashboard stats
        register_rest_route($this->namespace, '/' . $this->rest_base . '/stats', [
            [
                'methods'             => WP_REST_Server::READABLE,
                'callback'            => [$this, 'get_dashboard_stats'],
                'permission_callback' => [$this, 'get_stats_permissions_check'],
            ],
        ]);

        // Get health status
        register_rest_route($this->namespace, '/health/status', [
            [
                'methods'             => WP_REST_Server::READABLE,
                'callback'            => [$this, 'get_health_status'],
                'permission_callback' => [$this, 'get_stats_permissions_check'],
            ],
        ]);

        // Run health scan
        register_rest_route($this->namespace, '/health/scan', [
            [
                'methods'             => WP_REST_Server::CREATABLE,
                'callback'            => [$this, 'run_health_scan'],
                'permission_callback' => [$this, 'run_scan_permissions_check'],
            ],
        ]);
    }

    /**
     * Cache key for dashboard stats
     */
    private const CACHE_KEY = 'prorank_dashboard_stats';
    private const HISTORY_KEY = 'prorank_dashboard_stats_history';
    private const CACHE_TTL = 5 * MINUTE_IN_SECONDS;

    /**
     * Whether premium runtime is active on this site.
     */
    private function is_premium_runtime_active(): bool {
        if (defined('PRORANK_PREMIUM_VERSION')) {
            return true;
        }

        if (!function_exists('is_plugin_active')) {
            require_once ABSPATH . 'wp-admin/includes/plugin.php';
        }

        if (!function_exists('is_plugin_active')) {
            return false;
        }

        return is_plugin_active('prorank-seo-premium/prorank-seo.php')
            || is_plugin_active('prorank-seo-premium/prorank-seo-premium.php');
    }

    /**
     * Free runtime only supports local audit data.
     */
    private function can_use_cloud_audit(): bool {
        return false;
    }

    /**
     * Get dashboard statistics
     *
     * @param WP_REST_Request $request Full details about the request.
     * @return WP_REST_Response|WP_Error Response object on success, or WP_Error object on failure.
     */
    public function get_dashboard_stats($request) {
        $force_refresh = false;
        if ($request instanceof WP_REST_Request) {
            $force_refresh = (bool) $request->get_param('refresh');
        }

        $latest_audit = $this->get_latest_audit_summary();

        // Check for cached stats
        if (!$force_refresh) {
            $cached = get_transient(self::CACHE_KEY);
            if ($cached !== false && is_array($cached)) {
                $expected_version = defined('PRORANK_VERSION') ? PRORANK_VERSION : null;
                $cached_version = $cached['meta']['plugin_version'] ?? null;
                if (
                    $expected_version
                    && $cached_version === $expected_version
                    && $this->cached_dashboard_matches_latest_audit($cached, $latest_audit)
                ) {
                    return new WP_REST_Response($cached, 200);
                }
            }
        }

        // Calculate fresh stats
        $audit_payload = !empty($latest_audit) && !empty($latest_audit['id']) ? $latest_audit : null;
        $has_audit = $audit_payload !== null;
        $performance  = [
            'cache'    => $this->get_cache_overview(),
            'assets'   => $this->get_asset_overview(),
            'images'   => $this->get_image_overview(),
            'database' => $this->get_database_overview(),
            'rum'      => $this->get_rum_summary(),
        ];

        // Get analytics data (GSC/GA4) for premium users
        $analytics = $this->get_analytics_summary();

        // This is the number of published posts/pages (local, always available).
        // Keep "indexed_pages" reserved for a future Search Console integration.
        $published_pages = $this->get_indexed_pages_count();

        $current_stats = [
            // Only show score/issue/index stats after a completed audit exists.
            'seo_score'       => $has_audit ? ($latest_audit['score'] ?? null) : null,
            'indexed_pages'   => null,
            'published_pages' => $published_pages,
            'issues_count'    => $has_audit ? ($latest_audit['issues']['total'] ?? null) : null,
            'redirects_count' => $has_audit ? $this->get_redirects_count() : null,
            'audit'           => $audit_payload,
            'performance'     => $performance,
        ];

        // Get trend data
        $trends = $has_audit ? $this->calculate_trends($current_stats) : [];

        // Build full response
        $stats = array_merge($current_stats, [
            'seo_score_trend' => $trends['seo_score'] ?? null,
            'indexed_pages_trend' => $trends['indexed_pages'] ?? null,
            'issues_count_trend' => $trends['issues_count'] ?? null,
            'redirects_count_trend' => $trends['redirects_count'] ?? null,
            'recent_activity' => $this->get_recent_activity(),
            'audit' => $audit_payload,
            'performance' => $performance,
            'analytics' => $analytics,
            'license' => [
                'tier' => $this->license_manager->get_current_tier(),
                'display_name' => $this->license_manager->get_tier_display_name(
                    $this->license_manager->get_current_tier()
                ),
                'is_developer_mode' => $this->license_manager->is_developer_mode_active(),
            ],
            'meta' => [
                'plugin_version' => defined('PRORANK_VERSION') ? PRORANK_VERSION : null,
                'generated_at' => gmdate('c'),
            ],
        ]);

        // Cache the stats
        set_transient(self::CACHE_KEY, $stats, self::CACHE_TTL);

        // Store historical data for trend calculation (once per day)
        if ($has_audit) {
            $history_snapshot = [
                'seo_score' => $current_stats['seo_score'],
                'indexed_pages' => $current_stats['indexed_pages'],
                'issues_count' => $current_stats['issues_count'],
                'redirects_count' => $current_stats['redirects_count'],
            ];
            $this->maybe_store_historical_data($history_snapshot);
        }

        return new WP_REST_Response($stats, 200);
    }

    /**
     * Check whether cached dashboard data still points at the newest completed audit.
     *
     * @param array $cached       Cached dashboard payload.
     * @param array $latest_audit Fresh latest-audit summary.
     * @return bool
     */
    private function cached_dashboard_matches_latest_audit(array $cached, array $latest_audit): bool {
        $cached_audit = is_array($cached['audit'] ?? null) ? $cached['audit'] : [];

        if (empty($latest_audit)) {
            return empty($cached_audit['id']);
        }

        return (string) ($cached_audit['id'] ?? '') === (string) ($latest_audit['id'] ?? '')
            && (string) ($cached_audit['source'] ?? 'local') === (string) ($latest_audit['source'] ?? 'local');
    }

    /**
     * Calculate trends by comparing current stats to historical data
     *
     * @param array $current_stats Current statistics
     * @return array Trend percentages for each stat
     */
    private function calculate_trends(array $current_stats): array {
        $history = get_option(self::HISTORY_KEY, []);
        $trends = [];

        // Get data from 7 days ago (or closest available)
        $week_ago = strtotime('-7 days');
        $comparison_data = null;

        foreach ($history as $timestamp => $data) {
            if ($timestamp <= $week_ago) {
                $comparison_data = $data;
                break;
            }
        }

        if (!$comparison_data) {
            return $trends;
        }

        // Calculate percentage change for each stat
        foreach (['seo_score', 'indexed_pages', 'issues_count', 'redirects_count'] as $key) {
            $old = $comparison_data[$key] ?? 0;
            $new = $current_stats[$key] ?? 0;

            if ($old > 0) {
                $change = (($new - $old) / $old) * 100;
                $trends[$key] = round($change, 1);
            } elseif ($new > 0) {
                $trends[$key] = 100; // 100% increase from 0
            } else {
                $trends[$key] = null; // No change from 0
            }
        }

        return $trends;
    }

    /**
     * Store historical data for trend calculations (once per day)
     *
     * @param array $current_stats Current statistics
     * @return void
     */
    private function maybe_store_historical_data(array $current_stats): void {
        $history = get_option(self::HISTORY_KEY, []);
        $today = strtotime('today midnight');

        // Only store once per day
        if (isset($history[$today])) {
            return;
        }

        // Add today's data
        $history[$today] = $current_stats;

        // Keep only last 30 days of data
        $cutoff = strtotime('-30 days');
        $history = array_filter($history, function ($timestamp) use ($cutoff) {
            return $timestamp >= $cutoff;
        }, ARRAY_FILTER_USE_KEY);

        // Sort by timestamp descending
        krsort($history);

        update_option(self::HISTORY_KEY, $history, false);
    }

    /**
     * Get health status
     *
     * @param WP_REST_Request $request Full details about the request.
     * @return WP_REST_Response|WP_Error Response object on success, or WP_Error object on failure.
     */
    public function get_health_status($request) {
        $health_data = get_transient('prorank_health_data');
        
        if ($health_data === false) {
            // No cached data, run basic checks
            $health_data = $this->perform_basic_health_checks();
        }

        return new WP_REST_Response($health_data, 200);
    }

    /**
     * Run full health scan
     *
     * @param WP_REST_Request $request Full details about the request.
     * @return WP_REST_Response|WP_Error Response object on success, or WP_Error object on failure.
     */
    public function run_health_scan($request) {
        $health_data = $this->perform_full_health_scan();
        
        // Cache for 24 hours
        set_transient('prorank_health_data', $health_data, DAY_IN_SECONDS);

        return new WP_REST_Response($health_data, 200);
    }

    /**
     * Calculate overall SEO score
     *
     * @return int
     */
    private function calculate_seo_score(): int {
        $score = 100;
        $deductions = 0;

        // Check if essential modules are enabled
        if (!$this->module_manager->is_module_enabled('titles-meta')) {
            $deductions += 20;
        }
        if (!$this->module_manager->is_module_enabled('sitemaps')) {
            $deductions += 15;
        }

        // Check for missing meta descriptions
        $posts_without_meta = $this->get_posts_without_meta_descriptions();
        if ($posts_without_meta > 0) {
            $deductions += min(20, $posts_without_meta);
        }

        // Check for robots.txt
        $home_path = function_exists('prorank_get_home_path')
            ? (string) prorank_get_home_path()
            : '';
        if ($home_path === '') {
            if ( ! function_exists( 'get_home_path' ) ) {
                require_once ABSPATH . 'wp-admin/includes/file.php';
            }
            $home_path = (string) get_home_path();
        }
        $robots_path = trailingslashit(wp_normalize_path($home_path)) . 'robots.txt';
        if (!file_exists($robots_path)) {
            $deductions += 5;
        }

        // Check if permalink structure is set
        $permalink_structure = get_option('permalink_structure');
        if (empty($permalink_structure)) {
            $deductions += 10;
        }

        return max(0, $score - $deductions);
    }

    /**
     * Get count of indexed pages
     *
     * @return int
     */
    private function get_indexed_pages_count(): int {
        // Count published posts and pages
        $posts_count = wp_count_posts('post');
        $pages_count = wp_count_posts('page');
        
        return ($posts_count->publish ?? 0) + ($pages_count->publish ?? 0);
    }

    /**
     * Get SEO issues count
     *
     * @return int
     */
    private function get_issues_count(): int {
        $issues = 0;

        // Check for posts without meta descriptions
        $issues += $this->get_posts_without_meta_descriptions();

        // Check for missing alt text on images
        $issues += $this->get_images_without_alt_text();

        // Check for duplicate titles
        $issues += $this->get_duplicate_titles_count();

        return $issues;
    }

    /**
     * Get redirects count
     *
     * @return int
     */
    private function get_redirects_count(): int {
        global $wpdb;

        $table_name = $wpdb->prefix . 'prorank_redirects';

        // Check if table exists
        if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) !== $table_name) {
            return 0;
        }

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE status = %s",
                $table_name,
                'active'
            )
        );
    }

    /**
     * Get newest completed audit summary across local and cloud sources.
     */
    private function get_latest_audit_summary(): array {
        $local = $this->get_latest_local_audit_summary();
        $cloud = $this->can_use_cloud_audit() ? $this->get_latest_cloud_audit_summary() : [];

        if (empty($local) && empty($cloud)) {
            return [];
        }

        $latest = $this->select_newest_audit_summary($local, $cloud);
        $latest['history'] = $this->merge_audit_histories($local['history'] ?? [], $cloud['history'] ?? [], 6);

        return $latest;
    }

    /**
     * Get latest completed local audit summary (score, issues, history).
     */
    private function get_latest_local_audit_summary(): array {
        global $wpdb;

        $table_audits = $wpdb->prefix . 'prorank_audits';
        $table_issues = $wpdb->prefix . 'prorank_audit_issues';

        if (!$this->table_exists($table_audits)) {
            return [];
        }

        $audit_columns = $this->get_table_columns($table_audits);
        $order_by = $this->get_audit_order_by_clause($audit_columns);

        // Prefer the most recent completed local audit regardless of ID format.
        // Some local engines store UUIDs in `id` and timestamps in `start_time`/`end_time`.
        $audit = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i
                 WHERE status = %s
                 ORDER BY {$order_by}
                 LIMIT 1",
                $table_audits,
                'completed'
            )
        );

        if (!$audit) {
            return [];
        }

        $audit_id = $audit->audit_id ?? $audit->id ?? null;
        $issues_by_severity = [
            'critical' => 0,
            'high' => 0,
            'medium' => 0,
            'low' => 0,
        ];

        $stats_data = [];
        if (!empty($audit->stats)) {
            $decoded = json_decode($audit->stats, true);
            if (is_array($decoded)) {
                $stats_data = $decoded;
            }
        }

        $stats_unique = $stats_data['unique_issues'] ?? null;
        $stats_total = (isset($stats_data['total_issues']) && is_numeric($stats_data['total_issues']))
            ? (int) $stats_data['total_issues']
            : 0;
        if ($stats_total <= 0) {
            if (is_array($stats_unique) && isset($stats_unique['total']) && is_numeric($stats_unique['total'])) {
                $stats_total = (int) $stats_unique['total'];
            } elseif (is_numeric($stats_unique)) {
                $stats_total = (int) $stats_unique;
            }
        }

        $stats_critical = (isset($stats_data['critical_issues']) && is_numeric($stats_data['critical_issues']))
            ? (int) $stats_data['critical_issues']
            : (int) (is_array($stats_unique) ? ($stats_unique['critical'] ?? 0) : 0);
        $stats_high = (isset($stats_data['high_issues']) && is_numeric($stats_data['high_issues']))
            ? (int) $stats_data['high_issues']
            : (int) (is_array($stats_unique) ? ($stats_unique['high'] ?? 0) : 0);
        $stats_medium = (isset($stats_data['medium_issues']) && is_numeric($stats_data['medium_issues']))
            ? (int) $stats_data['medium_issues']
            : (int) (is_array($stats_unique) ? ($stats_unique['medium'] ?? 0) : 0);
        $stats_low = (isset($stats_data['low_issues']) && is_numeric($stats_data['low_issues']))
            ? (int) $stats_data['low_issues']
            : (int) (is_array($stats_unique) ? ($stats_unique['low'] ?? 0) : 0);

        if ($stats_total > 0 || ($stats_critical + $stats_high + $stats_medium + $stats_low) > 0) {
            $issues_by_severity['critical'] = $stats_critical;
            $issues_by_severity['high'] = $stats_high;
            $issues_by_severity['medium'] = $stats_medium;
            $issues_by_severity['low'] = $stats_low;
            $issues_by_severity['total'] = $stats_total > 0
                ? $stats_total
                : ($stats_critical + $stats_high + $stats_medium + $stats_low);
        } elseif ($audit_id && $this->table_exists($table_issues)) {
            $issue_rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT severity, COUNT(*) as cnt 
                     FROM %i 
                     WHERE audit_id = %s 
                     GROUP BY severity",
                    $table_issues,
                    $audit_id
                )
            );

            foreach ($issue_rows as $row) {
                $severity_key = $row->severity;
                if (isset($issues_by_severity[$severity_key])) {
                    $issues_by_severity[$severity_key] = (int) $row->cnt;
                }
            }

            $issues_by_severity['total'] = array_sum($issues_by_severity);
        } else {
            $issues_by_severity['total'] = 0;
        }

        // Build history for charts (latest 6 completed runs)
        $history = [];
        $history_select = ['audit_id', 'id', 'score', 'stats'];
        if (in_array('completed_at', $audit_columns, true)) {
            $history_select[] = 'completed_at';
        }
        if (in_array('started_at', $audit_columns, true)) {
            $history_select[] = 'started_at';
        }
        if (in_array('end_time', $audit_columns, true)) {
            $history_select[] = 'end_time';
        }
        if (in_array('start_time', $audit_columns, true)) {
            $history_select[] = 'start_time';
        }
        $history_select_sql = implode(', ', $history_select);

        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- select/order clauses are built from audited column allowlists only.
        $history_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT {$history_select_sql}
             FROM %i
             WHERE status = %s
             ORDER BY {$order_by}
             LIMIT 6",
            $table_audits,
            'completed'
        ));
        foreach ($history_rows as $row) {
            $row_stats = [];
            if (!empty($row->stats)) {
                $decoded = json_decode($row->stats, true);
                if (is_array($decoded)) {
                    $row_stats = $decoded;
                }
            }
            $history_issues_total = null;
            if (isset($row_stats['total_issues']) && is_numeric($row_stats['total_issues'])) {
                $history_issues_total = (int) $row_stats['total_issues'];
            } elseif (isset($row_stats['unique_issues']['total']) && is_numeric($row_stats['unique_issues']['total'])) {
                $history_issues_total = (int) $row_stats['unique_issues']['total'];
            } elseif (isset($row_stats['unique_issues']) && is_numeric($row_stats['unique_issues'])) {
                $history_issues_total = (int) $row_stats['unique_issues'];
            }
            $history[] = [
                'id' => $row->audit_id ?? $row->id,
                'score' => (int) ($row->score ?? 0),
                'completed_at' => $row->completed_at ?? $row->end_time ?? null,
                'issues_total' => $history_issues_total,
                'source' => 'local',
            ];
        }

        $last_run = $audit->completed_at ?? $audit->end_time ?? $audit->started_at ?? $audit->start_time ?? null;

        return [
            'id' => $audit_id,
            'state' => $audit->status ?? '',
            'score' => (int) ($audit->score ?? 0),
            'last_run' => $last_run,
            'completed_at' => $last_run,
            'completed_at_ts' => $this->get_audit_completed_timestamp($last_run),
            'source' => 'local',
            'issues' => $issues_by_severity,
            'history' => $history,
        ];
    }

    /**
     * Get latest completed non-local audit summary (score, issues, history).
     */
    private function get_latest_cloud_audit_summary(int $limit = 6): array {
        return [];
    }

    /**
     * Normalize a non-local audit row into the dashboard summary shape.
     *
     * @param array      $audit   Raw non-local audit row.
     * @param array|null $results Normalized cached results.
     * @return array
     */
    private function normalize_cloud_audit_summary_row(array $audit, ?array $results = null): array {
        $audit_id = (string) ($audit['id'] ?? $audit['audit_id'] ?? '');
        if ($audit_id === '') {
            return [];
        }

        $issue_counts = is_array($results['issue_counts'] ?? null) ? $results['issue_counts'] : (array) ($audit['issues'] ?? []);
        $statistics = is_array($results['statistics'] ?? null) ? $results['statistics'] : [];
        $completed_at = $audit['completed_at'] ?? $audit['created_at'] ?? null;
        $score = $results['raw_overall_score']
            ?? $results['overall_score']
            ?? $results['normalized_overall_score']
            ?? $audit['score']
            ?? $audit['overall_score']
            ?? 0;

        $issues_total = (int) ($issue_counts['total'] ?? 0);
        if ($issues_total <= 0) {
            $issues_total = (int) ($issue_counts['critical'] ?? 0)
                + (int) ($issue_counts['high'] ?? 0)
                + (int) ($issue_counts['medium'] ?? 0)
                + (int) ($issue_counts['low'] ?? 0)
                + (int) ($issue_counts['warning'] ?? 0);
        }

        return [
            'id' => $audit_id,
            'state' => (string) ($audit['status'] ?? 'completed'),
            'score' => (int) $score,
            'last_run' => $completed_at,
            'completed_at' => $completed_at,
            'completed_at_ts' => $this->get_audit_completed_timestamp($completed_at),
            'source' => 'cloud',
            'issues' => [
                'critical' => (int) ($issue_counts['critical'] ?? 0),
                'high' => (int) ($issue_counts['high'] ?? 0),
                'medium' => (int) ($issue_counts['medium'] ?? 0),
                'low' => (int) ($issue_counts['low'] ?? 0),
                'warning' => (int) ($issue_counts['warning'] ?? 0),
                'total' => $issues_total,
            ],
            'cloud' => [
                'pages_crawled' => (int) ($statistics['total_urls'] ?? $statistics['pages_crawled'] ?? $statistics['pages_analyzed'] ?? $audit['total_urls'] ?? $audit['analyzed_urls'] ?? $audit['crawled_urls'] ?? 0),
                'crawl_depth' => (int) ($statistics['crawl_depth'] ?? $audit['crawl_depth'] ?? 0),
                'lighthouse_scores' => is_array($results['lighthouse_scores'] ?? null)
                    ? $results['lighthouse_scores']
                    : (is_array($statistics['lighthouse_scores'] ?? null) ? $statistics['lighthouse_scores'] : []),
                'credits_used' => (int) ($audit['credits_used'] ?? 0),
                'credits_remaining' => (int) ($audit['credits_remaining'] ?? 0),
            ],
            'history' => [],
        ];
    }

    /**
     * Choose the newest audit summary between local and cloud.
     *
     * @param array $local Local audit summary.
     * @param array $cloud Non-local audit summary.
     * @return array
     */
    private function select_newest_audit_summary(array $local, array $cloud): array {
        if ($local === []) {
            return $cloud;
        }

        if ($cloud === []) {
            return $local;
        }

        $local_time = (int) ($local['completed_at_ts'] ?? 0);
        $cloud_time = (int) ($cloud['completed_at_ts'] ?? 0);

        return $cloud_time > $local_time ? $cloud : $local;
    }

    /**
     * Merge local and non-local audit histories, newest first.
     *
     * @param array $local_history Local audit history.
     * @param array $cloud_history Non-local audit history.
     * @param int   $limit         Maximum number of rows.
     * @return array
     */
    private function merge_audit_histories(array $local_history, array $cloud_history, int $limit = 6): array {
        $history = array_merge($local_history, $cloud_history);
        if ($history === []) {
            return [];
        }

        usort($history, function ($a, $b): int {
            $a_time = $this->get_audit_completed_timestamp($a['completed_at'] ?? null);
            $b_time = $this->get_audit_completed_timestamp($b['completed_at'] ?? null);
            return $b_time <=> $a_time;
        });

        $deduped = [];
        foreach ($history as $entry) {
            $key = (string) ($entry['source'] ?? 'local') . '|' . (string) ($entry['id'] ?? '');
            if (isset($deduped[$key])) {
                continue;
            }

            $deduped[$key] = $entry;
        }

        return array_slice(array_values($deduped), 0, $limit);
    }

    /**
     * Normalize a date/time value to a Unix timestamp.
     *
     * @param mixed $value Date/time value.
     * @return int
     */
    private function get_audit_completed_timestamp($value): int {
        if ($value instanceof \DateTimeInterface) {
            return $value->getTimestamp();
        }

        if (!is_scalar($value) || $value === '') {
            return 0;
        }

        $timestamp = strtotime((string) $value);

        return $timestamp !== false ? (int) $timestamp : 0;
    }

    /**
     * Get column names for a table.
     */
    private function get_table_columns(string $table_name): array {
        global $wpdb;

        $columns = $wpdb->get_col("SHOW COLUMNS FROM `{$table_name}`", 0);
        if (!is_array($columns)) {
            return [];
        }

        return array_map('strval', $columns);
    }

    /**
     * Build an ORDER BY clause that matches the available audit table columns.
     */
    private function get_audit_order_by_clause(array $columns): string {
        $completed_candidates = [];
        $started_candidates = [];

        if (in_array('completed_at', $columns, true)) {
            $completed_candidates[] = 'completed_at';
        }
        if (in_array('end_time', $columns, true)) {
            $completed_candidates[] = 'end_time';
        }
        if (in_array('started_at', $columns, true)) {
            $completed_candidates[] = 'started_at';
            $started_candidates[] = 'started_at';
        }
        if (in_array('start_time', $columns, true)) {
            $completed_candidates[] = 'start_time';
            $started_candidates[] = 'start_time';
        }

        $parts = [];
        if (!empty($completed_candidates)) {
            $parts[] = 'COALESCE(' . implode(', ', $completed_candidates) . ') DESC';
        }
        if (!empty($started_candidates)) {
            $parts[] = 'COALESCE(' . implode(', ', $started_candidates) . ') DESC';
        }
        if (in_array('id', $columns, true)) {
            $parts[] = 'id DESC';
        } elseif (in_array('audit_id', $columns, true)) {
            $parts[] = 'audit_id DESC';
        }

        if (empty($parts)) {
            return '1 DESC';
        }

        return implode(', ', $parts);
    }

    /**
     * Cache/Delivery overview from real settings
     */
    private function get_cache_overview(): array {
        $defaults = [
            'cache_enabled' => false,
            'cache_lifetime' => 0,
            'cdn_provider' => 'none',
            'preload_enabled' => false,
            'link_preload_enabled' => false,
            'browser_cache_enabled' => false,
        ];
        $settings = get_option('prorank_cache_settings', $defaults);
        $settings = wp_parse_args(is_array($settings) ? $settings : [], $defaults);

        return [
            'enabled' => (bool) $settings['cache_enabled'],
            'cache_lifetime' => (int) $settings['cache_lifetime'],
            'cdn_provider' => $settings['cdn_provider'] ?? 'none',
            'preload_enabled' => !empty($settings['preload_enabled']),
            'link_preload_enabled' => !empty($settings['link_preload_enabled']),
            'browser_cache_enabled' => !empty($settings['browser_cache_enabled']),
        ];
    }

    /**
     * Asset/JS overview from real settings
     */
    private function get_asset_overview(): array {
        $defaults = [
            'critical_css_enabled' => false,
            'unused_css_enabled' => false,
            'js_minify_enabled' => false,
            'partytown_enabled' => false,
            'inp_optimizer_enabled' => false,
            'host_google_fonts_locally' => false,
            'font_subsetting_enabled' => false,
        ];
        $settings = get_option('prorank_asset_optimization_settings', $defaults);
        $settings = wp_parse_args(is_array($settings) ? $settings : [], $defaults);

        return [
            'critical_css' => !empty($settings['critical_css_enabled']),
            'unused_css' => !empty($settings['unused_css_enabled']),
            'js_minify' => !empty($settings['js_minify_enabled']),
            'partytown' => !empty($settings['partytown_enabled']),
            'inp_optimizer' => !empty($settings['inp_optimizer_enabled']),
            'font_optimization' => !empty($settings['host_google_fonts_locally']) || !empty($settings['font_subsetting_enabled']),
        ];
    }

    /**
     * Image optimization overview from module stats
     */
    private function get_image_overview(): array {
        $settings_defaults = [
            'webp_enabled' => true,
            'avif_enabled' => false,
            'optimize_on_upload' => true,
        ];
        $settings = get_option('prorank_image_optimization_settings', $settings_defaults);
        $settings = wp_parse_args(is_array($settings) ? $settings : [], $settings_defaults);

        $stats = [
            'total_images' => 0,
            'optimized_images' => 0,
            'saved_bytes' => 0,
        ];

        $plugin = \ProRank\SEO\Plugin::get_instance();
        if ($plugin && $plugin->is_initialized()) {
            $module = $plugin->modules()->get_module('image_optimization');
            if ($module && method_exists($module, 'get_stats')) {
                $module_stats = $module->get_stats();
                $stats['total_images'] = (int) ($module_stats['total'] ?? 0);
                $stats['optimized_images'] = (int) ($module_stats['optimized'] ?? 0);
                $stats['saved_bytes'] = (int) ($module_stats['total_savings'] ?? $module_stats['bytes_saved'] ?? 0);
            }
        }

        return [
            'stats' => $stats,
            'webp_enabled' => !empty($settings['webp_enabled']),
            'avif_enabled' => !empty($settings['avif_enabled']),
            'optimize_on_upload' => !empty($settings['optimize_on_upload']),
        ];
    }

    /**
     * Database/cleanup overview
     */
    private function get_database_overview(): array {
        $defaults = [
            'auto_cleanup_enabled' => false,
            'cleanup_schedule' => 'weekly',
            'revision_limit' => 5,
            'heartbeat_control' => false,
        ];
        $settings = get_option('prorank_database_settings', $defaults);
        $settings = wp_parse_args(is_array($settings) ? $settings : [], $defaults);

        return [
            'auto_cleanup_enabled' => !empty($settings['auto_cleanup_enabled']),
            'cleanup_schedule' => $settings['cleanup_schedule'] ?? 'weekly',
            'revision_limit' => (int) ($settings['revision_limit'] ?? 0),
            'heartbeat_control' => !empty($settings['heartbeat_control']),
        ];
    }

    /**
     * Real-user monitoring (RUM) summary for Core Web Vitals
     */
    private function get_rum_summary(): array {
        $buffer = get_option('prorank_rum_buffer', []);
        if (!is_array($buffer) || empty($buffer)) {
            return [
                'samples' => 0,
                'averages' => [
                    'lcp' => null,
                    'inp' => null,
                    'cls' => null,
                ],
            ];
        }

        $samples = array_slice($buffer, 0, 50);
        $counts = ['lcp' => 0, 'inp' => 0, 'cls' => 0];
        $totals = ['lcp' => 0, 'inp' => 0, 'cls' => 0.0];

        foreach ($samples as $sample) {
            foreach (['lcp', 'inp', 'cls'] as $metric) {
                if (isset($sample[$metric]) && is_numeric($sample[$metric])) {
                    $totals[$metric] += (float) $sample[$metric];
                    $counts[$metric]++;
                }
            }
        }

        $averages = [
            'lcp' => $counts['lcp'] > 0 ? (int) round($totals['lcp'] / $counts['lcp']) : null,
            'inp' => $counts['inp'] > 0 ? (int) round($totals['inp'] / $counts['inp']) : null,
            'cls' => $counts['cls'] > 0 ? round($totals['cls'] / $counts['cls'], 3) : null,
        ];

        return [
            'samples' => count($samples),
            'averages' => $averages,
            'last_sample' => $samples[0] ?? null,
        ];
    }

    /**
     * Get analytics summary (GSC/GA4 data) for dashboard
     *
     * @return array
     */
    private function get_analytics_summary(): array {
        return [
            'gsc' => [],
            'ga' => [],
            'rank_tracking' => [],
        ];
    }

    /**
     * Helper to check if a table exists
     */
    private function table_exists(string $table_name): bool {
        global $wpdb;
        return $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) === $table_name;
    }

    /**
     * Get recent SEO activity
     *
     * @return array
     */
    private function get_recent_activity(): array {
        $activity = [];

        // Get recently modified posts
        $recent_posts = get_posts([
            'post_type' => ['post', 'page'],
            'posts_per_page' => 5,
            'orderby' => 'modified',
            'order' => 'DESC',
        ]);

        foreach ($recent_posts as $post) {
            $activity[] = [
                'icon' => 'dashicons-edit',
                'message' => sprintf(
/* translators: %1$: value */
                    __('Updated: %1$s', 'prorank-seo'),
                    esc_html($post->post_title)
                ),
                'time' => human_time_diff(strtotime($post->post_modified), current_time('timestamp')) . ' ' . __('ago', 'prorank-seo'),
            ];
        }

        return $activity;
    }

    /**
     * Perform basic health checks
     *
     * @return array
     */
    private function perform_basic_health_checks(): array {
        $checks = [];
        $passed = 0;
        $warning = 0;
        $failed = 0;

        // Check permalink structure
        $permalink_structure = get_option('permalink_structure');
        if (empty($permalink_structure)) {
            $checks[] = [
                'title' => __('Permalink Structure', 'prorank-seo'),
                'description' => __('Your permalink structure is set to plain. This is not SEO-friendly.', 'prorank-seo'),
                'status' => 'fail',
                'solution' => __('Change your permalink structure to Post name or a custom structure.', 'prorank-seo'),
                'action' => [
                    'label' => __('Fix Now', 'prorank-seo'),
                    'url' => admin_url('options-permalink.php'),
                ],
            ];
            $failed++;
        } else {
            $checks[] = [
                'title' => __('Permalink Structure', 'prorank-seo'),
                'description' => __('Your permalink structure is SEO-friendly.', 'prorank-seo'),
                'status' => 'pass',
            ];
            $passed++;
        }

        // Check if SEO modules are enabled
        if (!$this->module_manager->is_module_enabled('titles-meta')) {
            $checks[] = [
                'title' => __('Meta Tags Module', 'prorank-seo'),
                'description' => __('The Meta Tags module is disabled. This module is essential for SEO.', 'prorank-seo'),
                'status' => 'fail',
                'solution' => __('Enable the Meta Tags module to manage your page titles and descriptions.', 'prorank-seo'),
                'action' => [
                    'label' => __('Enable Module', 'prorank-seo'),
                    'url' => admin_url('admin.php?page=prorank-seo'),
                ],
            ];
            $failed++;
        } else {
            $checks[] = [
                'title' => __('Meta Tags Module', 'prorank-seo'),
                'description' => __('Meta Tags module is active and working.', 'prorank-seo'),
                'status' => 'pass',
            ];
            $passed++;
        }

        // Check for XML sitemaps
        if (!$this->module_manager->is_module_enabled('sitemaps')) {
            $checks[] = [
                'title' => __('XML Sitemaps', 'prorank-seo'),
                'description' => __('XML Sitemaps are not enabled.', 'prorank-seo'),
                'status' => 'warning',
                'solution' => __('Enable XML Sitemaps to help search engines discover your content.', 'prorank-seo'),
                'action' => [
                    'label' => __('Enable Sitemaps', 'prorank-seo'),
                    'url' => admin_url('admin.php?page=prorank-technical-seo'),
                ],
            ];
            $warning++;
        } else {
            $checks[] = [
                'title' => __('XML Sitemaps', 'prorank-seo'),
                'description' => __('XML Sitemaps are enabled and accessible.', 'prorank-seo'),
                'status' => 'pass',
            ];
            $passed++;
        }

        // Calculate score
        $total_checks = count($checks);
        $score = $total_checks > 0 ? round(($passed / $total_checks) * 100) : 0;

        return [
            'score' => $score,
            'summary' => $this->get_health_summary($score),
            'checks' => $checks,
            'checks_passed' => $passed,
            'checks_warning' => $warning,
            'checks_failed' => $failed,
            'last_scan' => current_time('c'),
            'recommendations' => $this->get_health_recommendations($checks),
        ];
    }

    /**
     * Perform full health scan
     *
     * @return array
     */
    private function perform_full_health_scan(): array {
        // Start with basic checks
        $health_data = $this->perform_basic_health_checks();
        
        // Add more comprehensive checks
        $additional_checks = [];
        
        // Check for missing meta descriptions
        $posts_without_meta = $this->get_posts_without_meta_descriptions();
        if ($posts_without_meta > 0) {
            $additional_checks[] = [
                'title' => __('Meta Descriptions', 'prorank-seo'),
                'description' => sprintf(
/* translators: %1$: value */
                    __('%1$d posts/pages are missing meta descriptions.', 'prorank-seo'),
                    $posts_without_meta
                ),
                'status' => 'warning',
                'solution' => __('Add unique meta descriptions to improve click-through rates.', 'prorank-seo'),
            ];
            $health_data['checks_warning']++;
        }
        
        // Check image alt text
        $images_without_alt = $this->get_images_without_alt_text();
        if ($images_without_alt > 0) {
            $additional_checks[] = [
                'title' => __('Image Alt Text', 'prorank-seo'),
                'description' => sprintf(
/* translators: %1$: value */
                    __('%1$d images are missing alt text.', 'prorank-seo'),
                    $images_without_alt
                ),
                'status' => 'warning',
                'solution' => __('Add descriptive alt text to images for better accessibility and SEO.', 'prorank-seo'),
            ];
            $health_data['checks_warning']++;
        }
        
        // Merge additional checks
        $health_data['checks'] = array_merge($health_data['checks'], $additional_checks);
        
        // Recalculate score
        $total_checks = count($health_data['checks']);
        $health_data['score'] = $total_checks > 0 
            ? round(($health_data['checks_passed'] / $total_checks) * 100) 
            : 0;
        
        $health_data['summary'] = $this->get_health_summary($health_data['score']);
        
        return $health_data;
    }

    /**
     * Get health summary based on score
     *
     * @param int $score Health score
     * @return string
     */
    private function get_health_summary(int|float $score): string {
        if ($score >= 80) {
            return __('Your site\'s SEO health is excellent! Keep up the good work.', 'prorank-seo');
        } elseif ($score >= 60) {
            return __('Your site\'s SEO health is good, but there\'s room for improvement.', 'prorank-seo');
        } elseif ($score >= 40) {
            return __('Your site\'s SEO health needs attention. Address the issues below.', 'prorank-seo');
        } else {
            return __('Your site\'s SEO health is poor. Immediate action is recommended.', 'prorank-seo');
        }
    }

    /**
     * Get health recommendations
     *
     * @param array $checks Health checks
     * @return array
     */
    private function get_health_recommendations(array $checks): array {
        $recommendations = [];
        
        // Check if there are any failed checks
        $has_failures = false;
        foreach ($checks as $check) {
            if ($check['status'] === 'fail') {
                $has_failures = true;
                break;
            }
        }
        
        if ($has_failures) {
            $recommendations[] = [
                'title' => __('Fix Critical Issues', 'prorank-seo'),
                'description' => __('Address the failed checks first as they have the most impact on your SEO.', 'prorank-seo'),
            ];
        }
        
        // Always recommend regular content updates
        $recommendations[] = [
            'title' => __('Keep Content Fresh', 'prorank-seo'),
            'description' => __('Regularly update your content to maintain search engine rankings.', 'prorank-seo'),
            'link' => 'https://prorank.io/docs/content-optimization',
        ];
        
        return $recommendations;
    }

    /**
     * Get count of posts without meta descriptions
     *
     * @return int
     */
    private function get_posts_without_meta_descriptions(): int {
        global $wpdb;
        
        $count = $wpdb->get_var("
            SELECT COUNT(p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_prorank_seo_description'
            WHERE p.post_type IN ('post', 'page') 
            AND p.post_status = 'publish'
            AND (pm.meta_value IS NULL OR pm.meta_value = '')
        ");
        
        return (int) $count;
    }

    /**
     * Get count of images without alt text
     *
     * @return int
     */
    private function get_images_without_alt_text(): int {
        global $wpdb;
        
        $count = $wpdb->get_var("
            SELECT COUNT(p.ID) 
            FROM {$wpdb->posts} p 
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_wp_attachment_image_alt'
            WHERE p.post_type = 'attachment'
            AND p.post_mime_type LIKE 'image/%'
            AND (pm.meta_value IS NULL OR pm.meta_value = '')
        ");
        
        return (int) $count;
    }

    /**
     * Get count of duplicate titles
     *
     * @return int
     */
    private function get_duplicate_titles_count(): int {
        global $wpdb;
        
        $count = $wpdb->get_var("
            SELECT COUNT(*) FROM (
                SELECT post_title, COUNT(*) as count
                FROM {$wpdb->posts}
                WHERE post_type IN ('post', 'page')
                AND post_status = 'publish'
                GROUP BY post_title
                HAVING count > 1
            ) as duplicates
        ");
        
        return (int) $count;
    }

    /**
     * Check if a given request has access to get stats
     *
     * @param WP_REST_Request $request Full details about the request.
     * @return true|WP_Error True if the request has read access, WP_Error object otherwise.
     */
    public function get_stats_permissions_check($request) {
        return current_user_can('edit_posts');
    }

    /**
     * Check if a given request has access to run scans
     *
     * @param WP_REST_Request $request Full details about the request.
     * @return true|WP_Error True if the request has access to run scans, WP_Error object otherwise.
     */
    public function run_scan_permissions_check($request) {
        return current_user_can('manage_options');
    }
}
