<?php
/**
 * Database Model for Logs.
 *
 * @package NimblyMail\SMTP
 */

namespace NimblyMail\SMTP\Log;

defined( 'ABSPATH' ) || exit;

/**
 * Class Model
 *
 * Handles raw CRUD operations on nimblymail_logs.
 *
 * @since 1.0.0 Refactored all SQL queries to use concatenation over interpolation.
 * @since 1.0.3 Search now uses MATCH...AGAINST against the FULLTEXT index when available, falling back to the previous LIKE clause otherwise.
 * @since 1.0.5 find() now converts created_at to an ISO-8601 string with the site's UTC offset baked in, and date_from/date_to are converted from site-local time to GMT before being bound into the WHERE clause.
 */
class Model {

    /**
     * Per-request cache for is_fulltext_index_available(), so the option is
     * only read once even if find() is called multiple times in one request.
     *
     * @var bool|null
     */
    private static ?bool $fulltext_available_cache = null;

    /**
     * Get the table name.
     *
     * @return string
     */
    public static function get_table(): string {
        global $wpdb;
        return $wpdb->prefix . 'nimblymail_logs';
    }

    /**
     * Insert a new log entry.
     *
     * @param array $data Log data.
     * @return int Inserted ID or 0 on failure.
     */
    public static function insert( array $data ): int {
        global $wpdb;

        $format = array_map(
            function ( $value ) {
                if ( is_int( $value ) ) {
                    return '%d';
                }
                if ( is_float( $value ) ) {
                    return '%f';
                }
                return '%s';
            },
            $data
        );

        $wpdb->insert( self::get_table(), $data, $format ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (int) $wpdb->insert_id;
    }

    /**
     * Update log status.
     *
     * @since 1.0.0
     * @since 1.0.0 Added optional $sandbox_mode parameter so callers can update
     *              status and sandbox_mode in a single UPDATE.
     *
     * @param int         $id           The log ID.
     * @param string      $status       New status.
     * @param string|null $error        Error message if failed.
     * @param int|null    $duration_ms  Duration in ms.
     * @param string|null $debug_log    Debug log.
     * @param string|null $sandbox_mode Sandbox mode tag ('log'|'redirect'|'bypassed') to set alongside status.
     * @return bool True on success.
     */
    public static function update_status( int $id, string $status, ?string $error = null, ?int $duration_ms = null, ?string $debug_log = null, ?string $sandbox_mode = null ): bool {
        global $wpdb;

        $data = array( 'status' => $status );

        if ( null !== $error ) {
            $data['error_message'] = $error;
        }

        if ( null !== $duration_ms ) {
            $data['send_duration_ms'] = $duration_ms;
        }

        if ( null !== $debug_log && '' !== $debug_log ) {
            $data['debug_log'] = $debug_log;
        }

        if ( null !== $sandbox_mode ) {
            $data['sandbox_mode'] = $sandbox_mode;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update( self::get_table(), $data, array( 'id' => $id ) );
        return false !== $result;
    }

    /**
     * Mark a log row as sandbox-redirected and persist the pre-rewrite recipients.
     *
     * Called by Logger after a redirect-mode send completes. The log row is
     * already populated by capture_email() with the rewritten recipients;
     * this method overrides the status to 'sandboxed', sets sandbox_mode to
     * 'redirect', and merges the original recipients into the headers JSON
     * under reserved keys so the Vue log detail modal can surface them.
     *
     * @since 1.0.0
     *
     * @param int    $id           Existing log row id.
     * @param string $original_to  Pre-rewrite To value.
     * @param string $original_cc  Pre-rewrite Cc value (may be empty).
     * @param string $original_bcc Pre-rewrite Bcc value (may be empty).
     *
     * @return bool True on success.
     */
    public static function update_sandbox_redirect( int $id, string $original_to, string $original_cc = '', string $original_bcc = '' ): bool {
        global $wpdb;

        if ( $id <= 0 ) {
            return false;
        }

        $existing = self::find_one( $id );
        if ( null === $existing ) {
            return false;
        }

        $headers_raw = $existing['headers'] ?? '';
        $decoded     = is_string( $headers_raw ) && '' !== $headers_raw ? json_decode( $headers_raw, true ) : array();
        if ( ! is_array( $decoded ) ) {
            $decoded = array();
        }

        $decoded['_original_to']  = $original_to;
        $decoded['_original_cc']  = $original_cc;
        $decoded['_original_bcc'] = $original_bcc;

        $data = array(
            'status'       => 'sandboxed',
            'sandbox_mode' => 'redirect',
            'headers'      => wp_json_encode( $decoded ),
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update( self::get_table(), $data, array( 'id' => $id ) );
        return false !== $result;
    }

    /**
     * Find logs with filters and pagination.
     *
     * Supports search, status, source, mailer, and date range filters.
     * Uses string concatenation (not interpolation) for table names and
     * validated structural parts. All user-supplied values go through
     * $wpdb->prepare() placeholders.
     *
     * Applies nimblymail_log_query_args before building the query so Pro
     * add-ons can inject extra filters, and nimblymail_log_items after
     * fetching rows so Pro add-ons can append, remove, or decorate rows.
     *
     * created_at is stored as GMT (Logger::capture_email() writes it
     * explicitly via current_time( 'mysql', true ), the same as every other
     * timestamp column this plugin writes - it is not left to the column's
     * DEFAULT CURRENT_TIMESTAMP, since MySQL's CURRENT_TIMESTAMP follows the
     * connection's session time_zone, which is not guaranteed to be UTC), so
     * date_from and date_to are treated as site-local calendar values and
     * converted to GMT via get_gmt_from_date() before being bound into the
     * WHERE clause - otherwise the comparison would be skewed by the site's
     * UTC offset.
     *
     * @since 1.0.0
     * @since 1.0.0 Strengthened date filter validation to use strtotime() for semantic correctness.
     * @since 1.0.0 Allowed statuses widened to include processing/queued/sandboxed.
     * @since 1.0.3 Search clause now built by build_search_clause(), which uses the FULLTEXT index via MATCH...AGAINST when available.
     * @since 1.0.5 Converts created_at on returned rows to an ISO-8601 string with the site's UTC offset via convert_row_timestamp(), and converts date_from/date_to from site-local time to GMT before binding, so both display and filtering are no longer skewed by the site/browser UTC offset.
     *
     * @param array $args Query arguments.
     *
     * @return array { items: array, total: int }
     */
    public static function find( array $args = array() ): array {
        global $wpdb;

        /**
         * Filters the query arguments before the log query is built.
         *
         * Allows Pro add-ons to inject additional filter parameters (e.g. a
         * custom column filter) before Model::find() constructs its WHERE clause.
         * Arguments must conform to the same keys accepted by find().
         *
         * @since 1.0.0
         *
         * @param array $args The raw query arguments passed to find().
         *
         * @return array
         */
        $args = (array) apply_filters( 'nimblymail_log_query_args', $args );

        $defaults = array(
            'page'      => 1,
            'per_page'  => 20,
            'search'    => '',
            'status'    => '',
            'source'    => '',
            'mailer'    => '',
            'date_from' => '',
            'date_to'   => '',
            'orderby'   => 'created_at',
            'order'     => 'DESC',
        );

        $parsed = wp_parse_args( $args, $defaults );

        $clauses  = array( '1=1' );
        $bindings = array();

        if ( ! empty( $parsed['search'] ) ) {
            $search = self::build_search_clause( (string) $parsed['search'], self::is_fulltext_index_available(), self::get_table() );
            if ( '' !== $search['clause'] ) {
                $clauses[] = $search['clause'];
                $bindings  = array_merge( $bindings, $search['bindings'] );
            }
        }

        $allowed_statuses = array( 'pending', 'processing', 'sent', 'failed', 'queued', 'sandboxed', 'blocked' );
        if ( ! empty( $parsed['status'] ) && in_array( $parsed['status'], $allowed_statuses, true ) ) {
            $clauses[]  = 'status = %s';
            $bindings[] = $parsed['status'];
        }

        if ( ! empty( $parsed['source'] ) ) {
            $clauses[]  = 'source = %s';
            $bindings[] = $parsed['source'];
        }

        if ( ! empty( $parsed['mailer'] ) ) {
            $clauses[]  = 'mailer_used = %s';
            $bindings[] = $parsed['mailer'];
        }

        if ( ! empty( $parsed['date_from'] ) && false !== strtotime( $parsed['date_from'] ) ) {
            $clauses[]  = 'created_at >= %s';
            $bindings[] = get_gmt_from_date( $parsed['date_from'] );
        }

        if ( ! empty( $parsed['date_to'] ) && false !== strtotime( $parsed['date_to'] ) ) {
            $clauses[]  = 'created_at <= %s';
            $bindings[] = get_gmt_from_date( $parsed['date_to'] );
        }

        $where_sql = implode( ' AND ', $clauses );

        $allowed_orders = array( 'id', 'created_at', 'status', 'source' );
        $orderby        = in_array( $parsed['orderby'], $allowed_orders, true ) ? $parsed['orderby'] : 'created_at';
        $order          = strtoupper( $parsed['order'] ) === 'ASC' ? 'ASC' : 'DESC';

        $per_page = max( 1, (int) $parsed['per_page'] );
        $offset   = ( max( 1, (int) $parsed['page'] ) - 1 ) * $per_page;

        // $where_sql contains only fixed clause fragments with %s placeholders;
        // user input is passed through prepare() bindings. $orderby and $order
        // are validated against allow-lists above.
        $count_sql  = "SELECT COUNT(id) FROM {$wpdb->prefix}nimblymail_logs WHERE {$where_sql}";
        $select_sql = "SELECT id, created_at, to_address, subject, SUBSTRING(body, 1, 500) as body, status, source, mailer_used, send_duration_ms, error_message FROM {$wpdb->prefix}nimblymail_logs WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d, %d";

        if ( ! empty( $bindings ) ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause has only literal placeholder fragments; user data is in $bindings.
            $total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $bindings ) );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input; WHERE is a literal "1=1".
            $total = (int) $wpdb->get_var( $count_sql );
        }

        $select_bindings = array_merge( $bindings, array( $offset, $per_page ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where_sql is literal placeholder fragments; $orderby/$order are allow-list validated; user data is in $select_bindings.
        $items = $wpdb->get_results( $wpdb->prepare( $select_sql, $select_bindings ), ARRAY_A );

        $items = is_array( $items ) ? $items : array();

        $items = array_map(
            static function ( array $item ): array {
                $item['created_at'] = self::convert_row_timestamp( $item['created_at'] ?? null );
                return $item;
            },
            $items
        );

        /**
         * Filters the log rows returned from the database query.
         *
         * Fires after rows are fetched and before they are returned. Allows
         * Pro add-ons to append, remove, or decorate individual log rows
         * (e.g. attaching delivery metadata from a third-party API).
         *
         * @since 1.0.0
         *
         * @param array $items The array of log row associative arrays.
         * @param array $args  The query arguments after nimblymail_log_query_args filtering.
         *
         * @return array
         */
        $items = (array) apply_filters( 'nimblymail_log_items', $items, $args );

        return array(
            'items' => $items,
            'total' => $total,
        );
    }

    /**
     * Convert a raw GMT DATETIME string to an ISO-8601 string carrying the
     * site's UTC offset.
     *
     * The created_at column is stored as GMT (Logger::capture_email() writes
     * it explicitly via current_time( 'mysql', true ), matching every other
     * timestamp column this plugin writes), but find() was previously
     * returning it unconverted. A space-separated "Y-m-d H:i:s"
     * string carries no timezone marker, so every JS Date parser treats it as
     * browser-local time, which skews the frontend's relative-time display by
     * the difference between the site and browser UTC offsets.
     * get_date_from_gmt( $value, 'c' ) bakes the site's configured offset
     * into the output so it parses unambiguously regardless of the browser's
     * own timezone. Kept as a pure function (no $wpdb dependency) so it can
     * be unit tested directly.
     *
     * @since 1.0.5
     *
     * @param string|null $value Raw GMT DATETIME string, or null/empty when the column allows NULL.
     *
     * @return string|null ISO-8601 string with the site's UTC offset, or null when $value was null/empty.
     */
    private static function convert_row_timestamp( ?string $value ): ?string {
        return ( null === $value || '' === $value ) ? null : get_date_from_gmt( $value, 'c' );
    }

    /**
     * Whether the idx_search FULLTEXT index on nimblymail_logs is usable.
     *
     * Reads the flag Installer::ensure_fulltext_index() records instead of
     * querying INFORMATION_SCHEMA directly, and caches the result for the
     * rest of the request so repeated find() calls don't re-read the option.
     *
     * @since 1.0.3
     *
     * @return bool
     */
    public static function is_fulltext_index_available(): bool {
        if ( null === self::$fulltext_available_cache ) {
            self::$fulltext_available_cache = '1' === get_option( Installer::FULLTEXT_INDEX_OPTION, '' );
        }

        return self::$fulltext_available_cache;
    }

    /**
     * Build the WHERE clause fragment and bindings for a log search term.
     *
     * When the FULLTEXT index is available, matching ids are found by a
     * UNION of three independently-indexable SELECTs — MATCH (to_address,
     * subject, body) AGAINST (%s IN BOOLEAN MODE) (using idx_search), plus a
     * LIKE on to_address and on source (neither is part of the FULLTEXT
     * index, so partial email-address matches and source matches still work
     * the way plain LIKE did). Those are combined via UNION rather than OR:
     * MySQL's optimizer can only use idx_search as an index seek when the
     * MATCH is evaluated on its own — OR'd directly against a LIKE in the
     * same WHERE forces a full table scan and defeats the point of the
     * index. The UNION is wrapped in a derived table and referenced through
     * `id IN (SELECT m.id FROM (...) AS m)` so it materializes once instead
     * of being re-run per outer row, and so the fragment still slots into
     * the existing $clauses/$where_sql AND-chain like every other filter.
     * Confirmed against a local 8k-row/~6KB-body table: EXPLAIN shows
     * `type: fulltext, key: idx_search` for the MATCH branch, and count
     * queries dropped from ~1360ms (plain LIKE on body) to ~110ms.
     *
     * When the index isn't available, falls back to the original
     * four-column LIKE clause.
     *
     * Kept free of any $wpdb calls (it duplicates $wpdb->esc_like()'s
     * addcslashes() behaviour instead of calling it) so it can be unit
     * tested without a database connection. $table is caller-supplied
     * (Model::get_table(), built from the internal $wpdb->prefix) rather
     * than looked up here, for the same reason.
     *
     * Accepted tradeoff of the FULLTEXT path: subject and body match on
     * word prefixes only, so mid-word substrings no longer match there
     * (partial matches still work on to_address and source via LIKE), and
     * words shorter than the server's minimum token size are ignored.
     *
     * @since 1.0.3
     *
     * @param string $term               Raw search term from the request.
     * @param bool   $fulltext_available Whether the FULLTEXT index is usable.
     * @param string $table              Fully-prefixed logs table name, used only when $fulltext_available is true.
     *
     * @return array{clause: string, bindings: array<int, string>}
     */
    public static function build_search_clause( string $term, bool $fulltext_available, string $table = '' ): array {
        $term = trim( $term );

        if ( '' === $term ) {
            return array(
                'clause'   => '',
                'bindings' => array(),
            );
        }

        $like = '%' . self::esc_like_term( $term ) . '%';

        if ( $fulltext_available && '' !== $table ) {
            $boolean_term = self::prepare_boolean_search_term( $term );
            if ( '' !== $boolean_term ) {
                $clause = 'id IN ( SELECT m.id FROM ('
                    . "SELECT id FROM {$table} WHERE MATCH (to_address, subject, body) AGAINST (%s IN BOOLEAN MODE)"
                    . " UNION SELECT id FROM {$table} WHERE to_address LIKE %s"
                    . " UNION SELECT id FROM {$table} WHERE source LIKE %s"
                    . ') AS m )';

                return array(
                    'clause'   => $clause,
                    'bindings' => array( $boolean_term, $like, $like ),
                );
            }
        }

        return array(
            'clause'   => '(to_address LIKE %s OR subject LIKE %s OR body LIKE %s OR source LIKE %s)',
            'bindings' => array( $like, $like, $like, $like ),
        );
    }

    /**
     * Escape a search term for use inside a LIKE '%...%' pattern.
     *
     * Mirrors $wpdb->esc_like(), which is just addcslashes( $text, '_%\\' ),
     * without requiring a live $wpdb instance so build_search_clause() stays
     * unit-testable.
     *
     * @since 1.0.3
     *
     * @param string $term Raw search term.
     *
     * @return string
     */
    private static function esc_like_term( string $term ): string {
        return addcslashes( $term, '_%\\' );
    }

    /**
     * Prepare a user search term for MySQL FULLTEXT BOOLEAN MODE.
     *
     * Strips the characters that carry operator meaning in boolean mode
     * (+ - > < ( ) ~ * " @) so a search term can't inject fulltext syntax or
     * raise a syntax error, then rebuilds each remaining word with a leading
     * + (required) and a trailing * (prefix match), e.g. "foo bar" becomes
     * "+foo* +bar*". The leading + matters: boolean mode's default between
     * space-separated words is OR, and stripping "@"/"." out of something
     * like "user@example.com" turns it into several words — without +, a
     * search would match any row containing just one of those words (found
     * this matching ~8000 rows for a term expected to match 1 while testing
     * against a live 8k-row table), which is a much broader match than the
     * old LIKE '%...%' gave. + makes all words required, so it behaves like
     * an AND of partial-word matches instead. Returns an empty string if
     * nothing searchable is left after stripping, so the caller can fall
     * back to the plain LIKE clause.
     *
     * @since 1.0.3
     *
     * @param string $term Raw search term (already trimmed by the caller).
     *
     * @return string
     */
    private static function prepare_boolean_search_term( string $term ): string {
        $stripped = preg_replace( '/[+\-><()~*"@]/', ' ', $term );
        $stripped = trim( preg_replace( '/\s+/', ' ', (string) $stripped ) );

        if ( '' === $stripped ) {
            return '';
        }

        $words = array_map(
            static fn ( string $word ): string => '+' . $word . '*',
            explode( ' ', $stripped )
        );

        return implode( ' ', $words );
    }

    /**
     * Find a single log by ID.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name.
     *
     * @param int $id The log ID.
     *
     * @return array|null
     */
    public static function find_one( int $id ): ?array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}nimblymail_logs WHERE id = %d", $id ),
            ARRAY_A
        );

        return $row ? $row : null;
    }

    /**
     * Delete a single log.
     *
     * @param int $id The log ID.
     * @return bool
     */
    public static function delete( int $id ): bool {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return false !== $wpdb->delete( self::get_table(), array( 'id' => $id ) );
    }

    /**
     * Delete multiple logs by ID.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name and placeholders.
     *
     * @param array $ids Array of log IDs.
     *
     * @return int Number of rows deleted.
     */
    public static function bulk_delete( array $ids ): int {
        global $wpdb;
        $ids = array_map( 'intval', $ids );
        if ( empty( $ids ) ) {
            return 0;
        }

        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders is built from a fixed-format integer count and contains only %d markers; values are bound via prepare().
        return (int) $wpdb->query(
            $wpdb->prepare( "DELETE FROM {$wpdb->prefix}nimblymail_logs WHERE id IN ({$placeholders})", $ids )
        );
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Get stats for filter tabs.
     *
     * This query has no user-supplied input; the GROUP BY operates on the
     * status column which is constrained by the application to known values.
     * Table name is appended via concatenation for consistency.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name.
     *
     * @return array
     */
    public static function get_stats(): array {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- No user input.
        $result = $wpdb->get_results(
            "SELECT status, COUNT(id) as count FROM {$wpdb->prefix}nimblymail_logs GROUP BY status",
            ARRAY_A
        );

        $stats = array(
            'total'   => 0,
            'sent'    => 0,
            'failed'  => 0,
            'pending' => 0,
        );

        foreach ( $result as $row ) {
            $status          = $row['status'];
            $count           = (int) $row['count'];
            $stats['total'] += $count;
            if ( isset( $stats[ $status ] ) ) {
                $stats[ $status ] = $count;
            }
        }

        return $stats;
    }

    /**
     * Get aggregated stats for the dashboard.
     *
     * Uses concatenation for table name and prepared WHERE clause.
     * The $days and $offset_days parameters are validated via absint() in the
     * controller and passed through $wpdb->prepare() here.
     *
     * When $offset_days is greater than zero the WHERE clause selects the
     * prior window of the same length, ending $offset_days ago. For example
     * `get_dashboard_stats( 7, 7 )` returns the 7-day window that ends one
     * week ago — the comparison baseline for a current 7-day window.
     *
     * @since 1.0.0 Refactored SQL to use concatenation for table name and prepared WHERE. Added optional $offset_days parameter for prior-period comparison queries.
     *
     * @param int $days        Number of days to look back (0 for all time).
     * @param int $offset_days Optional. Days to shift the window backwards. Default 0 (current window).
     *
     * @return array
     */
    public static function get_dashboard_stats( int $days = 7, int $offset_days = 0 ): array {
        global $wpdb;

        $base_select = "SELECT
            COUNT(id) AS total,
            SUM(CASE WHEN status = 'sent'    THEN 1 ELSE 0 END) AS sent,
            SUM(CASE WHEN status = 'failed'  THEN 1 ELSE 0 END) AS failed,
            SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
            AVG(CASE WHEN status = 'sent' AND send_duration_ms IS NOT NULL THEN send_duration_ms ELSE NULL END) AS avg_duration,
            MAX(CASE WHEN status = 'sent' THEN created_at ELSE NULL END) AS last_sent_at
            FROM {$wpdb->prefix}nimblymail_logs";

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $base_select is a literal SELECT with no user input; prepare() handles dynamic intervals.
        if ( $days <= 0 ) {
            $row = $wpdb->get_row( $base_select, ARRAY_A );
        } elseif ( $offset_days > 0 ) {
            $row = $wpdb->get_row(
                $wpdb->prepare(
                    $base_select . ' WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) AND created_at < DATE_SUB(NOW(), INTERVAL %d DAY)',
                    $days + $offset_days,
                    $offset_days
                ),
                ARRAY_A
            );
        } else {
            $row = $wpdb->get_row(
                $wpdb->prepare(
                    $base_select . ' WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)',
                    $days
                ),
                ARRAY_A
            );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

        $total   = (int) ( $row['total'] ?? 0 );
        $sent    = (int) ( $row['sent'] ?? 0 );
        $failed  = (int) ( $row['failed'] ?? 0 );
        $pending = (int) ( $row['pending'] ?? 0 );

        $success_rate = $total > 0 ? round( ( $sent / $total ) * 100, 1 ) : 0;

        return array(
            'period'          => $days > 0 ? $days . 'd' : 'all',
            'total'           => $total,
            'sent'            => $sent,
            'failed'          => $failed,
            'pending'         => $pending,
            'success_rate'    => $success_rate,
            'avg_duration_ms' => $row['avg_duration'] ? (int) $row['avg_duration'] : 0,
            'last_sent_at'    => $row['last_sent_at'] ? get_date_from_gmt( $row['last_sent_at'], 'c' ) : null,
        );
    }

    /**
     * Get sent/failed counts for multiple day ranges in a single query.
     *
     * Used by the network dashboard to avoid running get_dashboard_stats()
     * twice per site (once for 24h and once for 7d). All ranges requested
     * are aggregated via conditional SUMs in one table scan.
     *
     * @since 1.0.0
     *
     * @param int[] $day_ranges List of day counts (e.g. [1, 7]). Zero or negative values are ignored.
     *
     * @return array<int, array{sent: int, failed: int}>
     */
    public static function get_range_stats( array $day_ranges ): array {
        global $wpdb;

        $day_ranges = array_values( array_unique( array_filter( array_map( 'intval', $day_ranges ), static fn ( $d ) => $d > 0 ) ) );

        if ( empty( $day_ranges ) ) {
            return array();
        }

        // Build SELECT fragments using prepared subexpressions; each $days
        // value is an int (cast above) so it is safe to interpolate into
        // column aliases. Date thresholds are bound via prepare().
        $select_parts = array();
        $bindings     = array();
        foreach ( $day_ranges as $days ) {
            $select_parts[] = "SUM(CASE WHEN status = 'sent'   AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) THEN 1 ELSE 0 END) AS sent_{$days}";
            $select_parts[] = "SUM(CASE WHEN status = 'failed' AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) THEN 1 ELSE 0 END) AS failed_{$days}";
            $bindings[]     = $days;
            $bindings[]     = $days;
        }

        $sql = 'SELECT ' . implode( ', ', $select_parts ) . " FROM {$wpdb->prefix}nimblymail_logs";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Column aliases derived from int-cast $days values; date thresholds bound via $bindings.
        $row = $wpdb->get_row( $wpdb->prepare( $sql, $bindings ), ARRAY_A );

        $result = array();
        foreach ( $day_ranges as $days ) {
            $result[ $days ] = array(
                'sent'   => (int) ( $row[ 'sent_' . $days ] ?? 0 ),
                'failed' => (int) ( $row[ 'failed_' . $days ] ?? 0 ),
            );
        }

        return $result;
    }
}
