<?php
/**
 * Storage layer for plugin-owned custom tables.
 *
 * This file is the sole abstraction over the plugin's wp_aetos_seo_* custom tables.
 * Every table name is bound as an SQL identifier via the %i placeholder (WordPress
 * 6.2+), and every user-derived value is bound with %d / %s through $wpdb->prepare().
 * No table name or value is ever concatenated into a query.
 *
 * Every query string passed to $wpdb->prepare() is a single STRING LITERAL — no PHP
 * variable is interpolated into it. Optional filters are expressed as self-disabling
 * literal guards inside the fixed query (e.g. "( %d = 0 OR run_id = %d )"): when the
 * caller omits a filter we pass its neutral value (0 / '') and the guard makes that
 * column a no-op. Multi-row writes use $wpdb->insert() / $wpdb->delete() (method form,
 * table as a parameter) rather than a hand-built VALUES list. Because nothing is
 * interpolated, the WordPress.DB.PreparedSQL* injection sniffs verify every statement
 * on their own — NONE of them is suppressed anywhere in this file.
 *
 * Each method aliases the WP database handle into a local `$wpdb` and inlines the
 * prepared statement directly inside the query call — the canonical
 * `$wpdb->query( $wpdb->prepare( ... ) )` form — so static analysis can verify the
 * query is prepared without data-flow tracking.
 *
 * Direct (uncached) queries are intentional: these are write-or-fresh-read operations
 * on the plugin's own audit-run state, where a stale read would corrupt the run. Only
 * the non-injection DirectDatabaseQuery sniffs are suppressed file-wide:
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- CRUD data-access layer for this plugin's own custom tables; direct + uncached by design (a stale read would corrupt the audit run). Non-injection sniffs only; every query is $wpdb->prepare()'d (%i identifiers, %d/%s values) and the PreparedSQL sniffs are never suppressed.
 */

declare(strict_types=1);

namespace AetosSeo\Storage;

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

use DateTime;
use AetosSeo\Core\Settings;
use AetosSeo\Security\Redactor;
use AetosSeo\Utils\I18n;

class Repository
{
    /** @var \wpdb */
    private $wpdb;

    /** @var string */
    private $runs;
    /** @var string */
    private $findings;
    /** @var string */
    private $crawl;

    /** @var string */
    private $crawl_state;

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
        $p = $wpdb->prefix;
        $this->runs = $p . 'aetos_seo_runs';
        $this->findings = $p . 'aetos_seo_findings';
        $this->crawl = $p . 'aetos_seo_crawl';
        $this->crawl_state = $p . 'aetos_seo_crawl_state';
    }

    /**
     * Store crawl session state in a dedicated table (NOT wp_options).
     * State is stored as gz-compressed JSON base64 to reduce size.
     *
     * @return array<string,mixed>
     */
    public function get_crawl_state(int $run_id): array
    {
        $wpdb = $this->wpdb;
        $blob = $wpdb->get_var($wpdb->prepare("SELECT state_blob FROM %i WHERE run_id=%d", $this->crawl_state, $run_id));
        if (!is_string($blob) || $blob === '') {
            return [];
        }

        $json = base64_decode($blob, true);
        if ($json === false) {
            return [];
        }
        if (function_exists('gzuncompress')) {
            $inflated = @gzuncompress($json);
            if ($inflated !== false) {
                $json = $inflated;
            }
        }

        $decoded = json_decode((string) $json, true);
        return is_array($decoded) ? $decoded : [];
    }

    /**
     * @param array<string,mixed> $state
     */
    public function save_crawl_state(int $run_id, array $state): bool
    {
        $wpdb = $this->wpdb;
        $json = wp_json_encode($state);
        if (!is_string($json)) {
            return false;
        }
        $compressed = $json;
        if (function_exists('gzcompress')) {
            $tmp = @gzcompress($json, 6);
            if ($tmp !== false) {
                $compressed = $tmp;
            }
        }

        $blob = base64_encode($compressed);
        $now = current_time('mysql');

        // Upsert.
        $updated = $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO %i (run_id, state_blob, updated_at) VALUES (%d, %s, %s)
                 ON DUPLICATE KEY UPDATE state_blob=VALUES(state_blob), updated_at=VALUES(updated_at)",
                $this->crawl_state,
                $run_id,
                $blob,
                $now
            )
        );

        return $updated !== false;
    }

    public function delete_crawl_state(int $run_id): void
    {
        $wpdb = $this->wpdb;
        $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE run_id=%d", $this->crawl_state, $run_id));
    }

    /**
     * Acquire a per-run DB lock to prevent concurrent crawl batches.
     * Returns lock token if acquired, or empty string if busy.
     */
    public function acquire_crawl_lock(int $run_id, int $ttl_seconds = 30): string
    {
        $wpdb = $this->wpdb;
        $token = function_exists('wp_generate_uuid4') ? wp_generate_uuid4() : uniqid('aetos_lock_', true);
        $now_ts = (int) current_time('timestamp');
        $now = gmdate('Y-m-d H:i:s', $now_ts);
        $until = gmdate('Y-m-d H:i:s', $now_ts + max(5, $ttl_seconds));

        // Ensure row exists.
        $wpdb->query(
            $wpdb->prepare(
                "INSERT IGNORE INTO %i (run_id, state_blob, updated_at) VALUES (%d, NULL, %s)",
                $this->crawl_state,
                $run_id,
                current_time('mysql')
            )
        );

        $affected = $wpdb->query(
            $wpdb->prepare(
                "UPDATE %i
                 SET lock_token=%s, lock_until=%s
                 WHERE run_id=%d AND (lock_until IS NULL OR lock_until < %s)",
                $this->crawl_state,
                $token,
                $until,
                $run_id,
                $now
            )
        );
        return ($affected === 1) ? $token : '';
    }

    public function release_crawl_lock(int $run_id, string $token): void
    {
        if ($token === '') {
            return;
        }
        $wpdb = $this->wpdb;
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE %i SET lock_token=NULL, lock_until=NULL WHERE run_id=%d AND lock_token=%s",
                $this->crawl_state,
                $run_id,
                $token
            )
        );
    }

    public function create_run(string $run_type, string $status = 'running', ?int $overall_score = null, array $category_scores = [], array $counts = [], array $context = [], ?int $duration_ms = null, string $notes = ''): int
    {
        $now = current_time('mysql');

        $this->wpdb->insert(
            $this->runs,
            [
                'run_type' => $run_type,
                'status' => $status,
                'created_at' => $now,
                'ended_at' => null,
                'overall_score' => $overall_score,
                'category_scores' => $category_scores ? wp_json_encode($category_scores) : null,
                'counts_json' => $counts ? wp_json_encode($counts) : null,
                'context_json' => $context ? wp_json_encode($context) : null,
                'duration_ms' => $duration_ms,
                'notes' => $notes,
            ],
            ['%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d', '%s']
        );

        return (int) $this->wpdb->insert_id;
    }

    /**
     * @param array<string,mixed> $fields
     */
    public function update_run(int $run_id, array $fields): bool
    {
        if ($run_id <= 0) {
            return false;
        }

        $allowed = [
            'status',
            'ended_at',
            'overall_score',
            'category_scores',
            'counts_json',
            'context_json',
            'duration_ms',
            'notes',
        ];

        $data = [];
        foreach ($allowed as $k) {
            if (array_key_exists($k, $fields)) {
                $data[$k] = $fields[$k];
            }
        }
        if (empty($data)) {
            return false;
        }

        $where = ['id' => $run_id];
        $updated = $this->wpdb->update($this->runs, $data, $where);
        return $updated !== false;
    }

    /**
     * @return array<string,mixed>|null
     */
    /**
     * 4.0.18 (review item D1): keep the newest $keep finished runs of a type
     * and delete older ones together with their findings, crawl rows and
     * crawl state. Queued and running runs are never touched. Returns the
     * number of runs deleted.
     */
    public function prune_runs(string $run_type, int $keep): int
    {
        $keep = max(2, $keep);
        $wpdb = $this->wpdb;
        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT id FROM %i WHERE run_type=%s AND status IN ('completed','failed') ORDER BY id DESC LIMIT 1000 OFFSET %d",
                $this->runs,
                $run_type,
                $keep
            )
        );
        if (!is_array($ids) || empty($ids)) {
            return 0;
        }
        $deleted = 0;
        foreach ($ids as $id) {
            $id = (int) $id;
            if ($id <= 0) {
                continue;
            }
            $this->wpdb->delete($this->findings, ['run_id' => $id], ['%d']);
            $this->wpdb->delete($this->crawl, ['run_id' => $id], ['%d']);
            $this->wpdb->delete($this->crawl_state, ['run_id' => $id], ['%d']);
            if ($this->wpdb->delete($this->runs, ['id' => $id], ['%d'])) {
                $deleted++;
            }
        }
        return $deleted;
    }

    public function get_latest_run(string $run_type): ?array
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE run_type=%s ORDER BY id DESC LIMIT 1", $this->runs, $run_type), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    /**
     * Get the most recent completed run.
     *
     * @return array<string,mixed>|null
     */
    public function get_latest_completed_run(string $run_type): ?array
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE run_type=%s AND status='completed' ORDER BY id DESC LIMIT 1",
                $this->runs,
                $run_type
            ),
            ARRAY_A
        );
        return is_array($row) ? $row : null;
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public function get_recent_runs(string $run_type, int $limit = 10): array
    {
        $wpdb = $this->wpdb;
        $limit = max(1, min(50, $limit));
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, created_at, overall_score FROM %i WHERE run_type=%s AND status=%s ORDER BY id DESC LIMIT %d",
                $this->runs,
                $run_type,
                'completed',
                $limit
            ),
            ARRAY_A
        );
        return is_array($rows) ? $rows : [];
    }


    /**
     * @return array<string,mixed>|null
     */
    public function get_running_run(string $run_type): ?array
    {
        $wpdb = $this->wpdb;
        // Treat both "queued" and "running" as pending to prevent concurrent runs.
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE run_type=%s AND status IN ('queued','running') ORDER BY id DESC LIMIT 1",
                $this->runs,
                $run_type
            ),
            ARRAY_A
        );
        return is_array($row) ? $row : null;
    }

    /**
     * Insert findings for a run - OPTIMIZED with batch insert.
     *
     * @param array<int,array<string,mixed>> $findings
     */
    public function insert_findings(int $run_id, array $findings): void
    {
        if (empty($findings)) {
            return;
        }

        $now = current_time('mysql');

        // Process in batches to avoid max packet size issues
        $batch_size = 100;
        $chunks = array_chunk($findings, $batch_size);

        foreach ($chunks as $chunk) {
            $this->insert_findings_batch($run_id, $chunk, $now);
        }
    }

    /**
     * Remove existing findings for a run (used to keep re-runs idempotent).
     */
    public function clear_findings_for_run(int $run_id): void
    {
        if ($run_id <= 0) {
            return;
        }

        $this->wpdb->delete($this->findings, ['run_id' => $run_id], ['%d']);
    }

    /**
     * Batch insert findings (private helper).
     *
     * @param array<int,array<string,mixed>> $findings
     */
    private function insert_findings_batch(int $run_id, array $findings, string $now): void
    {
        if (empty($findings)) {
            return;
        }

        // v1.0.80: check if `location` column exists — Hostinger shared
        // hosting blocks ALTER TABLE, so the column may not be there yet.
        // If missing, INSERT without it to avoid silent failure (same
        // pattern as the outbound_links_count fix in v1.0.76).
        $has_location_col = $this->column_exists($this->findings, 'location');

        // Insert one row at a time via $wpdb->insert() (method form: the table
        // is a parameter and values are bound by format, so there is no raw SQL
        // and nothing for the injection sniffs to flag). This mirrors how
        // insert_crawl_url() already writes the larger crawl table — findings
        // per run are far fewer, so the per-row cost is negligible.
        foreach ($findings as $f) {
            if (empty($f['id']) || empty($f['title']) || empty($f['category']) || empty($f['severity'])) {
                continue;
            }

            // v3.3.57 — honor the lifecycle status derived by the Runner
            // (open / mitigated / parked). Anything else collapses to open.
            $status = strtolower((string) ($f['status'] ?? 'open'));
            if (!in_array($status, ['open', 'mitigated', 'parked'], true)) {
                $status = 'open';
            }
            // Carry ignore status across runs (done does not carry).
            $prev = $this->get_last_status_for_finding((string) $f['id']);
            if ($prev === 'ignored') {
                $status = 'ignored';
            }

            $row = [
                'run_id'               => $run_id,
                'finding_id'           => (string) $f['id'],
                'check_id'             => isset($f['check_id']) ? (string) $f['check_id'] : null,
                'title'                => (string) $f['title'],
                'category'             => (string) $f['category'],
                'severity'             => (string) $f['severity'],
                'score_impact'         => (int) ($f['score_impact'] ?? 0),
                'status'               => $status,
                'evidence'             => isset($f['evidence']) ? wp_json_encode($f['evidence']) : null,
                'why_it_matters'       => (string) ($f['why_it_matters'] ?? ''),
                'recommendation_steps' => isset($f['recommendation_steps']) ? wp_json_encode($f['recommendation_steps']) : null,
                'retest'               => isset($f['retest']) ? wp_json_encode($f['retest']) : null,
                'created_at'           => $now,
                'updated_at'           => $now,
            ];
            $formats = ['%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s'];

            // $wpdb->insert() reads columns from the array keys, so appending
            // `location` only when the column exists keeps the format aligned.
            if ($has_location_col) {
                // 4.0.17: a location can be a filesystem path (large image,
                // writable directory). Redact it before it reaches the UI or
                // a client report.
                $location = (string) ($f['location'] ?? '');
                $row['location'] = $location !== '' ? Redactor::redact($location, 300) : '';
                $formats[] = '%s';
            }

            $this->wpdb->insert($this->findings, $row, $formats);
        }
    }

    private function get_last_status_for_finding(string $finding_id): ?string
    {
        $wpdb = $this->wpdb;
        $v = $wpdb->get_var($wpdb->prepare("SELECT status FROM %i WHERE finding_id=%s ORDER BY id DESC LIMIT 1", $this->findings, $finding_id));
        return is_string($v) ? $v : null;
    }

    /**
     * @param array<string,mixed> $filters
     * @return array{rows: array<int,array<string,mixed>>, total: int}
     */
    public function get_findings(array $filters, int $paged, int $per_page): array
    {
        $wpdb = $this->wpdb;

        // Optional filters as self-disabling literal guards: an omitted filter
        // passes its neutral value (0 / '') and "( %d = 0 OR col = %d )" makes
        // that column a no-op. The query string stays a single literal (nothing
        // interpolated), the table is bound via %i, and every value is bound by
        // prepare() — so the PreparedSQL injection sniffs verify it directly.
        $run_id   = isset($filters['run_id'])   ? (int) $filters['run_id']      : 0;
        $category = isset($filters['category']) ? (string) $filters['category'] : '';
        $severity = isset($filters['severity']) ? (string) $filters['severity'] : '';
        $status   = isset($filters['status'])   ? (string) $filters['status']   : '';

        $offset = max(0, ($paged - 1) * $per_page);
        $limit  = max(1, min(200, $per_page));

        $total = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i
                 WHERE ( %d = 0  OR run_id   = %d )
                   AND ( %s = '' OR category = %s )
                   AND ( %s = '' OR severity = %s )
                   AND ( %s = '' OR status   = %s )",
                $this->findings,
                $run_id,
                $run_id,
                $category,
                $category,
                $severity,
                $severity,
                $status,
                $status
            )
        );

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM %i
                 WHERE ( %d = 0  OR run_id   = %d )
                   AND ( %s = '' OR category = %s )
                   AND ( %s = '' OR severity = %s )
                   AND ( %s = '' OR status   = %s )
                 ORDER BY FIELD(severity,'high','med','low','info'), score_impact ASC, id DESC
                 LIMIT %d OFFSET %d",
                $this->findings,
                $run_id,
                $run_id,
                $category,
                $category,
                $severity,
                $severity,
                $status,
                $status,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return [
            'rows' => is_array($rows) ? array_map([$this, 'hydrate_finding_row'], $rows) : [],
            'total' => $total,
        ];
    }

    /**
     * @return array<string,mixed>|null
     */
    public function get_finding_row(int $row_id): ?array
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id=%d", $this->findings, $row_id), ARRAY_A);
        return is_array($row) ? $this->hydrate_finding_row($row) : null;
    }

    public function update_finding_status(int $row_id, string $status): bool
    {
        $status = in_array($status, ['open', 'done', 'ignored'], true) ? $status : 'open';
        $updated = $this->wpdb->update(
            $this->findings,
            ['status' => $status, 'updated_at' => current_time('mysql')],
            ['id' => $row_id],
            ['%s', '%s'],
            ['%d']
        );
        return $updated !== false;
    }

    /**
     * v2.1.3 — when a new audit run completes, mark any open finding rows
     * from the most recent prior completed audit as `done` if their
     * finding_id is NOT in the set of finding_ids produced by the new run.
     * This is the "auto-close on fix" behavior the user requested: no manual
     * closure needed when the fix is verified by the next scan.
     *
     * Only acts on the SINGLE most recent prior audit to avoid stale
     * cascading updates across the entire history.
     *
     * @param int           $current_run_id        the new completed run ID
     * @param array<int,string> $current_finding_ids  finding_ids found in the new run
     * @return int  number of rows auto-resolved
     */
    /**
     * The most recent completed audit run before the given run, or 0.
     */
    public function previous_completed_audit_run_id(int $current_run_id): int
    {
        $wpdb = $this->wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM %i
                 WHERE id < %d AND run_type = %s AND status = %s
                 ORDER BY id DESC LIMIT 1",
                $this->runs,
                $current_run_id,
                'audit',
                'completed'
            )
        );
    }

    public function auto_resolve_findings_not_in_set(int $current_run_id, array $current_finding_ids): int
    {
        $wpdb = $this->wpdb;
        // Locate the most recent prior completed audit run.
        $previous_run_id = $this->previous_completed_audit_run_id($current_run_id);
        if ($previous_run_id <= 0) {
            return 0;
        }

        $current_set = [];
        foreach ($current_finding_ids as $fid) {
            $fid = (string) $fid;
            if ($fid !== '') {
                $current_set[$fid] = true;
            }
        }

        // Pull only finding_id + id columns from the previous run for memory
        // efficiency (some installs have thousands of findings).
        // v3.3.57 — mitigated/parked findings from the previous run that no
        // longer appear also count as auto-resolved, not just open ones.
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, finding_id FROM %i WHERE run_id = %d AND status IN (%s, %s, %s)",
                $this->findings,
                $previous_run_id,
                'open',
                'mitigated',
                'parked'
            ),
            ARRAY_A
        );
        if (!is_array($rows) || empty($rows)) {
            return 0;
        }

        $resolved = 0;
        foreach ($rows as $row) {
            $fid = (string) ($row['finding_id'] ?? '');
            if ($fid === '' || isset($current_set[$fid])) {
                continue;
            }
            $rid = (int) ($row['id'] ?? 0);
            if ($rid <= 0) {
                continue;
            }
            $ok = $wpdb->update(
                $this->findings,
                [
                    'status'     => 'done',
                    'updated_at' => current_time('mysql'),
                ],
                ['id' => $rid],
                ['%s', '%s'],
                ['%d']
            );
            if ($ok !== false) {
                $resolved++;
            }
        }
        return $resolved;
    }

    /**
     * Update finding evidence and metadata after re-test.
     *
     * @param array<string,mixed> $patch
     */
    public function update_finding_patch(int $row_id, array $patch): bool
    {
        $data = [];
        if (isset($patch['evidence'])) {
            $data['evidence'] = wp_json_encode($patch['evidence']);
        }
        if (isset($patch['why_it_matters'])) {
            $data['why_it_matters'] = (string) $patch['why_it_matters'];
        }
        if (isset($patch['recommendation_steps'])) {
            $data['recommendation_steps'] = wp_json_encode($patch['recommendation_steps']);
        }
        if (isset($patch['retest'])) {
            $data['retest'] = wp_json_encode($patch['retest']);
        }
        if (isset($patch['title'])) {
            $data['title'] = (string) $patch['title'];
        }
        if (isset($patch['score_impact'])) {
            $data['score_impact'] = (int) $patch['score_impact'];
        }
        if (isset($patch['severity'])) {
            $data['severity'] = (string) $patch['severity'];
        }
        if (isset($patch['category'])) {
            $data['category'] = (string) $patch['category'];
        }

        $data['updated_at'] = current_time('mysql');

        if (empty($data)) {
            return false;
        }

        $updated = $this->wpdb->update($this->findings, $data, ['id' => $row_id]);
        return $updated !== false;
    }

    /**
     * Count findings with optional filters.
     *
     * @param array<string,mixed> $filters
     */
    public function count_findings(array $filters = []): int
    {
        $wpdb = $this->wpdb;

        // Self-disabling literal guards (see get_findings): omitted filters pass
        // 0 / '' and become no-ops. Single literal query string, table via %i,
        // every value bound by prepare() — no suppression needed.
        $run_id   = isset($filters['run_id'])   ? (int) $filters['run_id']      : 0;
        $status   = isset($filters['status'])   ? (string) $filters['status']   : '';
        $category = isset($filters['category']) ? (string) $filters['category'] : '';
        $severity = isset($filters['severity']) ? (string) $filters['severity'] : '';

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i
                 WHERE ( %d = 0  OR run_id   = %d )
                   AND ( %s = '' OR status   = %s )
                   AND ( %s = '' OR category = %s )
                   AND ( %s = '' OR severity = %s )",
                $this->findings,
                $run_id,
                $run_id,
                $status,
                $status,
                $category,
                $category,
                $severity,
                $severity
            )
        );
    }

    /**
     * Get next open finding from latest audit run.
     *
     * @return array<string,mixed>|null
     */
    public function get_next_open_finding(): ?array
    {
        $wpdb = $this->wpdb;
        // First, try to use the latest *completed* audit run
        $latest = $this->get_latest_completed_run('audit');

        // Fallback: if no completed run, try latest run of any status
        if (!$latest) {
            $latest = $this->get_latest_run('audit');
            if (!$latest) {
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log('Aetos SEO: get_next_open_finding() - no audit runs found'); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
                return null;
            }
        }

        $run_id = (int) $latest['id'];

        // Prioritize severity and impact
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE run_id=%d AND status='open' ORDER BY FIELD(severity,'high','med','low','info'), score_impact ASC, id ASC LIMIT 1",
                $this->findings,
                $run_id
            ),
            ARRAY_A
        );

        if (!is_array($row)) {
            // Debug: log if findings exist but couldn't be loaded
            $count = $this->count_findings(['run_id' => $run_id, 'status' => 'open']);
            if ($count > 0) {
    if (defined('WP_DEBUG') && WP_DEBUG) { error_log("Aetos SEO: get_next_open_finding() - found {$count} open findings but query returned null"); } // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- WP_DEBUG-guarded debug logging only
            }
            return null;
        }

        return $this->hydrate_finding_row($row);
    }

    /**
     * Insert crawl url row.
     *
     * @param array<string,mixed> $data
     */
    public function insert_crawl_url(int $run_id, array $data): void
    {
        $normalized = (string) ($data['normalized_url'] ?? '');
        $norm_hash = $normalized !== '' ? md5($normalized) : null;

        // v1.0.76: check if outbound_links_count column exists (added in 1.0.69).
        // On sites that upgraded without re-activation, dbDelta may not have run,
        // so the column is missing and ALL inserts fail silently — causing 0 crawl
        // rows in the DB despite "197 processed" in the UI.
        $has_outbound_col = $this->column_exists($this->crawl, 'outbound_links_count');

        $row = [
            'run_id' => $run_id,
            'url' => (string) ($data['url'] ?? ''),
            'normalized_url' => $normalized,
            'normalized_url_hash' => $norm_hash,
            'status_code' => (int) ($data['status_code'] ?? 0),
            'redirect_chain' => isset($data['redirect_chain']) ? wp_json_encode($data['redirect_chain']) : null,
            'canonical' => (string) ($data['canonical'] ?? ''),
            'robots_noindex' => !empty($data['robots_noindex']) ? 1 : 0,
            'title' => (string) ($data['title'] ?? ''),
            'meta_desc' => (string) ($data['meta_desc'] ?? ''),
            'h1_count' => (int) ($data['h1_count'] ?? 0),
            'h2_count' => (int) ($data['h2_count'] ?? 0),
            'h3_count' => (int) ($data['h3_count'] ?? 0),
            'word_count' => (int) ($data['word_count'] ?? 0),
            'images_missing_alt' => (int) ($data['images_missing_alt'] ?? 0),
            'images_total' => (int) ($data['images_total'] ?? 0),
            'jsonld_types' => isset($data['jsonld_types']) ? wp_json_encode($data['jsonld_types']) : null,
            'og_title' => (string) ($data['og_title'] ?? ''),
            'og_description' => (string) ($data['og_description'] ?? ''),
            'og_image' => (string) ($data['og_image'] ?? ''),
            'og_type' => (string) ($data['og_type'] ?? ''),
            'twitter_card' => (string) ($data['twitter_card'] ?? ''),
            'lang' => (string) ($data['lang'] ?? ''),
            'hreflang_count' => (int) ($data['hreflang_count'] ?? 0),
            'author_name' => (string) ($data['author_name'] ?? ''),
            'article_modified_time' => (string) ($data['article_modified_time'] ?? ''),
            'discovered_from' => (string) ($data['discovered_from'] ?? ''),
            'created_at' => current_time('mysql'),
        ];
        $formats = ['%d', '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s'];

        if ($has_outbound_col) {
            $pos = array_search('author_name', array_keys($row), true);
            $before = array_slice($row, 0, $pos, true);
            $after = array_slice($row, $pos, null, true);
            $row = $before + ['outbound_links_count' => (int) ($data['outbound_links_count'] ?? 0)] + $after;
            array_splice($formats, $pos, 0, ['%d']);
        }

        // v1.0.77: store full analysis JSON so audit reuse gets ALL fields
        // (webp_images, asset_counts, srcset_count, jsonld_raw, internal_links,
        // css details, etc.) — not just the 20 DB columns.
        // v1.0.91: jsonld_raw is REQUIRED by LocalSeoCheck, AdvancedSchemaCheck,
        // EeatCheck, AiFriendlyCheck, VoiceSearchCheck — they walk the JSON-LD
        // tree to verify presence of address/geo/openingHours/sameAs/Speakable/
        // reviewedBy/etc. Stripping it caused systemic false positives on
        // schema-rich sites. We now keep it but cap each block at 8 KB and the
        // total at 25 KB so the analysis_json column stays under ~50 KB.
        if ($this->column_exists($this->crawl, 'analysis_json')) {
            $analysis_payload = $data;
            // v1.0.94 — cap with VALID JSON output. The v1.0.91 implementation
            // byte-truncated then appended `"_truncated":true}` which produced
            // invalid JSON; hydrate_crawl_row's json_decode then dropped the
            // block entirely, defeating the cap's intent. We now skip-on-too-
            // big rather than truncate (safer than producing invalid JSON), and
            // raise the per-block cap to 16 KB / total to 50 KB to accommodate
            // the v1.0.92 expanded compact_jsonld_block keep list on schema-
            // rich pages (Org+@graph with 30 entries can hit ~10-15 KB).
            if (isset($analysis_payload['jsonld_raw']) && is_array($analysis_payload['jsonld_raw'])) {
                $capped_blocks = [];
                $total_bytes = 0;
                $per_block_cap = 16384;  // 16 KB per block
                $total_cap = 51200;      // 50 KB total
                foreach ($analysis_payload['jsonld_raw'] as $block) {
                    $encoded = is_string($block) ? $block : (string) wp_json_encode($block);
                    if (!is_string($encoded) || $encoded === '' || $encoded === 'false' || $encoded === 'null') {
                        continue;
                    }
                    if (strlen($encoded) > $per_block_cap) {
                        // Skip oversized blocks rather than corrupt the JSON.
                        // The block summary marker stays valid and parseable.
                        $encoded = wp_json_encode([
                            '_skipped' => true,
                            '_reason' => 'exceeded_per_block_cap',
                            '_size_bytes' => strlen($encoded),
                            '_cap_bytes' => $per_block_cap,
                        ]);
                    }
                    $total_bytes += strlen($encoded);
                    if ($total_bytes > $total_cap) {
                        break;
                    }
                    $capped_blocks[] = $encoded;
                }
                $analysis_payload['jsonld_raw'] = $capped_blocks;
            }
            // v1.0.78: keep internal_links but cap at 50 URLs (link graph checks
            // need them for orphan/authority detection). Strip anchor text.
            if (isset($analysis_payload['internal_links']) && is_array($analysis_payload['internal_links'])) {
                $links = [];
                foreach ($analysis_payload['internal_links'] as $lnk) {
                    $links[] = is_string($lnk) ? $lnk : (string) ($lnk['url'] ?? $lnk['href'] ?? '');
                    if (count($links) >= 50) break;
                }
                $analysis_payload['internal_links'] = array_values(array_filter($links));
            }
            $pos2 = array_search('author_name', array_keys($row), true);
            $before2 = array_slice($row, 0, $pos2, true);
            $after2 = array_slice($row, $pos2, null, true);
            $row = $before2 + ['analysis_json' => wp_json_encode($analysis_payload)] + $after2;
            array_splice($formats, $pos2, 0, ['%s']);
        }

        $this->wpdb->insert($this->crawl, $row, $formats);
    }

    /**
     * v1.0.76: check if a column exists in a table (cached per request).
     */
    private function column_exists(string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;
        if (isset($cache[$key])) {
            return $cache[$key];
        }
        $wpdb = $this->wpdb;
        $result = $wpdb->get_var(
            $wpdb->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $table, $column)
        );
        $cache[$key] = ((int) $result) > 0;
        return $cache[$key];
    }

    public function clear_crawl_for_run(int $run_id): void
    {
        $wpdb = $this->wpdb;
        $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE run_id=%d", $this->crawl, $run_id));
        // Also clear persisted crawl state for this run (queue/visited/etc).
        $this->delete_crawl_state($run_id);
    }

    public function count_crawl_rows(int $run_id): int
    {
        $wpdb = $this->wpdb;
        return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i WHERE run_id=%d", $this->crawl, $run_id));
    }

    /**
     * @return array{rows: array<int,array<string,mixed>>, total: int}
     */
    public function get_crawl_urls(int $run_id, int $paged, int $per_page): array
    {
        $wpdb = $this->wpdb;
        $offset = max(0, ($paged - 1) * $per_page);
        $limit = max(1, min(200, $per_page));

        $total = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i WHERE run_id=%d", $this->crawl, $run_id));
        $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE run_id=%d ORDER BY id DESC LIMIT %d OFFSET %d", $this->crawl, $run_id, $limit, $offset), ARRAY_A);
        $rows = is_array($rows) ? array_map([$this, 'hydrate_crawl_row'], $rows) : [];

        return ['rows' => $rows, 'total' => $total];
    }

    /**
     * v2.1.1 — return analysis_json from the most recent crawl row, used by
     * DashboardPage::detect_stale_crawl_rows() to detect pre-collector_version
     * data and prompt the user to re-crawl.
     *
     * @return array<string,mixed> Empty array if no crawl rows exist.
     */
    public function get_latest_crawl_row_meta(): array
    {
        if (!$this->column_exists($this->crawl, 'analysis_json')) {
            return [];
        }
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row($wpdb->prepare("SELECT analysis_json FROM %i ORDER BY id DESC LIMIT 1", $this->crawl), ARRAY_A);
        return is_array($row) ? $row : [];
    }

    /**
     * v1.0.91: Hydrate a finding DB row.
     *
     * Casts numeric columns to int (the MySQL driver returns them as strings
     * by default, which broke downstream score arithmetic and JSON exports).
     * Also decodes JSON columns (evidence, recommendation_steps, retest)
     * into associative arrays so consumers don't have to decode again.
     *
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    private function hydrate_finding_row(array $row): array
    {
        foreach (['id', 'run_id', 'score_impact'] as $intkey) {
            if (isset($row[$intkey])) {
                $row[$intkey] = (int) $row[$intkey];
            }
        }
        foreach (['evidence', 'recommendation_steps', 'retest'] as $jsonkey) {
            if (isset($row[$jsonkey]) && is_string($row[$jsonkey]) && $row[$jsonkey] !== '') {
                $decoded = json_decode($row[$jsonkey], true);
                if (is_array($decoded)) {
                    $row[$jsonkey] = $decoded;
                }
            }
        }
        return self::localize_finding($row, I18n::lang());
    }

    /**
     * 4.0.18: return a hydrated finding row with its user-facing text in
     * $lang. Rows written since 4.0.18 carry both languages under
     * evidence._i18n; the map moves to $row['i18n'] and the chosen language
     * replaces title, explanation, steps, retest and the language-specific
     * evidence keys. `text_language` tells callers which language the row
     * text is actually in (older rows keep their scan language).
     *
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    public static function localize_finding(array $row, string $lang): array
    {
        $evidence = isset($row['evidence']) && is_array($row['evidence']) ? $row['evidence'] : null;
        $map = null;
        if ($evidence !== null && isset($evidence['_i18n']) && is_array($evidence['_i18n'])) {
            $map = $evidence['_i18n'];
            unset($evidence['_i18n']);
            $row['evidence'] = $evidence;
            $row['i18n'] = $map;
        } elseif (isset($row['i18n']) && is_array($row['i18n'])) {
            $map = $row['i18n'];
        }
        if ($map === null || !isset($map[$lang]) || !is_array($map[$lang])) {
            if (!isset($row['text_language'])) {
                $row['text_language'] = '';
            }
            return $row;
        }
        $texts = $map[$lang];
        foreach (['title', 'why_it_matters'] as $key) {
            if (isset($texts[$key]) && is_string($texts[$key]) && $texts[$key] !== '') {
                $row[$key] = $texts[$key];
            }
        }
        foreach (['recommendation_steps', 'retest'] as $key) {
            if (isset($texts[$key]) && is_array($texts[$key])) {
                $row[$key] = $texts[$key];
            }
        }
        if (isset($texts['evidence']) && is_array($texts['evidence']) && is_array($row['evidence'] ?? null)) {
            foreach ($texts['evidence'] as $key => $value) {
                $row['evidence'][$key] = $value;
            }
        }
        $row['text_language'] = $lang;
        return $row;
    }

    /**
     * v1.0.91: Hydrate a raw crawl DB row by unpacking the analysis_json column
     * into top-level array keys. This restores fields that audit checks expect
     * to find directly on $row (e.g. jsonld_raw, internal_links, asset_counts,
     * webp_images, srcset_count). Numeric-typed columns are cast to int so
     * downstream consumers don't have to coerce strings → ints.
     *
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    private function hydrate_crawl_row(array $row): array
    {
        if (isset($row['analysis_json']) && is_string($row['analysis_json']) && $row['analysis_json'] !== '') {
            $decoded = json_decode($row['analysis_json'], true);
            if (is_array($decoded)) {
                foreach ($decoded as $k => $v) {
                    // Don't overwrite existing direct columns.
                    if (!array_key_exists($k, $row) || $row[$k] === null || $row[$k] === '') {
                        $row[$k] = $v;
                    }
                }
                // jsonld_raw was stored as an array of JSON strings; decode for checks.
                if (isset($row['jsonld_raw']) && is_array($row['jsonld_raw'])) {
                    $decoded_blocks = [];
                    foreach ($row['jsonld_raw'] as $blk) {
                        if (is_string($blk)) {
                            $b = json_decode($blk, true);
                            $decoded_blocks[] = is_array($b) ? $b : $blk;
                        } else {
                            $decoded_blocks[] = $blk;
                        }
                    }
                    $row['jsonld_raw'] = $decoded_blocks;
                }
            }
        }
        // jsonld_types is JSON-encoded in its own column; decode if not already.
        if (isset($row['jsonld_types']) && is_string($row['jsonld_types'])) {
            $jt = json_decode($row['jsonld_types'], true);
            $row['jsonld_types'] = is_array($jt) ? $jt : [];
        }
        // Cast common numeric columns so checks/exporters get ints, not strings.
        foreach (['status_code', 'h1_count', 'h2_count', 'h3_count', 'word_count',
                  'images_missing_alt', 'images_total', 'hreflang_count',
                  'outbound_links_count', 'robots_noindex', 'run_id', 'id'] as $intkey) {
            if (isset($row[$intkey])) {
                $row[$intkey] = (int) $row[$intkey];
            }
        }
        return $row;
    }

    /**
     * Get all crawl rows in deterministic chunks so large sites are fully readable.
     *
     * @return array<int,array<string,mixed>>
     */
    public function get_all_crawl_rows(int $run_id): array
    {
        $wpdb = $this->wpdb;
        $count = $this->count_crawl_rows($run_id);
        if ($count <= 0) {
            return [];
        }

        if ($count <= 250) {
            $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE run_id=%d ORDER BY id ASC", $this->crawl, $run_id), ARRAY_A);
            return is_array($rows) ? array_map([$this, 'hydrate_crawl_row'], $rows) : [];
        }

        $all_rows = [];
        $chunk_size = 500;
        for ($offset = 0; $offset < $count; $offset += $chunk_size) {
            $chunk = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM %i WHERE run_id=%d ORDER BY id ASC LIMIT %d OFFSET %d",
                    $this->crawl,
                    $run_id,
                    $chunk_size,
                    $offset
                ),
                ARRAY_A
            );
            if (!is_array($chunk) || empty($chunk)) {
                break;
            }
            foreach ($chunk as $r) {
                $all_rows[] = $this->hydrate_crawl_row($r);
            }
        }

        return $all_rows;
    }


    /**
     * @return array<int,array<string,mixed>>
     */
    public function get_findings_for_run(int $run_id, string $status_filter = ''): array
    {
        $wpdb = $this->wpdb;

        // Single literal query; the optional status filter is a self-disabling
        // guard ("( %s = '' OR status = %s )"), so nothing is interpolated.
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM %i
                 WHERE run_id = %d
                   AND ( %s = '' OR status = %s )
                 ORDER BY FIELD(severity,'high','med','low','info'), score_impact ASC",
                $this->findings,
                $run_id,
                $status_filter,
                $status_filter
            ),
            ARRAY_A
        );
        return is_array($rows) ? array_map([$this, 'hydrate_finding_row'], $rows) : [];
    }

    /**
     * Get finding row by finding_id within latest audit run.
     *
     * @return array<string,mixed>|null
     */
    public function get_latest_audit_finding_by_id(string $finding_id): ?array
    {
        $wpdb = $this->wpdb;
        // Use the latest *completed* audit run to ensure findings exist.
        $latest = $this->get_latest_completed_run('audit');
        if (!$latest) {
            return null;
        }
        $run_id = (int) $latest['id'];
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE run_id=%d AND finding_id=%s ORDER BY id DESC LIMIT 1", $this->findings, $run_id, $finding_id), ARRAY_A);
        return is_array($row) ? $this->hydrate_finding_row($row) : null;
    }


    /**
     * Get a specific run by ID.
     *
     * @return array<string,mixed>|null
     */
    public function get_run(int $run_id): ?array
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id=%d", $this->runs, $run_id), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    /**
     * v2.8.2 — Returns the user-visible sequence number of an audit/crawl run.
     *
     * Pre-2.8.2 the dashboard showed `Run ID: 4` after a clean install
     * because the underlying MySQL `id` is shared with `import` rows that
     * the IntegrationManager creates when an integration is connected (3
     * runs allocated for analytics and related integration sync). The
     * raw id is fine internally but confuses users who think their reset
     * didn't work.
     *
     * This counts only `audit` + `crawl` rows up to and including the
     * given id, so the first audit after a reset shows as `#1` regardless
     * of how many import rows preceded it.
     */
    public function get_run_display_number(int $run_id): int
    {
        if ($run_id <= 0) {
            return 0;
        }
        $wpdb = $this->wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i
                 WHERE id <= %d AND run_type IN ('audit','crawl')",
                $this->runs,
                $run_id
            )
        );
    }

    /**
     * Get a finding row by run_id + finding_id.
     *
     * @return array<string,mixed>|null
     */
    public function get_finding_by_finding_id(int $run_id, string $finding_id): ?array
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE run_id=%d AND finding_id=%s ORDER BY id DESC LIMIT 1",
                $this->findings,
                $run_id,
                $finding_id
            ),
            ARRAY_A
        );
        return is_array($row) ? $this->hydrate_finding_row($row) : null;
    }


    public function get_last_db_error(): string
    {
        $err = isset($this->wpdb->last_error) ? (string) $this->wpdb->last_error : '';
        return trim($err);
    }


}