<?php
/**
 * System health diagnostics — table sizes / row counts / cleanup for the
 * plugin's own wp_aetos_seo_* custom tables. Every custom table name is bound
 * as an SQL identifier via the %i placeholder (WordPress 6.2+), and every value
 * (including LIKE patterns) is bound with %d / %s through $wpdb->prepare().
 *
 * Direct (uncached) queries are intentional: these are maintenance reads/writes
 * on the plugin's own audit tables, where a stale cache would mislead the
 * diagnostics. Only the non-injection DirectDatabaseQuery sniffs are suppressed:
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Maintenance reads/writes on this plugin's own custom tables where a stale cache would mislead the diagnostics. Non-injection sniffs only; identifiers bound with %i, values with %d/%s via $wpdb->prepare(); the PreparedSQL sniffs are never suppressed.
 */

declare(strict_types=1);

namespace AetosSeo\Core;

if (!defined('ABSPATH')) {
    exit;
}

use AetosSeo\Utils\Time;

class SystemHealth
{
    /**
     * @return array<string,mixed>
     */
    public function snapshot(): array
    {
        global $wpdb;
        $prefix = $wpdb->prefix;

        return [
            'wp_cron' => [
                'disable_wp_cron' => defined('DISABLE_WP_CRON') ? (bool) constant('DISABLE_WP_CRON') : null,
                'alternate_wp_cron' => defined('ALTERNATE_WP_CRON') ? (bool) constant('ALTERNATE_WP_CRON') : null,
                'doing_cron' => (string) get_transient('doing_cron'),
                'events' => $this->scheduled_aetos_events(),
            ],
            'runs' => $this->active_runs($prefix),
            // v3.3.30 — include last failed runs with their full notes so
            // diagnostics surface the actual exception message without
            // requiring wp-debug.log access.
            'recent_failed_runs' => $this->recent_failed_runs($prefix),
            'locks' => $this->audit_locks(),
            'crawl' => $this->crawl_state($prefix),
            'tables' => $this->table_status($prefix),
            'php' => [
                'version' => PHP_VERSION,
                'max_execution_time' => (string) ini_get('max_execution_time'),
                'memory_limit' => (string) ini_get('memory_limit'),
                'max_input_time' => (string) ini_get('max_input_time'),
            ],
            'generated_at' => gmdate('Y-m-d H:i:s') . ' UTC',
        ];
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function scheduled_aetos_events(): array
    {
        if (!function_exists('_get_cron_array')) {
            return [];
        }

        $cron = _get_cron_array();
        if (!is_array($cron)) {
            return [];
        }

        $events = [];
        $now = time();
        foreach ($cron as $timestamp => $hooks) {
            if (!is_array($hooks)) {
                continue;
            }
            foreach ($hooks as $hook => $items) {
                if (strpos((string) $hook, 'aetos_seo_') !== 0) {
                    continue;
                }
                foreach ((array) $items as $event) {
                    $events[] = [
                        'hook' => (string) $hook,
                        'due_at' => gmdate('Y-m-d H:i:s', (int) $timestamp) . ' UTC',
                        'due_in' => (int) $timestamp - $now,
                        'args' => isset($event['args']) ? (array) $event['args'] : [],
                    ];
                }
            }
        }

        usort($events, static function (array $a, array $b): int {
            return (int) $a['due_in'] <=> (int) $b['due_in'];
        });

        return $events;
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function active_runs(string $prefix): array
    {
        global $wpdb;
        $table = $prefix . 'aetos_seo_runs';
        if (!$this->table_exists($table)) {
            return [];
        }

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, run_type, status, created_at, ended_at, overall_score, notes
                 FROM %i
                 WHERE status IN ('queued','running','paused')
                 ORDER BY id DESC
                 LIMIT 10",
                $table
            ),
            ARRAY_A
        );

        return is_array($rows) ? $rows : [];
    }

    /**
     * v3.3.30 — Last 5 failed runs with full notes (= exception messages).
     *
     * @return array<int,array<string,mixed>>
     */
    private function recent_failed_runs(string $prefix): array
    {
        global $wpdb;
        $table = $prefix . 'aetos_seo_runs';
        if (!$this->table_exists($table)) {
            return [];
        }
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, run_type, status, created_at, ended_at, notes
                 FROM %i
                 WHERE status = 'failed'
                 ORDER BY id DESC
                 LIMIT 5",
                $table
            ),
            ARRAY_A
        );
        return is_array($rows) ? $rows : [];
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function audit_locks(): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT option_name, option_value
                 FROM %i
                 WHERE option_name LIKE %s
                    OR option_name LIKE %s
                    OR option_name LIKE %s
                 ORDER BY option_id DESC",
                $wpdb->options,
                'aetos_seo_audit_lock_%',
                '_transient_aetos_seo_audit_lock_%',
                '_transient_timeout_aetos_seo_audit_lock_%'
            ),
            ARRAY_A
        );
        if (!is_array($rows)) {
            return [];
        }

        $now = time();
        foreach ($rows as &$row) {
            $value = (string) ($row['option_value'] ?? '');
            $row['age_seconds'] = is_numeric($value) ? max(0, $now - (int) $value) : null;
        }
        unset($row);

        return $rows;
    }

    /**
     * @return array<string,mixed>
     */
    private function crawl_state(string $prefix): array
    {
        global $wpdb;
        $summary = get_option(Settings::CRAWL_STATE_OPTION);
        if (!is_array($summary)) {
            $summary = [];
        }

        $table = $prefix . 'aetos_seo_crawl_state';
        $rows = [];
        if ($this->table_exists($table)) {
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT run_id, state_blob, updated_at, lock_token, lock_until
                     FROM %i
                     ORDER BY updated_at DESC
                     LIMIT 5",
                    $table
                ),
                ARRAY_A
            );
            if (!is_array($rows)) {
                $rows = [];
            }
            foreach ($rows as &$row) {
                $state = json_decode((string) ($row['state_blob'] ?? ''), true);
                if (is_array($state)) {
                    $row['status'] = (string) ($state['status'] ?? '');
                    $row['processed'] = (int) ($state['processed'] ?? 0);
                    $row['queue'] = isset($state['queue']) && is_array($state['queue']) ? count($state['queue']) : 0;
                    $row['max_urls'] = (int) ($state['max_urls'] ?? 0);
                }
                unset($row['state_blob']);
            }
            unset($row);
        }

        return [
            'summary' => $summary,
            'db_rows' => $rows,
        ];
    }

    /**
     * @return array<string,bool>
     */
    private function table_status(string $prefix): array
    {
        $tables = [
            'runs' => $prefix . 'aetos_seo_runs',
            'findings' => $prefix . 'aetos_seo_findings',
            'crawl' => $prefix . 'aetos_seo_crawl',
            'crawl_state' => $prefix . 'aetos_seo_crawl_state',
        ];

        $out = [];
        foreach ($tables as $key => $table) {
            $out[$key] = $this->table_exists($table);
        }
        return $out;
    }

    private function table_exists(string $table): bool
    {
        global $wpdb;
        $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        return is_string($found) && strtolower($found) === strtolower($table);
    }

    /**
     * @return array<string,int>
     */
    public function clear_audit_locks(): array
    {
        global $wpdb;
        $locks = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT option_name FROM %i
                 WHERE option_name LIKE %s
                    OR option_name LIKE %s
                    OR option_name LIKE %s",
                $wpdb->options,
                'aetos_seo_audit_lock_%',
                '_transient_aetos_seo_audit_lock_%',
                '_transient_timeout_aetos_seo_audit_lock_%'
            )
        );
        if (!is_array($locks)) {
            $locks = [];
        }

        foreach ($locks as $lock) {
            $lock_name = (string) $lock;
            delete_option($lock_name);
            if (strpos($lock_name, '_transient_timeout_') === 0) {
                delete_option(str_replace('_transient_timeout_', '_transient_', $lock_name));
            } elseif (strpos($lock_name, '_transient_') === 0) {
                delete_option(str_replace('_transient_', '_transient_timeout_', $lock_name));
            } else {
                delete_transient($lock_name);
            }
        }
        delete_transient('doing_cron');

        return ['locks_cleared' => count($locks), 'doing_cron_cleared' => 1];
    }

    /**
     * @return array<string,int>
     */
    public function fail_stuck_runs(int $stale_seconds = 900): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'aetos_seo_runs';
        if (!$this->table_exists($table)) {
            return ['runs_failed' => 0];
        }

        $threshold_ts = time() - max(300, $stale_seconds);
        $now = current_time('mysql', true);

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, run_type, status, created_at, ended_at, notes
                 FROM %i
                 WHERE status IN ('queued','running','paused')
                   AND ended_at IS NULL
                 ORDER BY id DESC
                 LIMIT 20",
                $table
            ),
            ARRAY_A
        );
        if (!is_array($rows)) {
            return ['runs_failed' => 0];
        }

        $failed = 0;
        foreach ($rows as $row) {
            if (!$this->run_is_actually_stuck($row, $threshold_ts)) {
                continue;
            }
            $ok = $wpdb->update(
                $table,
                ['status' => 'failed', 'ended_at' => $now],
                ['id' => (int) ($row['id'] ?? 0)]
            );
            if ($ok !== false) {
                $failed++;
            }
        }
        if ($failed > 0) {
            $this->clear_audit_locks();
        }

        return ['runs_failed' => $failed];
    }

    public function has_stuck_runs(int $stale_seconds = 900): bool
    {
        global $wpdb;
        $table = $wpdb->prefix . 'aetos_seo_runs';
        if (!$this->table_exists($table)) {
            return false;
        }

        $threshold_ts = time() - max(300, $stale_seconds);
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, run_type, status, created_at, ended_at, notes
                 FROM %i
                 WHERE status IN ('queued','running','paused')
                   AND ended_at IS NULL
                 ORDER BY id DESC
                 LIMIT 20",
                $table
            ),
            ARRAY_A
        );
        if (!is_array($rows)) {
            return false;
        }

        foreach ($rows as $row) {
            if ($this->run_is_actually_stuck($row, $threshold_ts)) {
                return true;
            }
        }

        return false;
    }

    /**
     * @param array<string,mixed> $run
     */
    private function run_is_actually_stuck(array $run, int $threshold_ts): bool
    {
        $created_ts = Time::mysql_to_timestamp((string) ($run['created_at'] ?? ''));
        $last_activity = $created_ts;

        $notes = json_decode((string) ($run['notes'] ?? ''), true);
        if (is_array($notes)) {
            $updated_ts = Time::mysql_to_timestamp((string) ($notes['updated_at'] ?? ''));
            $last_progress_ts = Time::mysql_to_timestamp((string) ($notes['last_progress_at'] ?? ''));
            $last_activity = max($last_activity, $last_progress_ts > 0 ? $last_progress_ts : $updated_ts);

            $crawl_id = isset($notes['crawl_source_run_id']) ? (int) $notes['crawl_source_run_id'] : 0;
            if ($crawl_id > 0) {
                $crawl_activity = $this->crawl_updated_ts($crawl_id);
                $last_activity = max($last_activity, $crawl_activity);
            }
        }

        $summary = get_option(Settings::CRAWL_STATE_OPTION);
        if (is_array($summary) && in_array((string) ($summary['status'] ?? ''), ['running', 'paused'], true)) {
            $last_activity = max($last_activity, Time::mysql_to_timestamp((string) ($summary['updated_at'] ?? '')));
        }

        return $last_activity > 0 && $last_activity < $threshold_ts;
    }

    private function crawl_updated_ts(int $run_id): int
    {
        if ($run_id <= 0) {
            return 0;
        }
        global $wpdb;
        $table = $wpdb->prefix . 'aetos_seo_crawl_state';
        if (!$this->table_exists($table)) {
            return 0;
        }
        $updated = $wpdb->get_var($wpdb->prepare("SELECT updated_at FROM %i WHERE run_id=%d LIMIT 1", $table, $run_id));
        return Time::mysql_to_timestamp(is_string($updated) ? $updated : '');
    }

    /**
     * @return array<string,int>
     */
    public function clear_aetos_cron_events(): array
    {
        if (!function_exists('_get_cron_array')) {
            return ['events_cleared' => 0];
        }
        $cron = _get_cron_array();
        if (!is_array($cron)) {
            return ['events_cleared' => 0];
        }

        $count = 0;
        foreach ($cron as $timestamp => $hooks) {
            foreach ((array) $hooks as $hook => $events) {
                if (strpos((string) $hook, 'aetos_seo_run_') !== 0) {
                    continue;
                }
                foreach ((array) $events as $event) {
                    wp_unschedule_event((int) $timestamp, (string) $hook, isset($event['args']) ? (array) $event['args'] : []);
                    $count++;
                }
            }
        }

        return ['events_cleared' => $count];
    }

    /**
     * Clear scan/audit runtime data. Settings and license stay intact unless
     * $factory_reset is true.
     *
     * @return array<string,int>
     */
    public function clear_scan_data(bool $factory_reset = false): array
    {
        global $wpdb;

        $locks = $this->clear_audit_locks();
        $cron = $this->clear_aetos_cron_events();
        $transients = $this->clear_aetos_transients();

        $schema = new \AetosSeo\Storage\Schema($wpdb);
        $tables = $schema->get_table_names();
        if (!$factory_reset) {
            $allowed_suffixes = [
                'aetos_seo_runs',
                'aetos_seo_findings',
                'aetos_seo_crawl',
                'aetos_seo_crawl_state',
            ];
            $tables = array_values(array_filter($tables, static function (string $table) use ($allowed_suffixes): bool {
                foreach ($allowed_suffixes as $suffix) {
                    if (substr($table, -strlen($suffix)) === $suffix) {
                        return true;
                    }
                }
                return false;
            }));
        }
        $truncated = 0;
        $reset_identity = 0;
        $failed = 0;
        $hard_recreated = 0;

        foreach ($tables as $table) {
            if (!$this->table_exists($table)) {
                continue;
            }
            $result = $this->empty_table_and_reset_identity($table);
            if (!empty($result['emptied'])) {
                $truncated++;
            }
            if (!empty($result['identity_reset'])) {
                $reset_identity++;
            } else {
                // v2.8.1: hard fallback. Some shared hosts (Hostinger
                // restricted DB users) silently leave AUTO_INCREMENT alone
                // after TRUNCATE + ALTER. Drop + recreate the table to
                // *guarantee* the next run_id is 1. This is the fix for
                // "I cleared data but new scans still start at run #9".
                if ($this->hard_recreate_table($table, $schema)) {
                    $hard_recreated++;
                    $reset_identity++;
                }
            }
            if (empty($result['ok'])) {
                $failed++;
            }
        }

        delete_option(Settings::CRAWL_STATE_OPTION);
        delete_option(Settings::REPORTS_INDEX_OPTION);

        Settings::delete_upload_dirs();
        Settings::ensure_upload_dirs();

        if ($factory_reset) {
            delete_option(Settings::OPTION_KEY);
            // v3.3.11 — Integrations feature was removed. Drop the legacy
            // option key directly so factory reset cleans up old data from
            // sites that ran v3.3.10 or earlier.
            delete_option('aetos_seo_integrations');
        }

        return [
            'tables_truncated' => $truncated,
            'tables_failed' => $failed,
            'auto_increment_reset' => $reset_identity,
            'remaining_runs' => $this->table_row_count($wpdb->prefix . 'aetos_seo_runs'),
            'locks_cleared' => (int) ($locks['locks_cleared'] ?? 0),
            'events_cleared' => (int) ($cron['events_cleared'] ?? 0),
            'transients_cleared' => (int) ($transients['transients_cleared'] ?? 0),
            'factory_reset' => $factory_reset ? 1 : 0,
        ];
    }

    /**
     * Empty a plugin table and explicitly reset AUTO_INCREMENT. Some hosts
     * block TRUNCATE through restricted DB users, so fall back to DELETE +
     * ALTER TABLE rather than pretending the reset succeeded.
     *
     * @return array<string,int>
     */
    /**
     * v2.8.1 — Last-resort table reset. DROP + recreate via the same dbDelta
     * statements the Activator uses, so AUTO_INCREMENT is guaranteed to be 1.
     * Used only when TRUNCATE + ALTER both fail to reset the counter (some
     * shared-host MySQL configurations).
     */
    private function hard_recreate_table(string $table, \AetosSeo\Storage\Schema $schema): bool
    {
        global $wpdb;

        // Find the matching CREATE TABLE statement for this specific table.
        $stmts = $schema->get_create_table_sql();
        $matched_sql = '';
        foreach ($stmts as $sql) {
            if (preg_match('/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([^\s`(]+)/i', (string) $sql, $m)) {
                if (isset($m[1]) && $m[1] === $table) {
                    $matched_sql = (string) $sql;
                    break;
                }
            }
        }
        if ($matched_sql === '') {
            return false; // can't safely recreate without the schema
        }

        $dropped = $wpdb->query($wpdb->prepare("DROP TABLE IF EXISTS %i", $table));
        if ($dropped === false) {
            return false;
        }

        if (function_exists('dbDelta')) {
            // dbDelta tolerates re-run; for a single fresh table it just creates.
            @dbDelta($matched_sql);
        } else {
            return false;
        }

        return $this->table_exists($table);
    }

    private function empty_table_and_reset_identity(string $table): array
    {
        global $wpdb;

        // Table names are generated by Storage\Schema from the WP prefix.
        $ok = 1;
        $emptied = 0;
        $identity_reset = 0;

        $truncated = $wpdb->query($wpdb->prepare("TRUNCATE TABLE %i", $table));
        if ($truncated !== false) {
            $emptied = 1;
        } else {
            $deleted = $wpdb->query($wpdb->prepare("DELETE FROM %i", $table));
            if ($deleted !== false) {
                $emptied = 1;
            } else {
                $ok = 0;
            }
        }

        if ($emptied) {
            // v2.8.1: belt-and-suspenders AUTO_INCREMENT reset. TRUNCATE on
            // MySQL/InnoDB normally resets the counter, but some shared
            // hosts (Hostinger restricted DB users, MariaDB with FK refs)
            // skip the reset. We then try ALTER explicitly. If THAT fails
            // we report identity_reset=0 honestly instead of pretending it
            // worked, so the UI can warn the admin that the next run_id
            // will continue from the previous max+1 instead of 1.
            $altered = $wpdb->query($wpdb->prepare("ALTER TABLE %i AUTO_INCREMENT = 1", $table));
            if ($altered !== false) {
                $identity_reset = 1;
            }

            // Verify by reading the actual AUTO_INCREMENT — some hosts
            // accept ALTER but silently leave the counter where it was if
            // there are foreign-key references or query-cache snapshots.
            if ($identity_reset === 1) {
                $current = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT AUTO_INCREMENT FROM information_schema.TABLES
                     WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s LIMIT 1",
                    DB_NAME,
                    $table
                ));
                if ($current > 1) {
                    $identity_reset = 0; // claim no reset rather than mislead
                }
            }
        }

        return [
            'ok' => $ok,
            'emptied' => $emptied,
            'identity_reset' => $identity_reset,
        ];
    }

    private function table_row_count(string $table): int
    {
        global $wpdb;
        if (!$this->table_exists($table)) {
            return 0;
        }
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $table));
        return is_numeric($count) ? (int) $count : 0;
    }

    /**
     * @return array<string,int>
     */
    public function clear_aetos_transients(): array
    {
        global $wpdb;
        $patterns = [
            '_transient_aetos_seo_%',
            '_transient_timeout_aetos_seo_%',
        ];
        $count = 0;
        foreach ($patterns as $pattern) {
            $deleted = $wpdb->query($wpdb->prepare(
                "DELETE FROM %i WHERE option_name LIKE %s",
                $wpdb->options,
                $pattern
            ));
            if (is_numeric($deleted)) {
                $count += (int) $deleted;
            }
        }

        return ['transients_cleared' => $count];
    }

    /**
     * @return array<string,int>
     */
    public function recover_scan_state(): array
    {
        return array_merge(
            $this->fail_stuck_runs(900),
            $this->clear_audit_locks(),
            $this->clear_aetos_cron_events()
        );
    }
}