<?php
/**
 * Cases (§6.4 K1-a) — the WordPress-facing half of the case model.
 *
 * GUARDFORGE_Case_Model (class-case-model.php) is the pure rulebook: given
 * already-fetched rows, it decides what a case's identity is, what its
 * evidence is, and how confident to be. This class is the glue that fetches
 * those rows from the modules that already write them — the last integrity
 * scan's summary option, the vulnerability mirror's find_matches, and the
 * login log — and turns the model's candidates into rows in
 * guardforge_cases / guardforge_case_events.
 *
 * sync is idempotent and safe to call often: it is wired to its own daily
 * cron AND to the Cases screen's own render (a GET that reads options and a
 * few bounded queries, same cost class as the Hardening Score card
 * recomputing on every Settings load). Calling it twice with no new
 * observations changes nothing new: find_or_create_case re-writes the same
 * confidence/coverage/last_seen, and add_evidence is a lookup-before-insert
 * keyed on (case_id, event_key), so no evidence row is ever duplicated.
 */

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

class GUARDFORGE_Cases {

    const CRON_HOOK = 'guardforge_cases_cron';

    /**
 * §6.4 K1-d review round 1, M2: when sync last actually ran. The
 * entry screen's "needs a decision" bucket is a plain read of this
 * table (deliberately no sync on that GET — see class-admin.php's own
 * comment on render_settings), so an empty bucket only ever answers
 * for the table AS OF this moment, not for "right now" in the world:
 * an integrity scan or a login spike between this timestamp and the
 * page load has not been turned into a case yet. Written unconditionally
 * at the end of every sync — cron or the Cases screen's own render —
 * so the entry screen can say honestly how current its answer is.
 */
    const LAST_SYNC_OPTION = 'guardforge_cases_last_sync';

    /**
 * The status each remedy in a decision event's payload put the case into.
 * GUARDFORGE_Case_Actions::transition records the target status itself;
 * its block-IP, lockdown and patch handlers record only the remedy, so
 * report_decision_statuses below needs this to read the moment a case
 * became 'mitigated' / 'fix_applied' out of the event that did it.
 */
    const REMEDY_STATUS = [
        'block_ip' => 'mitigated',
        'lockdown' => 'mitigated',
        'patch'    => 'fix_applied',
    ];

    private static $instance = null;

    public static function instance() {
        if ( self::$instance === null ) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct() {
        add_action( self::CRON_HOOK, [ $this, 'sync' ] );

        // Self-scheduled, the same way GUARDFORGE_Patch_Rule is: WordPress
        // does not re-run register_activation_hook on a plugin UPDATE, only
        // on a fresh activation, so a cron added after 1.2.0 would otherwise
        // never be scheduled on any site that already had GuardForge
        // installed. Cheap when already scheduled — one transient-free
        // wp_next_scheduled call.
        if ( ! wp_next_scheduled( self::CRON_HOOK ) ) {
            wp_schedule_event( time() + 4 * HOUR_IN_SECONDS, 'daily', self::CRON_HOOK );
        }
    }

    private function cases_table() {
        global $wpdb;
        return $wpdb->prefix . 'guardforge_cases';
    }

    private function events_table() {
        global $wpdb;
        return $wpdb->prefix . 'guardforge_case_events';
    }

    // ─────────────────────────── sync ────────────────────────────────────

    /**
 * Read every observation source and bring the case table up to date.
 *
 * §6.4 K1-c rides the same call: once new evidence is ingested, every
 * still-open case gets a chance to close through
 * GUARDFORGE_Case_Recheck — the one place allowed to write 'fixed'. This
 * runs here (not behind a separate button) so a recheck reasons about
 * the SAME freshly-read state sync itself just used, on the same
 * schedule (the Cases screen's own render and the daily cron) that
 * already made sync safe to call often.
 *
 * @return array{created:int,touched:int,rechecked:int}
 */
    public function sync() {
        $created     = 0;
        $touched     = 0;
        $created_ids = []; // Case ids opened by THIS call — see recheck_open_cases's own docblock for why a recheck treats them differently.

        foreach ( array_merge(
            $this->sync_file_changes(),
            $this->sync_vuln_matches(),
            $this->sync_login_series()
        ) as $candidate ) {
            [ $case_id, $is_new ] = $this->ingest_candidate( $candidate );
            if ( $is_new ) {
                $created++;
                $created_ids[] = $case_id;
            } else {
                $touched++;
            }
        }

        $result = [ 'created' => $created, 'touched' => $touched, 'rechecked' => $this->recheck_open_cases( $created_ids ) ];

        // §6.4 K1-d review round 1, M2: unconditional — even a sync that
        // created and rechecked nothing is still the moment the table was
        // last brought up to date, and that is exactly what the entry
        // screen needs to say when its own "needs a decision" bucket is
        // empty.
        update_option( self::LAST_SYNC_OPTION, current_time( 'mysql' ), false );

        return $result;
    }

    /**
 * Give every non-terminal case a recheck. GUARDFORGE_Case_Recheck
 * decides per case type whether the underlying condition is confirmed
 * gone (closes as 'fixed'), confirmed still present ('still_affected' —
 * status untouched, but the attempt is recorded), or currently
 * impossible to confirm either way ('unavailable' — moves a freshly
 * opened case to 'unverified', never to 'fixed'). Explained/fixed cases
 * are terminal and are never revisited.
 *
 * $just_created_ids are passed through to GUARDFORGE_Case_Recheck::apply
 * so an 'unavailable' verdict cannot mark a case 'unverified' before an
 * operator has ever seen it as 'open' — a case this very sync call just
 * opened has, by construction, nothing new to recheck against yet (a
 * login_series case's own lockout window has obviously not elapsed one
 * instant after its own evidence was recorded); that is a fact about
 * timing, not a fact about the case, and must not read as "cannot verify"
 * on the operator's very first look. The recheck still runs and still
 * records its case_event either way — only the status write is held
 * back, for this one verdict, for this one case, for this one call.
 *
 * @param int $just_created_ids
 * @return int How many rechecks changed a case's status (fixed or newly unverified).
 */
    private function recheck_open_cases( array $just_created_ids = [] ) {
        if ( ! class_exists( 'GUARDFORGE_Case_Recheck' ) ) {
            return 0; // Should not happen in production — guards test files that load GUARDFORGE_Cases without this class.
        }
        $changed = 0;
        foreach ( $this->list_cases() as $case ) {
            if ( GUARDFORGE_Case_Model::is_terminal( (string) $case['status'] ) ) {
                continue;
            }
            $before = (string) $case['status'];
            GUARDFORGE_Case_Recheck::apply( $case, in_array( (int) $case['id'], $just_created_ids, true ) );
            $after = $this->get_case( (int) $case['id'] );
            if ( $after && (string) $after['status'] !== $before ) {
                $changed++;
            }
        }
        return $changed;
    }

    /**
 * @param array $candidate One entry from a GUARDFORGE_Case_Model::candidates_from_* call.
 * @return array{0:int,1:bool} [case_id, was_newly_created]
 */
    private function ingest_candidate( array $candidate ) {
        [ $case_id, $is_new ] = $this->find_or_create_case( $candidate );
        foreach ( $candidate['evidence'] as $evidence ) {
            $this->add_evidence( $case_id, $evidence );
        }
        return [ $case_id, $is_new ];
    }

    // ─────────────────────── sources ──────────────────────────────────────

    /** @return array<int,array> */
    private function sync_file_changes() {
        if ( ! class_exists( 'GUARDFORGE_Integrity' ) ) {
            return [];
        }
        $summary = get_option( GUARDFORGE_Integrity::LAST_RESULT_OPTION, [] );
        if ( ! is_array( $summary ) ) {
            return [];
        }
        $flagged = (array) ( $summary['flagged'] ?? [] );
        // §6.4 K1-e: the scan's own local diff, already reconciled against
        // the published package for the installed version at scan time
        // (GUARDFORGE_Integrity::local_change_rows). A scan can flag
        // nothing against a published reference and still have found a
        // changed theme file nobody publishes checksums for, so the two
        // lists are independent reasons to keep going.
        $local = (array) ( $summary['local_changes'] ?? [] );
        if ( ! $flagged && ! $local ) {
            return [];
        }
        // §6.4 K1-e: which paths the reference comparison already owns, so
        // GUARDFORGE_Case_Model::explain_local_changes can leave them
        // alone — one file is one investigation, and that guarantee is
        // decided in the model, on this authoritative list, rather than by
        // de-duplicating two candidate lists after the fact.
        $flagged_paths = [];
        foreach ( $flagged as $row ) {
            $flagged_paths[ (string) ( $row['scope'] ?? '' ) . '|' . (string) ( $row['path'] ?? '' ) ] = true;
        }

        $context = [
            'scan_id'          => (int) get_option( GUARDFORGE_Integrity::SCAN_ID_OPTION, 0 ),
            'scanned_at'       => (string) ( $summary['scanned_at'] ?? '' ),
            'queued'           => (array) ( $summary['queued'] ?? [] ),
            'scope_extras_off' => class_exists( 'GUARDFORGE_Options' ) && ! GUARDFORGE_Options::get( 'integrity_scope_extras' ),
            // §6.4 K1-e: an operator's own stored approvals — GUARDFORGE_
            // Case_Model::candidates_from_integrity decides what to do
            // with them, this class only fetches them, the same split as
            // every other source below.
            'baselines'        => class_exists( 'GUARDFORGE_Trusted_Baseline' ) ? GUARDFORGE_Trusted_Baseline::instance()->all() : [],
            'flagged_paths'    => $flagged_paths,
        ];
        $out = GUARDFORGE_Case_Model::candidates_from_integrity( $flagged, $context );

        // The stored summary caps the flagged list (GUARDFORGE_Integrity::
        // MAX_SUMMARY_PATHS); flagged_total tells the truth about what was
        // left out, so every candidate built from this run carries it.
        $total = (int) ( $summary['flagged_total'] ?? count( $flagged ) );
        if ( $total > count( $flagged ) ) {
            $missing = $total - count( $flagged );
            foreach ( $out as &$candidate ) {
                $candidate['coverage'][] = sprintf(
                    /* translators: %d is the number of flagged files this run found but did not carry in the summary (capped list). */
                    _n(
                        '%d more flagged file from this scan is not shown here — the stored summary is capped.',
                        '%d more flagged files from this scan are not shown here — the stored summary is capped.',
                        $missing,
                        'guardforge'
                    ),
                    $missing
                );
            }
            unset( $candidate );
        }

        // §6.4 K1-e. Same case_key recipe as above; the model declines to
        // build a candidate for any path $context['flagged_paths'] names, so
        // a file that is both locally changed AND flagged stays ONE
        // investigation with the reference comparison's own reason on it.
        $local_out = GUARDFORGE_Case_Model::candidates_from_local_diff( $local, $context );

        $local_total = (int) ( $summary['local_changes_total'] ?? count( $local ) );
        if ( $local_total > count( $local ) ) {
            $missing = $local_total - count( $local );
            foreach ( $local_out as &$local_candidate ) {
                $local_candidate['coverage'][] = sprintf(
                    /* translators: %d is the number of locally changed files this run found but did not carry in the summary (capped list). */
                    _n(
                        '%d more file changed since the previous snapshot is not shown here — the stored summary is capped.',
                        '%d more files changed since the previous snapshot are not shown here — the stored summary is capped.',
                        $missing,
                        'guardforge'
                    ),
                    $missing
                );
            }
            unset( $local_candidate );
        }

        return array_merge( $out, $local_out );
    }

    /** @return array<int,array> */
    private function sync_vuln_matches() {
        if ( class_exists( 'GUARDFORGE_Vuln_Feed' ) && GUARDFORGE_Vuln_Feed::pro_feed_active() && class_exists( 'GUARDFORGE_Vuln_Db' ) ) {
            $hits  = GUARDFORGE_Vuln_Db::instance()->find_matches();
            $state = GUARDFORGE_Vuln_Db::state();
            $tier  = 'pro';
        } elseif ( class_exists( 'GUARDFORGE_Vuln_Feed' ) ) {
            $hits  = GUARDFORGE_Vuln_Feed::instance()->find_matches();
            $state = GUARDFORGE_Vuln_Feed::state();
            $tier  = 'free';
        } else {
            return [];
        }
        if ( ! $hits ) {
            return [];
        }
        return GUARDFORGE_Case_Model::candidates_from_vuln_matches( $hits, array_merge( $state, [ 'tier' => $tier ] ) );
    }

    /** @return array<int,array> */
    private function sync_login_series() {
        if ( ! class_exists( 'GUARDFORGE_Options' ) || ! GUARDFORGE_Options::get( 'bf_log_enabled' ) ) {
            return [];
        }
        $threshold = (int) GUARDFORGE_Options::get( 'bf_max_attempts' );
        $minutes   = max( 1, (int) GUARDFORGE_Options::get( 'bf_lockout_minutes' ) );
        $window    = $minutes * MINUTE_IN_SECONDS;

        global $wpdb;
        $table = $wpdb->prefix . 'guardforge_login_log';
        // Two windows of lookback: candidates_from_login_log itself refuses
        // anything spanning more than one window, so fetching exactly one
        // window would cut off a series that started a few minutes before
        // this sync tick and clip its evidence.
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- plugin-owned table ($wpdb->prefix + a literal) that no WordPress API reads; every value is a prepare placeholder or a fragment prepared on its own.
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, event_time, ip, username, result FROM {$table} WHERE event_time >= DATE_SUB(NOW(), INTERVAL %d MINUTE) AND result IN ('failed','locked','soft_locked','success') ORDER BY event_time ASC",
                $minutes * 2
            ),
            ARRAY_A
        );
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ( ! $rows ) {
            return [];
        }
        return GUARDFORGE_Case_Model::candidates_from_login_log( $rows, $threshold, $window );
    }

    // ─────────────────────── persistence ──────────────────────────────────

    /**
 * @param array $candidate
 * @return array{0:int,1:bool} [case_id, was_newly_created]
 */
    private function find_or_create_case( array $candidate ) {
        global $wpdb;
        $table = $this->cases_table();
        $now   = current_time( 'mysql' );

        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; the key is prepared.
        $existing = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, status FROM {$table} WHERE case_key = %s ORDER BY id DESC LIMIT 1",
            $candidate['case_key']
        ), ARRAY_A );
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        $type = $this->infer_type( $candidate );

        if ( $existing && ! GUARDFORGE_Case_Model::is_terminal( (string) $existing['status'] ) ) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned table; $wpdb escapes insert/update/replace/delete values itself, and there is nothing to cache on a write.
            $wpdb->update(
                $table,
                [
                    'confidence'        => $candidate['confidence'],
                    'confidence_reason' => $candidate['confidence_reason'],
                    'coverage_json'     => wp_json_encode( $candidate['coverage'] ),
                    'last_seen'         => $now,
                    'updated_at'        => $now,
                ],
                [ 'id' => (int) $existing['id'] ]
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
            return [ (int) $existing['id'], false ];
        }

        // The most recent case for this case_key IS terminal (explained/
        // fixed) — but "terminal" only means a HUMAN or a recheck already
        // closed it, not that every future match is a new recurrence.
        // all_evidence_already_known is what tells "the same stored read,
        // asked again" apart from "the condition genuinely came back": sync
        // re-derives every candidate from options/tables it re-reads on
        // every call (the Cases screen's own render among them), so a
        // candidate whose evidence is entirely event_keys the case has
        // already recorded is not new information, whichever terminal
        // status the case is in — a closed case's case_key matching a
        // candidate with even one UNSEEN event_key is the genuine
        // recurrence signal, not the mere fact that case_key matched again.

        // §6.4 review round 3, B1-r3: round 2 (B1-r2) scoped this dedup to
        // 'explained' only, on the reasoning that file_change's and
        // login_series' event_keys "already carry something that moves in
        // time (scan_id, the login_log row's own id), so [the dedup] is
        // moot" for 'fixed' cases of those types. That reasoning was
        // backwards. scan_id and a login_log row's own id do not change
        // between two READS of the same already-flagged row/burst — they
        // are exactly what makes the SAME scan (or the SAME lockout-window
        // burst, re-read on every Cases-screen render, `bf_lockout_minutes *
        // 2` of lookback) hash to the IDENTICAL event_key every single time.
        // Skipping the dedup for 'fixed' file_change/login_series cases
        // therefore did the opposite of what the comment claimed: it let
        // find_or_create_case insert a brand new 'fixed' case, from
        // evidence already on file, on every render — unbounded case/evidence
        // growth from a single burst (§6.4 review round 3 verdict, R3-P6).

        // The one type where the dedup must NOT apply to a 'fixed' case is
        // vuln_match, and for a different reason than "moves in time":
        // vuln_match's own event_key is {slug, cve/range, version} — nothing
        // in it ever advances between two finds of the exact same installed
        // version inside the exact same advisory range, because a plugin
        // rolled back to the vulnerable version reports the identical
        // version string it had before it was ever fixed. Applying the
        // dedup there would make 'fixed' permanent no matter how many times
        // the same vulnerable version returns — the bug round 2 (B1-r2)
        // itself caught and fixed by carving OUT the exemption below, rather
        // than by narrowing dedup down to 'explained' as round 2 did.

        // (case_key deliberately has no UNIQUE constraint — see the schema
        // comment — precisely so a genuine recurrence can insert a fresh
        // row regardless of which branch below reaches it.)
        $vuln_match_fixed_is_exempt = $existing
            && $type === 'vuln_match'
            && (string) $existing['status'] === 'fixed';

        if ( $existing && ! $vuln_match_fixed_is_exempt
            && $this->all_evidence_already_known( (int) $existing['id'], $candidate['evidence'] )
        ) {
            return [ (int) $existing['id'], false ];
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- plugin-owned table; $wpdb escapes insert/update/replace/delete values itself, and there is nothing to cache on a write.
        $wpdb->insert( $table, [
            'case_key'          => $candidate['case_key'],
            'type'              => $type,
            'status'            => 'open',
            'confidence'        => $candidate['confidence'],
            'confidence_reason' => $candidate['confidence_reason'],
            'subject'           => substr( (string) $candidate['subject'], 0, 190 ),
            'coverage_json'     => wp_json_encode( $candidate['coverage'] ),
            'assigned_user'     => 0,
            'first_seen'        => $now,
            'last_seen'         => $now,
            'created_at'        => $now,
            'updated_at'        => $now,
        ] );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
        return [ (int) $wpdb->insert_id, true ];
    }

    /**
 * True when every evidence entry a candidate would add to $case_id is
 * already recorded there — i.e. this candidate is the SAME stored
 * result read again, not a new recurrence. add_evidence's own
 * lookup-before-insert would no-op on each of these anyway; this check
 * exists so find_or_create_case can tell "nothing to do" apart from
 * "open a fresh case" for a terminal case, where letting the insert
 * happen would silently reopen (or duplicate) a closed investigation on
 * every page view of the same unrepaired rows.
 *
 * Called for BOTH terminal statuses — 'explained' and 'fixed' — with
 * exactly one exemption carved out by the caller: a 'fixed' vuln_match
 * case, whose event_key never advances between two finds of the same
 * installed version in the same advisory range (§6.4 review round 2,
 * B1-r2; the exemption's rationale is in find_or_create_case, not
 * here — this method itself is agnostic to which case reached it).
 *
 * Uses get_case_event_keys rather than get_case_events (§6.4 review
 * round 2, m5-r2): this method only ever compares event_key strings, so
 * there is no reason to also pull every other column and json_decode
 * a payload for each row just to throw it away.
 *
 * @param int $case_id
 * @param array $evidence candidate['evidence'] — always at least one entry.
 */
    private function all_evidence_already_known( $case_id, array $evidence ) {
        $known = $this->get_case_event_keys( $case_id );
        foreach ( $evidence as $item ) {
            if ( ! in_array( $item['event_key'], $known, true ) ) {
                return false;
            }
        }
        return true;
    }

    /** The evidence's own `source` names the type unambiguously — cheaper than re-deriving it from the case_key. */
    private function infer_type( array $candidate ) {
        $source = (string) ( $candidate['evidence'][0]['source'] ?? '' );
        switch ( $source ) {
            case 'integrity_scan':
                return 'file_change';
            case 'vuln_feed':
                return 'vuln_match';
            case 'login_log':
                return 'login_series';
        }
        return 'unknown';
    }

    /**
 * Insert one evidence row unless (case_id, event_key) is already there.
 * Look-up-before-insert rather than relying on the UNIQUE KEY to fail
 * silently: $wpdb->insert on a duplicate key does not throw in
 * WordPress, it just returns false, indistinguishable from a real error.
 */
    private function add_evidence( $case_id, array $evidence ) {
        global $wpdb;
        $table = $this->events_table();

        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; both values are prepared.
        $exists = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$table} WHERE case_id = %d AND event_key = %s",
            $case_id,
            $evidence['event_key']
        ) );
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ( $exists ) {
            return;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- plugin-owned table; $wpdb escapes insert/update/replace/delete values itself, and there is nothing to cache on a write.
        $wpdb->insert( $table, [
            'case_id'      => (int) $case_id,
            'event_key'    => $evidence['event_key'],
            'observed_at'  => $evidence['observed_at'] !== '' ? $evidence['observed_at'] : current_time( 'mysql' ),
            'source'       => $evidence['source'],
            'source_ref'   => substr( (string) $evidence['source_ref'], 0, 190 ),
            'summary'      => $evidence['summary'],
            'payload_json' => wp_json_encode( $evidence['payload'] ),
            'created_at'   => current_time( 'mysql' ),
        ] );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
    }

    // ─────────────────────────── reads ────────────────────────────────────

    /**
 * @param array{status?:string,type?:string,updated_since?:string} $filters WHERE clauses shared with count_cases.
 * 'updated_since' (§6.4 K1-d review round 1, m2) is a mysql datetime
 * string — first_screen_summary's own "closed inside the last N
 * days" window, expressed as a WHERE clause instead of a walk over
 * every row.
 * @return array{0:string,1:array} [SQL WHERE fragment, bound params]
 */
    private function case_filter_sql( array $filters ) {
        // Seeded with a placeholder of its own so every query built from this
        // goes through prepare — there is no "no filters" branch that runs
        // raw SQL, however harmless that SQL would be.
        $where  = [ '1 = %d' ];
        $params = [ 1 ];
        if ( ! empty( $filters['status'] ) ) {
            $where[]  = 'status = %s';
            $params[] = (string) $filters['status'];
        }
        if ( ! empty( $filters['type'] ) ) {
            $where[]  = 'type = %s';
            $params[] = (string) $filters['type'];
        }
        if ( ! empty( $filters['statuses'] ) && is_array( $filters['statuses'] ) ) {
            // The Suite results provider asks for "every status except the
            // terminal ones" in one query rather than nine — a dashboard
            // render is not the place for a query per status.
            $list = array_values( array_unique( array_filter( array_map( 'strval', $filters['statuses'] ) ) ) );
            if ( $list ) {
                $where[]  = 'status IN ( ' . implode( ', ', array_fill( 0, count( $list ), '%s' ) ) . ' )';
                $params   = array_merge( $params, $list );
            } else {
                // The caller asked for a set of statuses and none of them
                // survived sanitising ( [''], ['0'] ). Dropping the condition
                // would answer with EVERY row — a filter must fail closed.
                $where[] = '1 = 0';
            }
        }
        if ( ! empty( $filters['updated_since'] ) ) {
            $where[]  = 'updated_at >= %s';
            $params[] = (string) $filters['updated_since'];
        }
        return [ implode( ' AND ', $where ), $params ];
    }

    /**
 * @param array{status?:string,type?:string,updated_since?:string} $filters
 * @return int Total matching rows, ignoring limit/offset — for the Cases screen's own pagination.
 */
    public function count_cases( array $filters = [] ) {
        global $wpdb;
        [ $where, $params ] = $this->case_filter_sql( $filters );
        $sql = "SELECT COUNT(*) FROM {$this->cases_table()} WHERE {$where}";
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- $sql is this class's own literals (table = $wpdb->prefix + a literal, WHERE from case_filter_sql's fixed column list); every value is a placeholder filled by prepare.
        return (int) $wpdb->get_var( $wpdb->prepare( $sql, ...$params ) );
    }

    /**
 * limit/offset are opt-in and unbounded by default on purpose:
 * recheck_open_cases calls this with no filters at all to sweep every
 * open case in the system, and silently capping that sweep at one page
 * would leave cases beyond the cap never rechecked — a worse defect than
 * the admin screen this pagination is actually for. Only the Cases
 * screen itself passes 'limit'/'offset'.
 *
 * @param array{status?:string,type?:string,updated_since?:string,limit?:int,offset?:int} $filters
 * @return array<int,array<string,mixed>>
 */
    public function list_cases( array $filters = [] ) {
        global $wpdb;
        [ $where, $params ] = $this->case_filter_sql( $filters );
        // last_seen is stamped once per sync, so every case a sync touched
        // shares it. MySQL gives no order to rows that are equal in what
        // ORDER BY names (and a LIMIT can change the one it gives), so a
        // pager would show one case on two pages and another on none, and the
        // first screen's "newest five of a status" would be any five of the
        // tied ones. The newest case (highest id) goes first among equals;
        // first_screen_summary and the results provider break the same tie
        // the same way when they merge lists.
        $sql = "SELECT * FROM {$this->cases_table()} WHERE {$where} ORDER BY last_seen DESC, id DESC";
        if ( isset( $filters['limit'] ) ) {
            $sql     .= ' LIMIT %d';
            $params[] = max( 1, (int) $filters['limit'] );
            if ( ! empty( $filters['offset'] ) ) {
                $sql     .= ' OFFSET %d';
                $params[] = max( 0, (int) $filters['offset'] );
            }
        }
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- $sql is this class's own literals (table = $wpdb->prefix + a literal, WHERE from case_filter_sql's fixed column list, LIMIT/OFFSET as %d); every value is a placeholder filled by prepare.
        $rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ), ARRAY_A );
        return array_map( [ $this, 'hydrate_case' ], (array) $rows );
    }

    /**
 * How many "needs a decision" rows the entry screen actually shows
 * before its own "%d more cases" line — see PREVIEW_LIMIT's use below.
 */
    const FIRST_SCREEN_PREVIEW_LIMIT = 5;

    /**
 * §6.4 K1-d: the first screen's own read of this table — every case
 * bucketed into the two groups that screen shows, using
 * GUARDFORGE_Case_Model's own status lists so this can never drift from
 * the model by re-declaring a second copy of which statuses mean what.
 *
 * §6.4 K1-d review round 1, m2: this used to be list_cases with NO
 * filter at all — every column of every case row, json_decode'd via
 * hydrate_case, on the plugin's own most-visited screen, to show at
 * most 5 rows and two counts. That was the shape recheck_open_cases
 * needs (it really does have to see every open case, unbounded, to
 * sweep them all) — not the shape a summary card needs. This reads:
 *
 * - the "needs a decision" TOTAL from count_cases per status (three
 * small COUNT(*) queries — GUARDFORGE_Case_Model::
 * NEEDS_DECISION_STATUSES has three members, and case_filter_sql
 * only takes one status at a time, so a per-status count is the
 * existing primitive rather than a new IN(...) SQL path);
 * - the PREVIEW rows from list_cases(['status' => …, 'limit' =>
 * FIRST_SCREEN_PREVIEW_LIMIT]) per status, merged and re-sorted by
 * last_seen. This still surfaces the true newest N cases overall:
 * any case among the global newest N must, by definition, be among
 * its OWN status's newest N too, so fetching each status's own top
 * N and merging can never miss one — while every query stays
 * bounded to a small constant regardless of table size;
 * - the "verified" count straight from count_cases with the new
 * 'updated_since' filter (one COUNT(*) per terminal status, WHERE
 * updated_at >= the window's cutoff) — nobody ever reads the
 * verified ROWS themselves here (the partial only prints the
 * count), so there was never a reason to fetch them at all.
 *
 * 'acknowledged'/'assigned'/'mitigated'/'fix_applied' cases appear in
 * neither bucket — deliberately: they already carry a human decision in
 * progress, so they are visible on the Cases screen this card links to,
 * not repeated here as either "still needs a decision" (it does not,
 * somebody decided) or "verified" (K1-c has not confirmed it yet).
 *
 * §6.4 K1-d review round 1, M2: 'as_of' is LAST_SYNC_OPTION — the last
 * time sync actually ran (cron, or the Cases screen's own render).
 * This method itself never calls sync (the entry screen must not
 * write on a GET), so an empty "needs a decision" bucket is only ever
 * an honest answer for the table as of that timestamp, never a claim
 * about the present moment — the partial that prints "no open case"
 * must say so rather than assert "right now".
 *
 * @param int $verified_window_days Only 'fixed'/'explained' cases whose
 * updated_at falls inside this many days count as newly verified —
 * a case closed a year ago is not today's news.
 * @return array{needs_decision:array<int,array>,needs_decision_total:int,verified_count:int,as_of:string}
 */
    public function first_screen_summary( $verified_window_days = 30 ) {
        $cutoff_ts  = current_time( 'timestamp' ) - max( 1, (int) $verified_window_days ) * DAY_IN_SECONDS;
        $cutoff_sql = gmdate( 'Y-m-d H:i:s', $cutoff_ts );

        $needs_decision_total = 0;
        $needs_decision       = [];
        foreach ( GUARDFORGE_Case_Model::NEEDS_DECISION_STATUSES as $status ) {
            $needs_decision_total += $this->count_cases( [ 'status' => $status ] );
            $needs_decision        = array_merge(
                $needs_decision,
                $this->list_cases( [ 'status' => $status, 'limit' => self::FIRST_SCREEN_PREVIEW_LIMIT ] )
            );
        }
        // Newest first, and the same tie-break list_cases ends its ORDER BY
        // in (highest id first): the argument above that merging each status's
        // newest N cannot miss one of the global newest N holds only if both
        // sides rank equal last_seen the same way. Without it the five shown
        // were whichever tied cases the statuses happened to be read in.
        usort( $needs_decision, function ( $a, $b ) {
            $by_seen = strcmp( (string) ( $b['last_seen'] ?? '' ), (string) ( $a['last_seen'] ?? '' ) );
            if ( 0 !== $by_seen ) {
                return $by_seen;
            }
            return (int) ( $b['id'] ?? 0 ) <=> (int) ( $a['id'] ?? 0 );
        } );
        $needs_decision = array_slice( $needs_decision, 0, self::FIRST_SCREEN_PREVIEW_LIMIT );

        $verified_count = 0;
        foreach ( GUARDFORGE_Case_Model::TERMINAL_STATUSES as $status ) {
            $verified_count += $this->count_cases( [ 'status' => $status, 'updated_since' => $cutoff_sql ] );
        }

        return [
            'needs_decision'       => $needs_decision,
            'needs_decision_total' => $needs_decision_total,
            'verified_count'       => $verified_count,
            'as_of'                => (string) get_option( self::LAST_SYNC_OPTION, '' ),
        ];
    }

    public function get_case( $id ) {
        global $wpdb;
        $table = $this->cases_table();
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; the id is prepared.
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ), ARRAY_A );
        return $row ? $this->hydrate_case( $row ) : null;
    }

    /**
 * @return array<int,array<string,mixed>> Evidence timeline, oldest first.
 *
 * $rows is cast to array and assigned FIRST, then iterated by reference —
 * `foreach ( (array) $wpdb->get_results(...) as &$row )` looks equivalent
 * but silently decodes nothing: a foreach-by-reference over a cast
 * expression (not a variable) binds to a throwaway copy, so every
 * 'payload' write below would be lost and every caller reading
 * $event['payload'] (GUARDFORGE_Case_Actions' apply-fix remedies among
 * them) would silently see nothing. Caught by §6.4 K1-c's own tests,
 * which are the first callers to round-trip evidence through the real
 * payload_json encode/decode rather than a fixture that hands back
 * 'payload' pre-decoded.
 */
    public function get_case_events( $case_id ) {
        global $wpdb;
        $table = $this->events_table();
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; the id is prepared.
        $rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE case_id = %d ORDER BY observed_at ASC, id ASC", (int) $case_id ), ARRAY_A );
        foreach ( $rows as &$row ) {
            $row['payload'] = json_decode( (string) ( $row['payload_json'] ?? '' ), true );
        }
        unset( $row );
        return $rows;
    }

    /**
 * Just the event_key column for one case — §6.4 review round 2, m5-r2:
 * all_evidence_already_known is the only caller, and it never looks
 * at anything else a case_event row carries, so it has no reason to
 * pay for get_case_events's full SELECT * plus a json_decode per
 * row.
 *
 * @param int $case_id
 * @return string
 */
    private function get_case_event_keys( $case_id ) {
        global $wpdb;
        $table = $this->events_table();
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; the id is prepared.
        return (array) $wpdb->get_col( $wpdb->prepare( "SELECT event_key FROM {$table} WHERE case_id = %d", (int) $case_id ) );
    }

    private function hydrate_case( array $row ) {
        $row['coverage'] = json_decode( (string) ( $row['coverage_json'] ?? '' ), true );
        if ( ! is_array( $row['coverage'] ) ) {
            $row['coverage'] = [];
        }
        return $row;
    }

    // ─────────────────────────── writes used by decisions ────────────────

    /**
 * Applied by GUARDFORGE_Case_Actions after it has validated the
 * transition against GUARDFORGE_Case_Model::can_transition. Kept here,
 * not there, so every write to the table goes through one method.
 */
    public function update_status( $case_id, $status, $resolution_basis = '', $assigned_user = null ) {
        global $wpdb;
        $table = $this->cases_table();
        $data  = [
            'status'     => (string) $status,
            'updated_at' => current_time( 'mysql' ),
        ];
        if ( $resolution_basis !== '' ) {
            $data['resolution_basis'] = $resolution_basis;
        }
        if ( $assigned_user !== null ) {
            $data['assigned_user'] = (int) $assigned_user;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- plugin-owned table; $wpdb escapes insert/update/replace/delete values itself, and there is nothing to cache on a write.
        $wpdb->update( $table, $data, [ 'id' => (int) $case_id ] );
    }

    /** A decision (K1-b) or a recheck (K1-c) is itself an observation — recorded as a case_event with source 'decision'/'recheck'. */
    public function record_decision_event( $case_id, $summary, array $payload = [] ) {
        $now = current_time( 'mysql' );
        $this->add_evidence( $case_id, [
            'event_key'   => GUARDFORGE_Case_Model::evidence_key( array_merge( [ 'kind' => 'decision', 'at' => $now, 'case_id' => (int) $case_id ], $payload ) ),
            'observed_at' => $now,
            'source'      => 'decision',
            'source_ref'  => '',
            'summary'     => (string) $summary,
            'payload'     => $payload,
        ] );
    }

    /**
 * A recheck attempt (K1-c) is its own observation too, with its own
 * 'recheck' source so the evidence timeline reads it apart from an
 * operator's own 'decision'. Unlike record_decision_event, the
 * event_key here carries no timestamp — only case_id + the verdict's own
 * $payload (result + reason) — so calling this with an UNCHANGED verdict
 * (sync running again with nothing new to find) is a no-op via
 * add_evidence's own lookup-before-insert, rather than one new row per
 * page view. A genuinely different verdict (new reason text, or a
 * result flip) still gets its own row, because that changes the hash.
 */
    public function record_recheck_event( $case_id, $summary, array $payload = [] ) {
        $this->add_evidence( $case_id, [
            'event_key'   => GUARDFORGE_Case_Model::evidence_key( array_merge( [ 'kind' => 'recheck', 'case_id' => (int) $case_id ], $payload ) ),
            'observed_at' => current_time( 'mysql' ),
            'source'      => 'recheck',
            'source_ref'  => '',
            'summary'     => (string) $summary,
            'payload'     => $payload,
        ] );
    }

    // ──────────────────── reads for the monthly report (§6.4 K1-f) ────────

    /**
 * The recheck verdicts recorded inside [$from, $to), oldest first.
 *
 * Only `result` leaves this method. A recheck payload also carries the
 * verdict's own `reason` — free text built from a file path, a package
 * label or an advisory range — and GUARDFORGE_Guard_Report is a report
 * that gets handed to a client, so the reason has no reason to travel
 * this far in the first place.
 *
 * @param string $from Site-local mysql datetime, inclusive.
 * @param string $to Site-local mysql datetime, exclusive.
 * @return array<int,array{case_id:int,result:string}>
 */
    public function report_recheck_verdicts( $from, $to ) {
        global $wpdb;
        $table = $this->events_table();
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; both bounds are prepared.
        $rows = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT case_id, payload_json FROM {$table}
              WHERE source = 'recheck' AND observed_at >= %s AND observed_at < %s
              ORDER BY observed_at ASC, id ASC",
            (string) $from,
            (string) $to
        ), ARRAY_A );
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        $out = [];
        foreach ( $rows as $row ) {
            $payload = json_decode( (string) ( $row['payload_json'] ?? '' ), true );
            $out[]   = [
                'case_id' => (int) ( $row['case_id'] ?? 0 ),
                'result'  => is_array( $payload ) ? (string) ( $payload['result'] ?? '' ) : '',
            ];
        }
        return $out;
    }

    /**
 * The decisions recorded inside [$from, $to) that put a case into a
 * mitigating status, oldest first — the moment of the decision, which is
 * what the report's fifth line counts. `updated_at` cannot answer this:
 * 'mitigated' and 'fix_applied' are both reopenable, so a new observation
 * of the same case_key bumps the column (find_or_create_case above) and
 * a June mitigation would walk into every later month's report.
 *
 * Two payload shapes are read, because two writers exist:
 * GUARDFORGE_Case_Actions::transition records `to_status`, while its
 * block-IP / lockdown / patch handlers record only the `remedy` they
 * carried out — REMEDY_STATUS maps those three to the status each of them
 * sets, which also covers every decision row written before this read
 * existed. Only a status from GUARDFORGE_Case_Model::STATUSES leaves this
 * method; a decision payload also carries free text (an IP, a version),
 * and none of it has any business travelling toward a client-facing
 * report.
 *
 * @param string $from Site-local mysql datetime, inclusive.
 * @param string $to Site-local mysql datetime, exclusive.
 * @return array<int,array{case_id:int,to_status:string}>
 */
    public function report_decision_statuses( $from, $to ) {
        global $wpdb;
        $table = $this->events_table();
        // phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix + a literal; both bounds are prepared.
        $rows = (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT case_id, payload_json FROM {$table}
              WHERE source = 'decision' AND observed_at >= %s AND observed_at < %s
              ORDER BY observed_at ASC, id ASC",
            (string) $from,
            (string) $to
        ), ARRAY_A );
        // phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        $out = [];
        foreach ( $rows as $row ) {
            $payload = json_decode( (string) ( $row['payload_json'] ?? '' ), true );
            if ( ! is_array( $payload ) ) {
                continue;
            }
            $status = (string) ( $payload['to_status'] ?? '' );
            if ( $status === '' ) {
                $remedy = (string) ( $payload['remedy'] ?? '' );
                $status = (string) ( self::REMEDY_STATUS[ $remedy ] ?? '' );
            }
            if ( ! in_array( $status, GUARDFORGE_Case_Model::STATUSES, true ) ) {
                continue;
            }
            $out[] = [
                'case_id'   => (int) ( $row['case_id'] ?? 0 ),
                'to_status' => $status,
            ];
        }
        return $out;
    }

    /**
 * Every case the month's report has to consider: opened in the window,
 * changed in the window, or rechecked in the window.
 *
 * The third branch is not redundant. A 'still_affected' recheck records
 * a case_event and deliberately changes no status (see GUARDFORGE_Case_
 * Recheck::apply), so it moves neither first_seen nor updated_at — a
 * case opened in June and rechecked in August would be missing from an
 * August report built on the date columns alone, and the "checked"
 * number would silently undercount exactly the cases still open.
 *
 * @param string $from
 * @param string $to
 * @param int $also_ids Case ids to include regardless of dates.
 * @return array<int,array<string,mixed>> Only the columns the report reads.
 */
    /**
 * How many observations each of these cases carries.
 *
 * One grouped query, not one per case: the Suite results provider needs
 * a count beside every row it hands to the dashboard, and it runs while
 * somebody else's screen is rendering.
 *
 * @param int $case_ids
 * @return array<int,int> case id => number of OBSERVATIONS (see the source filter below)
 */
    public function count_events_for( array $case_ids ) {
        global $wpdb;
        $ids = array_values( array_unique( array_filter( array_map( 'intval', $case_ids ) ) ) );
        if ( ! $ids ) {
            return [];
        }
        $table   = $this->events_table();
        $sources = GUARDFORGE_Case_Model::OBSERVATION_SOURCES;
        // Observations only. `decision` and `recheck` rows are somebody's own
        // clicks on the case; counting them would let two acknowledgements
        // read as two more sightings of the thing itself.
        $sql = "SELECT case_id, COUNT(*) AS n FROM {$table}
                 WHERE case_id IN ( " . implode( ', ', array_fill( 0, count( $ids ), '%d' ) ) . ' )
                   AND source IN ( ' . implode( ', ', array_fill( 0, count( $sources ), '%s' ) ) . ' )
                 GROUP BY case_id';
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix + a literal; every value is prepared.
        $rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, ...array_merge( $ids, $sources ) ), ARRAY_A );

        $out = [];
        foreach ( $rows as $row ) {
            $out[ (int) $row['case_id'] ] = (int) $row['n'];
        }
        return $out;
    }

    /**
 * Which of the three observation sources actually reported since $since.
 *
 * `decision` and `recheck` rows are written by this class itself and say
 * nothing about how much of the world was looked at, so they are not
 * counted: the answer is meant to read "2 of 3 checks reported", and a
 * human's own click must not be able to fill that in.
 *
 * @return string Sorted subset of GUARDFORGE_Case_Model::OBSERVATION_SOURCES.
 */
    public function observation_sources_since( $since ) {
        global $wpdb;
        $table   = $this->events_table();
        $sources = GUARDFORGE_Case_Model::OBSERVATION_SOURCES;
        $slots   = implode( ', ', array_fill( 0, count( $sources ), '%s' ) );
        $sql     = "SELECT DISTINCT source FROM {$table}
                     WHERE observed_at >= %s AND source IN ( {$slots} )";
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix + a literal; every value is prepared.
        $rows = (array) $wpdb->get_col( $wpdb->prepare( $sql, (string) $since, ...$sources ) );

        $out = array_values( array_intersect( $sources, array_map( 'strval', $rows ) ) );
        sort( $out );
        return $out;
    }

    public function report_case_rows( $from, $to, array $also_ids = [] ) {
        global $wpdb;
        $table = $this->cases_table();

        $where  = '( ( first_seen >= %s AND first_seen < %s ) OR ( updated_at >= %s AND updated_at < %s ) )';
        $params = [ (string) $from, (string) $to, (string) $from, (string) $to ];

        $ids = array_values( array_unique( array_filter( array_map( 'intval', $also_ids ) ) ) );
        if ( $ids ) {
            $where   .= ' OR id IN ( ' . implode( ', ', array_fill( 0, count( $ids ), '%d' ) ) . ' )';
            $params   = array_merge( $params, $ids );
        }

        $sql = "SELECT id, type, status, first_seen, updated_at, resolution_basis
                  FROM {$table} WHERE {$where} ORDER BY id ASC";
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix + a literal; every value is prepared.
        return (array) $wpdb->get_results( $wpdb->prepare( $sql, ...$params ), ARRAY_A );
    }
}