<?php
/**
 * Base Table Operations Abstract Class
 *
 * Provides common CRUD operations, caching, aggregation, and visualization methods
 * for all database table classes. Child classes only need to define table-specific logic.
 * Implements comprehensive data validation, sanitization, and caching strategies.
 *
 * @package    UltraguardPro\db
 * @author     Ultraguard Pro Team
 * @copyright  Ultraguard Pro
 * @license    GPL-2.0+
 * @version    1.2.0
 * @since      1.2.0
 * @link       https://ultraguardpro.com
 */

namespace UltraguardPro\db;

use UltraguardPro\core\DatabaseCrud;
use UltraguardPro\core\DatabaseSchema;
use UltraguardPro\core\CacheManager;
use UltraguardPro\core\DataCodec;

// Prevent direct access
defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Abstract table layer intentionally performs controlled $wpdb reads/writes for all child table classes.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query fragments in this base table layer are derived from validated schema metadata and sanitized method inputs.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Abstract table helpers intentionally assemble validated identifier fragments before preparing value placeholders.

/**
 * Abstract base class for database table operations
 *
 * This class provides a comprehensive framework for database table operations including
 * CRUD, caching, aggregation, chart data generation, and data validation. Child classes
 * inherit all functionality while only needing to implement table-specific validation
 * and sanitization logic.
 *
 * Features include:
 * - Complete CRUD operations with automatic caching
 * - Advanced caching with versioning and invalidation
 * - Chart data generation for visualization
 * - Data validation and sanitization
 * - Bulk operations with size limits
 * - Pagination and search capabilities
 * - Transaction support
 * - Error logging and handling
 *
 * @package    UltraguardPro\db
 * @author     Ultraguard Pro Team
 * @version    1.2.0
 * @since      1.2.0
 * @see        DatabaseCrud
 * @see        DatabaseSchema
 *
 * @example
 * // Child class implementation
 * class UserTable extends BaseTable {
 *     protected $table_name = 'users';
 *     protected $cache_group = 'ultraguard_users';
 *
 *     public function get_defaults() {
 *         return ['username' => '', 'email' => '', 'status' => 'active'];
 *     }
 *
 *     protected function validate_data(array $data) {
 *         // Validation logic
 *         return $data;
 *     }
 *
 *     protected function sanitize_data(array $data) {
 *         // Sanitization logic
 *         return $data;
 *     }
 * }
 *
 * // Usage
 * $users = new UserTable();
 * $user = $users->get(1);
 * $recent_users = $users->get_recent(10);
 */
abstract class BaseTable
{
    /**
     * Table name (without prefix) - MUST be defined by child class
     *
     * This property must be set in the child class constructor or property definition.
     * Should contain only lowercase letters, numbers, and underscores.
     *
     * @var string
     * @access protected
     */
    protected $table_name = '';

    /**
     * Cache group for this table - MUST be defined by child class
     *
     * Used to group cache entries for this specific table. Automatically generated
     * if not provided: 'ultraguard_' . $this->table_name
     *
     * @var string
     * @access protected
     */
    protected $cache_group = '';

    /**
     * Default cache duration (in seconds)
     *
     * Default: 6 hours (21600 seconds). Used for general record caching.
     *
     * @var int
     * @access protected
     * @default 21600
     */
    protected $cache_duration = 0; // 0 = resolved at runtime from CacheManager (db_query_ttl)

    /**
     * Short cache duration for frequently changing data
     *
     * Default: 1 hour (3600 seconds). Used for lists, pagination, and search results.
     *
     * @var int
     * @access protected
     * @default 3600
     */
    protected $short_cache_duration = 0; // 0 = resolved at runtime from CacheManager (short_ttl)

    /**
     * Very short cache duration for rapidly changing data
     *
     * Default: 15 minutes (900 seconds). Used for real-time data and sparklines.
     *
     * @var int
     * @access protected
     * @default 900
     */
    protected $very_short_cache_duration = 0; // 0 = resolved at runtime from CacheManager (short_ttl)

    /**
     * Default method parameters for consistent behavior
     *
     * Provides default values for common method parameters across the class.
     *
     * @var array
     * @access protected
     */
    protected $method_defaults = [
        'limit' => 100,
        'orderby' => 'id',
        'order' => 'DESC',
        'output_type' => ARRAY_A,
        'page' => 1,
        'per_page' => 20,
        'use_cache' => true
    ];

    /**
     * Cache version for invalidation
     *
     * Used to invalidate all cached data when clearing cache. Stored as transient.
     *
     * @var string
     * @access private
     */
    private $cache_version = '1.0';

    /**
     * Table columns cache
     *
     * Static cache for table column information to avoid repeated database queries.
     *
     * @var array|null
     * @access private
     * @static
     */
    private static $table_columns_cache = [];

    /**
     * Maximum bulk operation size to prevent memory issues
     *
     * Limits the number of records processed in bulk operations like bulk_insert.
     *
     * @var int
     * @access protected
     * @default 1000
     */
    protected $max_bulk_size = 1000;

    /**
     * Constructor - validates required properties
     *
     * Ensures that child classes define required properties and validates table name format.
     *
     * @throws \Exception If required properties are not defined or table name is invalid
     * @access public
     * @since  1.2.0
     */
    public function __construct()
    {
        if (empty($this->table_name) || !is_string($this->table_name)) {
            throw new \Exception(
                esc_html__('Table name must be defined as a non-empty string in child class', 'ultraguard-security-suite')
            );
        }

        // Validate table name format
        if (!preg_match('/^[a-z_][a-z0-9_]*$/', $this->table_name)) {
            throw new \Exception(
                esc_html__('Table name must contain only lowercase letters, numbers, and underscores', 'ultraguard-security-suite')
            );
        }

        if (empty($this->cache_group)) {
            $this->cache_group = 'ultraguard_' . $this->table_name;
        }

        // Resolve TTLs from CacheManager so all cache durations are controlled
        // from the single CacheManager config rather than per-table constants.
        if ($this->cache_duration === 0) {
            $cfg = CacheManager::get_instance()->get_config();
            $this->cache_duration       = $cfg['db_query_ttl'] ?? 300;
            $this->short_cache_duration = $cfg['short_ttl']    ?? 120;
            $this->very_short_cache_duration = $cfg['short_ttl'] ?? 120;
        }
//        $this->clear_all_caches();
        // Initialize cache version
        $this->init_cache_version();
    }

    // ============================================================
    // PUBLIC ACCESSORS
    // ============================================================

    /**
     * Return the raw (un-prefixed, without $wpdb->prefix) table name stored in
     * $this->table_name — e.g. 'ultraguard_firewall_rules'.
     *
     * Used by DatabaseSchema::maybe_load_registry() to build the name-cache
     * without triggering the full module boot cycle.
     *
     * @return string
     */
    public function get_table_name_raw(): string {
        return $this->table_name;
    }

    // ============================================================
    // SHARED SANITIZERS
    // ============================================================

    /**
     * Sanitize a regex / raw-pattern string for safe DB storage.
     *
     * Unlike sanitize_text_field() / sanitize_textarea_field(), this method
     * does NOT call wp_strip_all_tags() or wp_pre_kses_less_than(), which
     * would destroy patterns containing HTML-like tokens such as:
     *   /<script[\s>]/i  →  /]/i   (broken beyond use)
     *
     * It only:
     *   1. Strips null bytes  (prevents null-byte injection in SQL).
     *   2. Validates UTF-8    (returns '' on invalid byte sequences).
     *   3. Trims whitespace.
     *
     * SQL injection safety is provided by $wpdb->prepare()'s %s placeholder,
     * which calls mysqli_real_escape_string() and correctly doubles backslashes
     * so the pattern round-trips through the DB unchanged.
     *
     * Child classes (FirewallRulesTable, AttackPatternsTable,
     * MalwareSignaturesTable) delegate to this single implementation rather
     * than maintaining their own identical copies.
     *
     * @param  string $pattern
     * @return string
     */
    public static function sanitize_regex_pattern( string $pattern ): string {
        $pattern = str_replace( "\x00", '', $pattern );
        if ( function_exists( 'mb_check_encoding' ) && ! mb_check_encoding( $pattern, 'UTF-8' ) ) {
            return '';
        }
        return trim( $pattern );
    }


    // ============================================================
    // CODEC — Safe DB round-trip encoding for sensitive fields
    // ============================================================

    /**
     * Return the list of column names that must be Base64-encoded before
     * being passed to DatabaseCrud (to survive WP sanitization), and decoded
     * transparently after every read.
     *
     * Child classes MUST override this method when they store regex patterns,
     * raw JSON, or any content containing HTML-like tokens (e.g. /<script/i).
     *
     * @return string[]  Column names that require codec treatment.
     */
    protected function get_codec_fields(): array {
        return [];
    }

    /**
     * Subset of get_codec_fields() whose stored payloads are JSON arrays/objects.
     * On decode, these fields are automatically json_decode()d back to PHP arrays.
     *
     * @return string[]  Column names decoded to PHP arrays on read.
     */
    protected function get_json_codec_fields(): array {
        return [];
    }

    /**
     * Encode codec fields before passing data to DatabaseCrud.
     * Idempotent — already-encoded values are NOT double-encoded.
     * NULL values are preserved as SQL NULL.
     *
     * @param  array $data  Column => value pairs for INSERT or UPDATE.
     * @return array        Same array with sensitive columns Base64-wrapped.
     */
    protected function codec_encode( array $data ): array {
        $fields = $this->get_codec_fields();
        if ( empty( $fields ) ) {
            return $data;
        }
        return DataCodec::encode_fields( $data, $fields );
    }

    /**
     * Decode codec fields in a single row returned from the database.
     * Plain (legacy) values without the UGC1: prefix pass through unchanged.
     *
     * @param  array $row  Associative row array from DatabaseCrud.
     * @return array       Row with sensitive columns decoded.
     */
    protected function codec_decode( array $row ): array {
        $fields = $this->get_codec_fields();
        if ( empty( $fields ) ) {
            return $row;
        }
        return DataCodec::decode_fields( $row, $fields, $this->get_json_codec_fields() );
    }

    /**
     * Decode codec fields across an array of rows (result sets).
     *
     * @param  array $rows  Array of associative row arrays.
     * @return array        Rows with codec fields decoded.
     */
    protected function codec_decode_rows( array $rows ): array {
        $fields = $this->get_codec_fields();
        if ( empty( $fields ) || empty( $rows ) ) {
            return $rows;
        }
        return DataCodec::decode_rows( $rows, $fields, $this->get_json_codec_fields() );
    }

    // ============================================================
    // ABSTRACT METHODS - Must be implemented by child classes
    // ============================================================

    /**
     * Get default values for records
     *
     * Prevents null/undefined index errors by providing default values for all fields.
     * Should return an associative array with all table fields and their default values.
     *
     * @return array Default field values
     * @access public
     * @abstract
     * @since  1.2.0
     *
     * @example
     * return [
     *     'id' => 0,
     *     'name' => '',
     *     'email' => '',
     *     'status' => 'active',
     *     'created_at' => '',
     *     'updated_at' => ''
     * ];
     */
    abstract public function get_defaults();

    /**
     * Validate record data before insert/update
     *
     * Performs data validation specific to the table's requirements.
     * Should return false if validation fails, or validated data array on success.
     *
     * @param array $data Record data to validate
     * @return array|false Validated data or false on failure
     * @access protected
     * @abstract
     * @since  1.2.0
     *
     * @example
     * if (empty($data['email']) || !is_email($data['email'])) {
     *     return false;
     * }
     * return $data;
     */
    abstract protected function validate_data($data);

    /**
     * Sanitize record data
     *
     * Cleans and prepares data for database insertion/update.
     * Should handle escaping, trimming, and type casting as needed.
     *
     * @param array $data Record data to sanitize
     * @return array Sanitized data
     * @access protected
     * @abstract
     * @since  1.2.0
     *
     * @example
     * $sanitized = [];
     * foreach ($data as $key => $value) {
     *     $sanitized[$key] = sanitize_text_field($value);
     * }
     * return $sanitized;
     */
    abstract protected function sanitize_data($data);

    // ============================================================
    // CACHE MANAGEMENT
    // ============================================================

    /**
     * Initialize cache version
     *
     * Sets up cache versioning for invalidation purposes. Creates or retrieves
     * a cache version stored as a transient.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function init_cache_version()
    {
        $version_key = $this->cache_group . '_cache_version';
        $stored_version = CacheManager::get_instance()->get( $version_key, 'db_query' );

        if (false === $stored_version) {
            $this->cache_version = (string) time();
            CacheManager::get_instance()->set( $version_key, $this->cache_version, 'db_query' );
        } else {
            $this->cache_version = (string) $stored_version;
        }
    }

    /**
     * Generate cache key with version
     *
     * Creates a unique cache key incorporating blog ID, cache version, type, and identifier.
     * Ensures multisite compatibility and proper cache invalidation.
     *
     * @param string $type       Cache type (single, list, count, stats, etc.)
     * @param mixed  $identifier Unique identifier for this cache entry
     * @return string Generated cache key
     * @access protected
     * @since  1.2.0
     *
     * @example
     * // Returns: ultraguard_users_0_v1640995200_single_md5hash
     * $this->get_cache_key('single', 123);
     */
    protected function get_cache_key(string $type, $identifier)
    {
        $blog_id = is_multisite() ? (string) get_current_blog_id() : '0';
        $identifier_hash = $this->generate_identifier_hash($identifier);

        return sprintf(
            '%s_%s_v%s_%s_%s',
            sanitize_key($this->cache_group),
            $blog_id,
            sanitize_key($this->cache_version),
            sanitize_key($type),
            $identifier_hash
        );
    }

    /**
     * Generate hash for identifier
     *
     * Creates an MD5 hash from the identifier for use in cache keys.
     * Handles both scalar values and arrays.
     *
     * @param mixed $identifier Identifier to hash
     * @return string Hashed identifier
     * @access private
     * @since  1.2.0
     */
    private function generate_identifier_hash($identifier)
    {
        if (is_scalar($identifier)) {
            return md5((string) $identifier);
        }

        return md5(wp_json_encode($identifier));
    }

    protected function get_cached_data(string $key, callable $callback, ?int $duration = null, bool $use_cache = false)
    {
        // Security: Validate cache key
        if (empty($key) || !is_string($key)) {
            return call_user_func($callback);
        }

        if (!$use_cache) {
            return call_user_func($callback);
        }

        $duration = $duration ?? $this->cache_duration;
        $cache    = CacheManager::get_instance();

        // Check if we have a chunked cache index
        $chunk_index = $cache->get( $key . '_chunk_index', 'db_query' );
        if ($chunk_index !== false) {
            return $this->retrieve_chunked_cache($key, $chunk_index);
        }

        // Try CacheManager (L1 object cache → L2 transient)
        $cached = $cache->get( $key, 'db_query' );
        if (false !== $cached) {
            return $cached;
        }

        $data = call_user_func($callback);

        // Only cache if data is not false or null
        if ($data !== false && $data !== null) {
            $this->store_data_intelligently($key, $data, $duration);
        }

        return $data;
    }

    protected function store_data_intelligently(string $key, $data, int $duration)
    {
        $cache = CacheManager::get_instance();

        if (!is_array($data)) {
            $cache->set( $key, $data, 'db_query', $duration );
            return true;
        }

        $item_count = count($data);

        if ($item_count > 300) {
            // Large datasets: chunk to stay under 1 MB per cache entry
            $chunks      = array_chunk($data, 300, true);
            $chunk_count = count($chunks);

            foreach ($chunks as $index => $chunk) {
                $cache->set( $key . '_chunk_' . $index, $chunk, 'db_query', $duration );
            }

            $cache->set( $key . '_chunk_index', $chunk_count, 'db_query', $duration );
            return true;
        }

        $cache->set( $key, $data, 'db_query', $duration );
        return true;
    }

    /**
     * Store data in chunks
     */
    protected function store_chunked_cache(string $key, $data, int $duration)
    {
        // For arrays, chunk the array itself
        if (is_array($data)) {
            $chunks = array_chunk($data, 100, true);
            $chunk_count = count($chunks);

            foreach ($chunks as $index => $chunk) {
                CacheManager::get_instance()->set( $key . '_chunk_' . $index, $chunk, 'db_query', $duration );
            }

            // Store the chunk index
            CacheManager::get_instance()->set( $key . '_chunk_index', $chunk_count, 'db_query', $duration );
        } else {
            // For non-arrays, fall back to regular storage
            CacheManager::get_instance()->set( $key, $data, 'db_query', $duration );
        }
    }

    /**
     * Retrieve chunked cache
     */
    protected function retrieve_chunked_cache(string $key, int $chunk_count)
    {
        $data = [];

        for ($i = 0; $i < $chunk_count; $i++) {
            $chunk = CacheManager::get_instance()->get( $key . '_chunk_' . $i, 'db_query' );
            if ($chunk === false || ! is_array( $chunk )) {
                // Cache is incomplete, return false to trigger regeneration
                $this->clear_chunked_cache($key, $chunk_count);
                return false;
            }

            // Preserve the original array keys so large cached datasets come
            // back in the exact same shape they were stored in.
            foreach ( $chunk as $chunk_key => $chunk_value ) {
                $data[ $chunk_key ] = $chunk_value;
            }
        }

        return $data;
    }

    /**
     * Clear chunked cache
     */
    protected function clear_chunked_cache(string $key, int $chunk_count)
    {
        for ($i = 0; $i < $chunk_count; $i++) {
            CacheManager::get_instance()->delete( $key . '_chunk_' . $i, 'db_query' );
        }
        CacheManager::get_instance()->delete( $key . '_chunk_index', 'db_query' );
    }

    /**
     * Clear all caches for this table
     *
     * Completely clears all cached data for this table including:
     * - Increments cache version
     * - Deletes all related transients
     * - Clears object cache if supported
     * - Clears static caches
     *
     * @return void
     * @access public
     * @since  1.2.0
     *
     * @example
     * $table->clear_all_caches();
     */
    public function clear_all_caches()
    {
        global $wpdb;

        // Increment cache version to invalidate all BaseTable-keyed entries.
        $version_key = $this->cache_group . '_cache_version';
        $this->cache_version = (string) time();
        CacheManager::get_instance()->set( $version_key, $this->cache_version, 'db_query' );

        // ── Wipe ALL transients that belong to this table ──────────────────────
        // Two key-origin patterns exist (both now share the table_name prefix):
        //
        //  A) BaseTable    : ultraguard_db_query_{cache_group}_{blog}_v{ver}_{type}_{hash}
        //  B) DatabaseCrud : ultraguard_db_query_{table_name}_{operation}_{hash}
        //
        // Both are caught by `ultraguard_db_query_{table_name}%` because
        //  - cache_group is set to 'ultraguard_' . table_name (or equal to table_name)
        //  - DatabaseCrud keys now put table_name first after the key-format fix.
        $base_prefix = 'ultraguard_db_query_' . $this->table_name;

        foreach ( [ $base_prefix, 'ultraguard_db_query_' . $this->cache_group ] as $pfx ) {
            $like_val = '_transient_'         . $wpdb->esc_like( $pfx ) . '%';
            $like_to  = '_transient_timeout_' . $wpdb->esc_like( $pfx ) . '%';
            $wpdb->query(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
                $wpdb->prepare(
                    "DELETE FROM {$wpdb->options}
                    WHERE option_name LIKE %s OR option_name LIKE %s",
                    $like_val,
                    $like_to
                )
            );
        }

        // Flush WordPress's in-memory alloptions and object-cache group so
        // the SQL DELETE takes effect in the current request immediately.
        wp_cache_delete( 'alloptions', 'options' );

        if ( function_exists( 'wp_cache_flush_group' ) ) {
            wp_cache_flush_group( $this->cache_group );
            wp_cache_flush_group( 'ultraguard_db_query' );
        }

        // Clear static caches.
        $this->clear_static_caches();

        do_action( 'ultraguard_cleared_cache', $this->table_name );
    }

    /**
     * Clear static property caches
     *
     * Clears any static caches used by this class instance.
     *
     * @return void
     * @access private
     * @since  1.2.0
     */
    private function clear_static_caches()
    {
        if (isset(self::$table_columns_cache[$this->table_name])) {
            unset(self::$table_columns_cache[$this->table_name]);
        }
    }

    /**
     * Clear single record cache
     *
     * Clears cache for a specific record by ID.
     *
     * @param int $id Record ID to clear cache for
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function clear_record_cache(int $id)
    {
        if ($id <= 0) {
            return;
        }

        CacheManager::get_instance()->delete( $this->get_cache_key('single', $id), 'db_query' );
    }

    /**
     * Clear list caches (paginated, search, etc.)
     *
     * Clears all list-related caches while preserving single record caches.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function clear_list_caches()
    {
        global $wpdb;

        // Cover BOTH key-origin patterns (see clear_all_caches for full explanation):
        //  A) BaseTable   : ultraguard_db_query_{cache_group}_...
        //  B) DatabaseCrud: ultraguard_db_query_{table_name}_{operation}_...
        foreach ( [ 'ultraguard_db_query_' . $this->table_name, 'ultraguard_db_query_' . $this->cache_group ] as $pfx ) {
            $wpdb->query(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
                $wpdb->prepare(
                    "DELETE FROM {$wpdb->options}
                    WHERE option_name LIKE %s OR option_name LIKE %s",
                    '_transient_'         . $wpdb->esc_like( $pfx ) . '%',
                    '_transient_timeout_' . $wpdb->esc_like( $pfx ) . '%'
                )
            );
        }

        wp_cache_delete( 'alloptions', 'options' );

        if ( function_exists( 'wp_cache_flush_group' ) ) {
            wp_cache_flush_group( 'ultraguard_db_query' );
        }
    }

    /**
     * Invalidate caches after insert operation
     *
     * Called after inserting a new record to clear relevant caches.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function invalidate_caches_after_insert()
    {
        // clear_list_caches() SQL uses the wrong prefix and misses count/paginate
        // type keys. clear_all_caches() bumps the version — the only reliable path.
        $this->clear_all_caches();
    }

    /**
     * Invalidate caches after update operation
     *
     * Called after updating a record to clear relevant caches.
     *
     * @param int $id Record ID that was updated
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function invalidate_caches_after_update($id)
    {
        if ($id > 0) {
            $this->clear_record_cache($id);
        }
        // clear_list_caches() SQL uses the wrong prefix and misses count/paginate
        // type keys. clear_all_caches() bumps the version — the only reliable path.
        $this->clear_all_caches();
    }

    /**
     * Invalidate caches after delete operation
     *
     * Called after deleting a record to clear relevant caches.
     *
     * @param int $id Record ID that was deleted
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function invalidate_caches_after_delete($id)
    {
        if ($id > 0) {
            $this->clear_record_cache($id);
        }
        // clear_list_caches() SQL uses the wrong prefix and misses count/paginate
        // type keys. clear_all_caches() bumps the version — the only reliable path.
        $this->clear_all_caches();
    }

    /**
     * Invalidate caches after bulk operations
     *
     * Called after bulk operations to clear relevant caches.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function invalidate_caches_after_bulk()
    {
        // clear_list_caches() SQL uses the wrong prefix and misses count/paginate
        // type keys. clear_all_caches() bumps the version — the only reliable path.
        $this->clear_all_caches();
    }

    /**
     * Clear specific cache type
     *
     * Clears only caches of a specific type (e.g., 'chart', 'count', 'search').
     *
     * @param string $type Cache type to clear
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function clear_cache(string $type)
    {
        global $wpdb;

        $cache_prefix = $this->cache_group . '_%_v' . $this->cache_version . '_' . sanitize_key($type) . '_%';
        $wpdb->query(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
            $wpdb->prepare(
                "DELETE FROM {$wpdb->options}
                WHERE (option_name LIKE %s OR option_name LIKE %s)",
                '_transient_' . $cache_prefix,
                '_transient_timeout_' . $cache_prefix
            )
        );
    }

    // ============================================================
    // CREATE OPERATIONS
    // ============================================================

    /**
     * Insert new record
     *
     * Inserts a new record into the table with automatic validation,
     * sanitization, timestamp addition, and cache invalidation.
     *
     * @param array $data Record data to insert
     * @return int|false Inserted record ID or false on failure
     * @access public
     * @since  1.2.0
     * @throws \Exception If validation fails
     *
     * @example
     * $id = $table->insert([
     *     'name' => 'John Doe',
     *     'email' => 'john@example.com'
     * ]);
     */
    public function insert(array $data)
    {
        // Validate and prepare data
        $data = $this->prepare_data_for_insert($data);
        if (!$data) {
            return false;
        }

        // Add timestamps and user references
        $data = $this->add_timestamps($data, 'insert');
        $data = $this->add_user_references($data, 'insert');

        // Call before hook
        $data = $this->before_insert($data);

        // Perform insertion
        $id = DatabaseCrud::insert($this->table_name, $data, null, true);

        if ($id) {
            $this->invalidate_caches_after_insert();
            $this->after_insert((int) $id, $data);
            do_action("ultraguard_{$this->table_name}_inserted", (int) $id, $data);
        }

        return $id;
    }

    /**
     * Prepare data for insert operation
     *
     * Internal method to prepare data for insertion by merging defaults,
     * validating, sanitizing, and removing auto-increment ID.
     *
     * @param array $data Raw data to prepare
     * @return array|false Prepared data or false on failure
     * @access private
     * @since  1.2.0
     */
    private function prepare_data_for_insert(array $data)
    {
        // Merge with defaults
        $data = wp_parse_args($data, $this->get_defaults());

        // Validate data
        $validated_data = $this->validate_data($data);
        if (!$validated_data) {
            $this->log_validation_error($data);
            return false;
        }

        // Sanitize data
        $sanitized_data = $this->sanitize_data($validated_data);

        // Encode sensitive fields so they survive DatabaseCrud's sanitize pass.
        $sanitized_data = $this->codec_encode($sanitized_data);

        // Remove auto-increment ID if present in insert data
        unset($sanitized_data['id']);

        return $sanitized_data;
    }

    /**
     * Bulk insert records
     *
     * Inserts multiple records in a single operation with validation,
     * sanitization, and automatic chunking to prevent memory issues.
     *
     * @param array $records Array of record data arrays to insert
     * @return bool Success status
     * @access public
     * @since  1.2.0
     *
     * @example
     * $result = $table->bulk_insert([
     *     ['name' => 'John', 'email' => 'john@example.com'],
     *     ['name' => 'Jane', 'email' => 'jane@example.com']
     * ]);
     */
    public function bulk_insert($records)
    {
        if (empty($records)) {
            $this->log('Bulk insert called with empty records array', 'warning');
            return false;
        }

        // Limit bulk size to prevent memory issues
        if (count($records) > $this->max_bulk_size) {
            $this->log(sprintf('Bulk insert limited to %d records from %d provided', $this->max_bulk_size, count($records)), 'warning');
            $records = array_slice($records, 0, $this->max_bulk_size);
        }

        // Prepare each record
        $prepared_records = [];
        foreach ($records as $index => $record) {
            $record = (array) $record;
            $record = wp_parse_args($record, $this->get_defaults());

            $validated_record = $this->validate_data($record);
            if (!$validated_record) {
                $this->log(sprintf('Record %d failed validation in bulk insert', $index), 'warning');
                continue;
            }

            $sanitized_record = $this->sanitize_data($validated_record);
            unset($sanitized_record['id']);

            // Encode sensitive fields before they reach DatabaseCrud.
            $sanitized_record = $this->codec_encode($sanitized_record);

            $sanitized_record = $this->add_timestamps($sanitized_record, 'insert');
            $sanitized_record = $this->add_user_references($sanitized_record, 'insert');
            $sanitized_record = $this->before_insert($sanitized_record);

            $prepared_records[] = $sanitized_record;
        }

        if (empty($prepared_records)) {
            $this->log('No valid records to insert', 'warning');
            return false;
        }

        $result = DatabaseCrud::bulk_insert($this->table_name, $prepared_records, null, true);

        if ($result) {
            $this->invalidate_caches_after_bulk();
            $this->after_bulk_insert($prepared_records);
            do_action("ultraguard_{$this->table_name}_bulk_inserted", count($prepared_records));
        }

        return $result;
    }

    // ============================================================
    // READ OPERATIONS (WITH COMPREHENSIVE CACHING)
    // ============================================================

    /**
     * Get record by ID with caching
     *
     * Retrieves a single record by its ID with automatic caching.
     * Returns record data merged with defaults for consistency.
     *
     * @param int  $id        Record ID to retrieve
     * @param bool $use_cache Whether to use cache (true) or force fresh data
     * @return array Record data with defaults applied or empty array on failure
     * @access public
     * @since  1.2.0
     *
     * @example
     * $user = $table->get(123);
     * echo $user['name'];
     */
    public function get(int $id, bool $use_cache = true)
    {
        if ($id <= 0) {
            $this->log(sprintf('Invalid ID requested: %d', $id), 'warning');
            return $this->get_defaults();
        }

        $cache_key = $this->get_cache_key('single', $id);

        return $this->get_cached_data($cache_key, function() use ($id) {
            $record = DatabaseCrud::get_by_id($this->table_name, $id, ARRAY_A);
            if ( $record ) {
                $record = $this->codec_decode( (array) $record );
                return wp_parse_args($record, $this->get_defaults());
            }
            return $this->get_defaults();
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get records by conditions with caching
     *
     * Retrieves multiple records based on conditions with support for
     * limit, ordering, offset, and caching.
     *
     * @param array  $conditions Where conditions as associative array
     * @param int    $limit      Maximum number of records to return (default: 100)
     * @param string $orderby    Field to order by (default: 'id')
     * @param string $order      Order direction: 'ASC' or 'DESC' (default: 'DESC')
     * @param int    $offset     Number of records to skip (default: 0)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Array of records with defaults applied
     * @access public
     * @since  1.2.0
     *
     * @example
     * $active_users = $table->get_by(
     *     ['status' => 'active'],
     *     50,
     *     'created_at',
     *     'DESC',
     *     0,
     *     true
     * );
     */
    public function get_by(array $conditions = [], int $limit = 100, string $orderby = 'id',
                          string $order = 'DESC', int $offset = 0, bool $use_cache = true)
    {
        // Validate and sanitize parameters
        $limit = max(0, $limit);
        $offset = max(0, $offset);
        $orderby = $this->sanitize_orderby_field($orderby);
        $order = $this->sanitize_order_direction($order);

        $cache_key = $this->get_cache_key('list', [$conditions, $limit, $orderby, $order, $offset]);

        return $this->get_cached_data($cache_key, function() use ($conditions, $limit, $orderby, $order, $offset) {
            $records = DatabaseCrud::get_records($this->table_name, [
                'where' => $this->sanitize_conditions($conditions),
                'limit' => $limit,
                'offset' => $offset,
                'orderby' => $orderby,
                'order' => $order,
                'output_type' => ARRAY_A
            ]);

            $records = is_array($records) ? $records : [];

            // Decode codec-protected fields, then apply defaults.
            $records = $this->codec_decode_rows( $records );

            foreach ($records as $key => $record) {
                $records[$key] = wp_parse_args($record, $this->get_defaults());
            }

            return $records;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get all records with caching
     *
     * Retrieves all records from the table with optional ordering and caching.
     * Warning: Use with caution on large tables.
     *
     * @param string $orderby   Field to order by (default: 'id')
     * @param string $order     Order direction: 'ASC' or 'DESC' (default: 'DESC')
     * @param bool   $use_cache Whether to use cache (default: true)
     * @return array All records from the table
     * @access public
     * @since  1.2.0
     *
     * @example
     * $all_users = $table->get_all('name', 'ASC');
     */
    public function get_all(string $orderby = 'id', string $order = 'DESC', bool $use_cache = true)
    {
        $orderby = $this->sanitize_orderby_field($orderby);
        $order = $this->sanitize_order_direction($order);

        $cache_key = $this->get_cache_key('list_all', [$orderby, $order]);

        return $this->get_cached_data($cache_key, function() use ($orderby, $order) {
            return $this->get_by([], 999999, $orderby, $order, 0, false);
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get multiple records with filters and caching using DatabaseCrud method
     *
     * Advanced method for retrieving records with comprehensive query options
     * including field selection, complex conditions, and output type control.
     *
     * @param array $args Query arguments with the following keys:
     *   - 'select'     => string SQL SELECT clause (default: '*')
     *   - 'where'      => array WHERE conditions (default: [])
     *   - 'orderby'    => string ORDER BY field (default: 'id')
     *   - 'order'      => string ORDER direction (default: 'DESC')
     *   - 'limit'      => int Maximum records (default: 0 - no limit)
     *   - 'offset'     => int Records to skip (default: 0)
     *   - 'output_type'=> string Output type: OBJECT or ARRAY_A (default: ARRAY_A)
     * @param bool $use_cache Whether to use cache (default: true)
     * @return array Array of records
     * @access public
     * @since  1.2.0
     *
     * @example
     * $users = $table->get_records([
     *     'select' => 'id, name, email',
     *     'where' => ['status' => 'active', 'age >=' => 18],
     *     'orderby' => 'name',
     *     'order' => 'ASC',
     *     'limit' => 100,
     *     'output_type' => ARRAY_A
     * ]);
     */
    public function get_records(array $args = [], bool $use_cache = true)
    {
        // SECURE: Default arguments with validation
        $defaults = [
            'select' => '*',
            'where' => [],
            'orderby' => 'id',
            'order' => 'DESC',
            'limit' => 0,
            'offset' => 0,
            'output_type' => ARRAY_A
        ];

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

        // Extract and validate arguments
        $select = $args['select'];
        $where = is_array($args['where']) ? $args['where'] : [];
        $orderby = $this->sanitize_orderby_field($args['orderby']);
        $order = $this->sanitize_order_direction($args['order']);
        $limit = max(0, intval($args['limit']));
        $offset = max(0, intval($args['offset']));
        $output_type = in_array($args['output_type'], [OBJECT, ARRAY_A]) ? $args['output_type'] : ARRAY_A;

        // Build cache key
        $cache_key = $this->get_cache_key('records', [
            'select' => $select,
            'where' => $where,
            'orderby' => $orderby,
            'order' => $order,
            'limit' => $limit,
            'offset' => $offset,
            'output_type' => $output_type
        ]);

        return $this->get_cached_data($cache_key, function() use ($select, $where, $orderby, $order, $limit, $offset, $output_type, $args) {
            // Get records using DatabaseCrud
            $records = DatabaseCrud::get_records($this->table_name, [
                'select' => $select,
                'where' => $this->sanitize_conditions($where),
                'orderby' => $orderby,
                'order' => $order,
                'limit' => $limit,
                'offset' => $offset,
                'output_type' => $output_type
            ]);

            $records = is_array($records) ? $records : [];

            // Apply defaults to each record if using ARRAY_A output
            if ($output_type === ARRAY_A && !empty($records)) {
                // Decode codec-protected fields before applying defaults.
                $records = $this->codec_decode_rows( $records );
                foreach ($records as $key => $record) {
                    $records[$key] = wp_parse_args($record, $this->get_defaults());
                }
            }

            // Apply defaults to each record if using OBJECT output
            if ($output_type === OBJECT && !empty($records)) {
                $defaults = $this->get_defaults();
                foreach ($records as $key => $record) {
                    foreach ($defaults as $field => $value) {
                        if (!property_exists($record, $field)) {
                            $record->$field = $value;
                        }
                    }
                    $records[$key] = $record;
                }
            }

            // Allow filtering results
            $records = apply_filters("ultraguard_{$this->table_name}_get_records", $records, $args);

            return $records;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get records with pagination and caching
     *
     * Retrieves paginated records with total count and page information.
     *
     * @param int    $page       Page number (1-based, default: 1)
     * @param int    $per_page   Records per page (default: 20)
     * @param array  $conditions Where conditions (default: [])
     * @param string $orderby    Order by field (default: 'id')
     * @param string $order      Order direction (default: 'DESC')
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Paginated results with structure:
     *               [
     *                 'records' => array of records,
     *                 'total' => total records count,
     *                 'pages' => total pages,
     *                 'current_page' => current page number
     *               ]
     * @access public
     * @since  1.2.0
     *
     * @example
     * $page1 = $table->paginate(1, 10, ['status' => 'active']);
     * echo "Page 1 of {$page1['pages']}";
     * echo "Total records: {$page1['total']}";
     */
    public function paginate(int $page = 1, int $per_page = 20, array $conditions = [],
                            string $orderby = 'id', string $order = 'DESC', bool $use_cache = true)
    {
        $page = max(1, $page);
        $per_page = max(1, $per_page);
        $orderby = $this->sanitize_orderby_field($orderby);
        $order = $this->sanitize_order_direction($order);

        $cache_key = $this->get_cache_key('paginate', [$page, $per_page, $conditions, $orderby, $order]);

        return $this->get_cached_data($cache_key, function() use ($page, $per_page, $conditions, $orderby, $order) {
            $result = DatabaseCrud::get_paginated_records($this->table_name, [
                'page' => $page,
                'per_page' => $per_page,
                'where' => $this->sanitize_conditions($conditions),
                'orderby' => $orderby,
                'order' => $order,
                'output_type' => ARRAY_A
            ]);

            $result = wp_parse_args((array) $result, [
                'records' => [],
                'total' => 0,
                'pages' => 0,
                'current_page' => $page
            ]);

            if (!empty($result['records'])) {
                // Decode codec-protected fields before applying defaults.
                $result['records'] = $this->codec_decode_rows( $result['records'] );
                foreach ($result['records'] as $key => $record) {
                    $result['records'][$key] = wp_parse_args($record, $this->get_defaults());
                }
            }

            return $result;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Search records with caching
     *
     * Searches records across specified fields with fuzzy matching.
     *
     * @param string $search_term Search term to look for
     * @param array  $fields      Fields to search in (empty = use searchable_fields)
     * @param int    $limit       Maximum results (default: 50)
     * @param bool   $use_cache   Whether to use cache (default: true)
     * @return array Matching records
     * @access public
     * @since  1.2.0
     *
     * @example
     * $results = $table->search('john', ['name', 'email'], 25);
     */
    public function search(string $search_term, array $fields = [], int $limit = 50, bool $use_cache = true)
    {
        $search_term = sanitize_text_field($search_term);

        if (empty($search_term) || empty(trim($search_term))) {
            return [];
        }

        $fields = array_map('sanitize_key', $fields);
        $limit = max(1, $limit);

        if (empty($fields)) {
            $fields = $this->get_searchable_fields();
        }

        $cache_key = $this->get_cache_key('search', [$search_term, $fields, $limit]);

        return $this->get_cached_data($cache_key, function() use ($search_term, $fields, $limit) {
            $records = DatabaseCrud::search_records($this->table_name, $search_term, $fields, [], $limit);
            $records = is_array($records) ? $records : [];

            foreach ($records as $key => $record) {
                $records[$key] = wp_parse_args($record, $this->get_defaults());
            }

            return $records;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Check if record exists with caching
     *
     * Checks if any record matches the given conditions.
     *
     * @param array $conditions Where conditions to check
     * @param bool  $use_cache  Whether to use cache (default: true)
     * @return bool True if at least one matching record exists
     * @access public
     * @since  1.2.0
     *
     * @example
     * if ($table->exists(['email' => 'test@example.com'])) {
     *     echo 'Email already registered';
     * }
     */
    public function exists(array $conditions, bool $use_cache = true)
    {
        if (empty($conditions)) {
            return false;
        }

        if ($this->has_complex_conditions($conditions)) {
            return $this->exists_complex($conditions);
        }

        $cache_key = $this->get_cache_key('exists', $conditions);

        return $this->get_cached_data($cache_key, function() use ($conditions) {
            return DatabaseCrud::record_exists($this->table_name, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Handle complex conditions for exists check
     *
     * Internal method to check existence with complex nested conditions.
     *
     * @param array $conditions Complex conditions array
     * @return bool True if at least one matching record exists
     * @access protected
     * @since  1.2.0
     */
    protected function exists_complex(array $conditions)
    {
        global $wpdb;
        $table_name = DatabaseSchema::get_table_name($this->table_name);

        if (!$table_name) {
            return false;
        }

        $where_sql = $this->build_advanced_where($conditions);

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

        $query = "SELECT EXISTS(SELECT 1 FROM {$table_name} {$where_sql})";
        $result = $wpdb->get_var($query);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()

        return (bool) $result;
    }

    /**
     * Check if conditions contain complex nested arrays
     *
     * Internal method to detect complex condition structures.
     *
     * @param array $conditions Conditions array to check
     * @return bool True if complex conditions found
     * @access protected
     * @since  1.2.0
     */
    protected function has_complex_conditions(array $conditions)
    {
        foreach ($conditions as $condition) {
            if (is_array($condition) && isset($condition['operator'])) {
                return true;
            }
        }
        return false;
    }

    // ============================================================
    // UPDATE OPERATIONS
    // ============================================================

    /**
     * Update record
     *
     * Updates an existing record by ID with validation, sanitization,
     * timestamp updates, and cache invalidation.
     *
     * @param int   $id   Record ID to update
     * @param array $data Data to update
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     *
     * @example
     * $success = $table->update(123, ['name' => 'John Updated', 'status' => 'inactive']);
     */
    public function update(int $id, array $data)
    {
        if ($id <= 0) {
            $this->log(sprintf('Invalid ID for update: %d', $id), 'warning');
            return false;
        }

        $data = $this->sanitize_data($data);
        // Encode sensitive fields so they survive DatabaseCrud's sanitize pass.
        $data = $this->codec_encode($data);
        $data = $this->add_timestamps($data, 'update');
        $data = $this->add_user_references($data, 'update');
        $data = $this->before_update($id, $data);

        // Remove ID from update data if present
        unset($data['id']);

        $result = DatabaseCrud::update($this->table_name, $data, ['id' => $id], null, null, true);

        if ($result) {
            $this->invalidate_caches_after_update($id);
            $this->after_update($id, $data);
            do_action("ultraguard_{$this->table_name}_updated", $id, $data);
        }

        return (bool) $result;
    }

    /**
     * Update records by conditions
     *
     * Updates multiple records matching given conditions.
     *
     * @param array $data       Data to update
     * @param array $conditions Where conditions to match
     * @return int Number of updated rows
     * @access public
     * @since  1.2.0
     *
     * @example
     * $updated = $table->update_where(
     *     ['status' => 'archived'],
     *     ['last_login <' => '2023-01-01']
     * );
     * echo "Archived $updated inactive users";
     */
    public function update_where(array $data, array $conditions)
    {
        if (empty($data) || empty($conditions)) {
            return 0;
        }

        $data = $this->sanitize_data($data);
        // Encode sensitive fields so they survive DatabaseCrud's sanitize pass.
        $data = $this->codec_encode($data);
        $data = $this->add_timestamps($data, 'update');

        // Remove ID from update data if present
        unset($data['id']);

        $result = DatabaseCrud::update($this->table_name, $data, $this->sanitize_conditions($conditions), null, null, true);

        if ($result) {
            $this->invalidate_caches_after_bulk();
            do_action("ultraguard_{$this->table_name}_bulk_updated", $result);
        }

        return (int) $result;
    }

    // ============================================================
    // DELETE OPERATIONS
    // ============================================================

    /**
     * Delete record (hard delete)
     *
     * Permanently deletes a record by ID with cache invalidation.
     *
     * @param int $id Record ID to delete
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     * @see    soft_delete() For non-permanent deletion
     *
     * @example
     * $deleted = $table->delete(123);
     * if ($deleted) echo 'Record deleted';
     */
    public function delete(int $id)
    {
        if ($id <= 0) {
            $this->log(sprintf('Invalid ID for delete: %d', $id), 'warning');
            return false;
        }

        $this->before_delete($id);
        $result = DatabaseCrud::delete($this->table_name, ['id' => $id], null, true);

        if ($result) {
            $this->invalidate_caches_after_delete($id);
            $this->after_delete($id);
            do_action("ultraguard_{$this->table_name}_deleted", $id);
        }

        return (bool) $result;
    }

    /**
     * Delete records by conditions
     *
     * Permanently deletes multiple records matching given conditions.
     *
     * @param array $conditions Where conditions to match
     * @return int Number of deleted rows
     * @access public
     * @since  1.2.0
     *
     * @example
     * $deleted = $table->delete_where(['status' => 'spam']);
     * echo "Deleted $deleted spam records";
     */
    public function delete_where(array $conditions)
    {
        if (empty($conditions)) {
            return 0;
        }

        $result = DatabaseCrud::delete($this->table_name, $this->sanitize_conditions($conditions), null, true);

        if ($result) {
            $this->invalidate_caches_after_bulk();
            do_action("ultraguard_{$this->table_name}_bulk_deleted", $result);
        }

        return (int) $result;
    }

    /**
     * Bulk delete records by IDs
     *
     * Deletes multiple records by their IDs in sequence.
     *
     * @param array $ids Array of record IDs to delete
     * @return bool True if all deletions succeeded, false otherwise
     * @access public
     * @since  1.2.0
     *
     * @example
     * $success = $table->bulk_delete([1, 2, 3, 4, 5]);
     */
    public function bulk_delete($ids)
    {
        $ids = array_filter(array_map('absint', $ids));

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

        $success = true;
        foreach ($ids as $id) {
            if (!$this->delete($id)) {
                $success = false;
            }
        }

        return $success;
    }

    // ============================================================
    // AGGREGATION OPERATIONS WITH CACHING
    // ============================================================

    /**
     * Count records with caching
     *
     * Counts records matching given conditions with caching support.
     *
     * @param array  $conditions Where conditions (default: [])
     * @param string $field      Field to count (default: 'id')
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return int Number of matching records
     * @access public
     * @since  1.2.0
     *
     * @example
     * $active_count = $table->count(['status' => 'active']);
     * $total_count = $table->count();
     */
    public function count(array $conditions = [], string $field = 'id', bool $use_cache = true)
    {
        $field = $this->sanitize_count_field($field);

        $cache_key = $this->get_cache_key('count', [$conditions, $field]);

        return (int) $this->get_cached_data($cache_key, function() use ($conditions, $field) {
            return DatabaseCrud::get_count($this->table_name, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Sum field values with caching
     *
     * Calculates sum of a numeric field for matching records.
     *
     * @param string $field      Field to sum (must be numeric)
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return float Sum of field values
     * @access public
     * @since  1.2.0
     *
     * @example
     * $total_sales = $table->sum('amount', ['status' => 'completed']);
     */
    public function sum(string $field, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_key($field);

        $cache_key = $this->get_cache_key('sum', [$field, $conditions]);

        return (float) $this->get_cached_data($cache_key, function() use ($field, $conditions) {
            return DatabaseCrud::get_aggregate($this->table_name, 'SUM', $field, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get average of field with caching
     *
     * Calculates average value of a numeric field for matching records.
     *
     * @param string $field      Field to average (must be numeric)
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return float Average of field values
     * @access public
     * @since  1.2.0
     *
     * @example
     * $avg_score = $table->avg('score', ['status' => 'active']);
     */
    public function avg(string $field, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_key($field);

        $cache_key = $this->get_cache_key('avg', [$field, $conditions]);

        return (float) $this->get_cached_data($cache_key, function() use ($field, $conditions) {
            return DatabaseCrud::get_aggregate($this->table_name, 'AVG', $field, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get maximum value with caching
     *
     * Finds maximum value of a field for matching records.
     *
     * @param string $field      Field to check
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return mixed Maximum value found
     * @access public
     * @since  1.2.0
     *
     * @example
     * $max_age = $table->max('age');
     * $latest_date = $table->max('created_at');
     */
    public function max(string $field, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_key($field);

        $cache_key = $this->get_cache_key('max', [$field, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($field, $conditions) {
            return DatabaseCrud::get_aggregate($this->table_name, 'MAX', $field, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get minimum value with caching
     *
     * Finds minimum value of a field for matching records.
     *
     * @param string $field      Field to check
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return mixed Minimum value found
     * @access public
     * @since  1.2.0
     *
     * @example
     * $min_price = $table->min('price');
     * $earliest_date = $table->min('created_at');
     */
    public function min(string $field, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_key($field);

        $cache_key = $this->get_cache_key('min', [$field, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($field, $conditions) {
            return DatabaseCrud::get_aggregate($this->table_name, 'MIN', $field, $this->sanitize_conditions($conditions));
        }, $this->short_cache_duration, $use_cache);
    }

    // ============================================================
    // CHART & VISUALIZATION DATA WITH CACHING
    // ============================================================

    /**
     * Get data formatted for line/bar charts with caching
     *
     * Generates time-series data for charting with various grouping options.
     *
     * @param string $date_field   Date field for time axis (default: 'created_at')
     * @param string $value_field  Field to aggregate (default: 'id')
     * @param string $group_by     Grouping period: 'hour', 'day', 'week', 'month', 'year' (default: 'day')
     * @param array  $conditions   Where conditions (default: [])
     * @param int    $limit        Number of data points (default: 30, max: 365)
     * @param string $agg_function Aggregate function: 'COUNT', 'SUM', 'AVG', 'MAX', 'MIN' (default: 'COUNT')
     * @param bool   $use_cache    Whether to use cache (default: true)
     * @return array Chart data with structure:
     *               [
     *                 'labels' => array of formatted period labels,
     *                 'data'   => array of aggregated values,
     *                 'raw'    => array of raw database results
     *               ]
     * @access public
     * @since  1.2.0
     *
     * @example
     * $chart_data = $table->get_chart_data(
     *     'created_at',
     *     'amount',
     *     'day',
     *     ['status' => 'completed'],
     *     30,
     *     'SUM'
     * );
     */
    public function get_chart_data(string $date_field = 'created_at', string $value_field = 'id',
                                   string $group_by = 'day', array $conditions = [], int $limit = 30,
                                   string $agg_function = 'COUNT', bool $use_cache = true)
    {
        // Validate parameters
        $date_field = $this->sanitize_date_field($date_field);
        $value_field = sanitize_key($value_field);
        $group_by = $this->sanitize_group_by_period($group_by);
        $limit = max(1, min($limit, 365)); // Limit to reasonable range
        $agg_function = $this->sanitize_aggregate_function($agg_function);

        $cache_key = $this->get_cache_key('chart', [$date_field, $value_field, $group_by, $conditions, $limit, $agg_function]);

        return $this->get_cached_data($cache_key, function() use ($date_field, $value_field, $group_by, $conditions, $limit, $agg_function) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

            if (!$table_name) {
                return ['labels' => [], 'data' => [], 'raw' => []];
            }

            $date_format = $this->get_date_format_sql($group_by);
            $where_sql = $this->build_where_clause($conditions);
            $agg_sql = $this->build_aggregation_sql($agg_function, $value_field);

            // Validate date format for SQL safety
            $valid_date_formats = ['%Y-%m-%d %H:00:00', '%Y-%m-%d', '%Y-%u', '%Y-%m', '%Y'];
            if (!in_array($date_format, $valid_date_formats, true)) {
                $date_format = '%Y-%m-%d';
            }

            // Escape % signs in the MySQL date format so wpdb->prepare does not
            // miscount them as additional sprintf-style placeholders (%Y, %m, %d → %%Y, %%m, %%d).
            $escaped_date_format = str_replace( '%', '%%', $date_format );

            $query = "SELECT DATE_FORMAT({$date_field}, '{$escaped_date_format}') as period,
                             {$agg_sql} as value
                      FROM {$table_name}
                      {$where_sql}
                      GROUP BY period
                      ORDER BY period DESC
                      LIMIT %d";

            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT DATE_FORMAT({$date_field}, '{$escaped_date_format}') as period,
                             {$agg_sql} as value
                      FROM {$table_name}
                      {$where_sql}
                      GROUP BY period
                      ORDER BY period DESC
                      LIMIT %d",
                    $limit
                ),
                ARRAY_A
            ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.
            $results = is_array($results) ? $results : [];
            $results = array_reverse($results);

            $labels = [];
            $data = [];

            foreach ($results as $row) {
                $period = isset($row['period']) ? (string) $row['period'] : '';
                $value = isset($row['value']) ? (float) $row['value'] : 0;

                $labels[] = $this->format_period_label($period, $group_by);
                $data[] = $value;
            }

            return [
                'labels' => $labels,
                'data'   => $data,
                'raw'    => $results,
            ];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get data formatted for pie/donut charts with caching
     *
     * Generates categorical data for pie/donut charts.
     *
     * @param string $category_field Field to group by (categorical)
     * @param string $value_field    Field to aggregate (default: 'id')
     * @param string $agg_function   Aggregate function: 'COUNT', 'SUM' (default: 'COUNT')
     * @param array  $conditions     Where conditions (default: [])
     * @param int    $limit          Number of slices (default: 10, max: 50)
     * @param bool   $use_cache      Whether to use cache (default: true)
     * @return array Pie chart data with structure:
     *               [
     *                 'labels' => array of category labels,
     *                 'data'   => array of values,
     *                 'colors' => array of hex colors,
     *                 'raw'    => array of raw database results
     *               ]
     * @access public
     * @since  1.2.0
     *
     * @example
     * $pie_data = $table->get_pie_chart_data(
     *     'status',
     *     'id',
     *     'COUNT',
     *     [],
     *     5
     * );
     */
    public function get_pie_chart_data(string $category_field, string $value_field = 'id',
                                       string $agg_function = 'COUNT', array $conditions = [],
                                       int $limit = 10, bool $use_cache = true)
    {
        $category_field = sanitize_key($category_field);
        $value_field = sanitize_key($value_field);
        $agg_function = $this->sanitize_aggregate_function($agg_function);
        $limit = max(1, min($limit, 50));

        $cache_key = $this->get_cache_key('pie', [$category_field, $value_field, $agg_function, $conditions, $limit]);

        return $this->get_cached_data($cache_key, function() use ($category_field, $value_field, $agg_function, $conditions, $limit) {
            $results = $this->get_grouped_data($category_field, $agg_function, $value_field, $conditions, $limit, false);

            $labels = [];
            $data = [];
            $colors = [];

            $color_palette = $this->get_color_palette();
            $color_index = 0;

            foreach ($results as $row) {
                $category_value = isset($row[$category_field]) ? (string) $row[$category_field] : '';
                $aggregate_value = isset($row['aggregate_value']) ? (float) $row['aggregate_value'] : 0;

                $labels[] = $this->format_category_label($category_value);
                $data[] = $aggregate_value;
                $colors[] = $color_palette[$color_index % count($color_palette)];
                $color_index++;
            }

            return [
                'labels' => $labels,
                'data'   => $data,
                'colors' => $colors,
                'raw'    => $results,
            ];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get grouped data for charting
     *
     * Internal method to retrieve grouped data for chart generation.
     *
     * @param string $group_field  Field to group by
     * @param string $agg_function Aggregate function
     * @param string $agg_field    Field to aggregate
     * @param array  $conditions   Where conditions
     * @param int    $limit        Result limit
     * @param bool   $use_cache    Whether to use cache
     * @return array Grouped results
     * @access private
     * @since  1.2.0
     */
    private function get_grouped_data(string $group_field, string $agg_function, string $agg_field,
                                     array $conditions, int $limit, bool $use_cache = true)
    {
        $group_field = sanitize_key($group_field);
        $agg_function = $this->sanitize_aggregate_function($agg_function);
        $agg_field = sanitize_key($agg_field);
        $limit = max(1, $limit);

        if ($agg_function === 'COUNT') {
            $results = DatabaseCrud::get_grouped_count($this->table_name, $group_field, $this->sanitize_conditions($conditions), $limit);
            $formatted_results = [];

            foreach ((array) $results as $result) {
                $result = (array) $result;
                $group_value = isset($result[$group_field]) ? $result[$group_field] : '';
                $formatted_results[] = [
                    $group_field => $group_value,
                    'aggregate_value' => isset($result['count']) ? (int) $result['count'] : 0
                ];
            }

            return $formatted_results;
        }

        return $this->custom_group_by($group_field, $agg_function, $agg_field, $conditions, $limit);
    }

    // ============================================================
    // SECURITY AND VALIDATION METHODS
    // ============================================================

    /**
     * Check user capability for operation
     *
     * Verifies if current user has required capabilities for the specified operation.
     *
     * @param string $operation Operation type: 'create', 'read', 'edit', 'delete'
     * @return bool True if user has required capabilities
     * @access protected
     * @since  1.2.0
     *
     * @example
     * if (!$this->check_capability('create')) {
     *     wp_die('Insufficient permissions');
     * }
     */
    protected function check_capability(string $operation)
    {
        $current_user_id = get_current_user_id();
        $capabilities = $this->get_required_capabilities($operation);

        foreach ($capabilities as $capability) {
            $current_user_can = 'manage_options' === $capability
                ? \UltraguardPro\ultraguard_current_user_can_manage()
                : current_user_can($capability);

            if (!$current_user_can) {
                // Allow cron and AJAX operations for system tasks
                if (wp_doing_cron() || wp_doing_ajax()) {
                    continue;
                }

                // Allow administrators to bypass some checks in debug mode
                if (defined('WP_DEBUG') && WP_DEBUG && \UltraguardPro\ultraguard_current_user_can_manage()) {
                    continue;
                }

                return false;
            }
        }

        return true;
    }

    /**
     * Get required capabilities for operation
     *
     * Returns the capabilities required for a specific operation.
     * Can be filtered by child classes.
     *
     * @param string $operation Operation type: 'create', 'read', 'edit', 'delete'
     * @return array Required capability strings
     * @access protected
     * @since  1.2.0
     */
    protected function get_required_capabilities(string $operation)
    {
        $default_caps = [
            'create' => ['manage_options'],
            'read'   => ['read'],
            'edit'   => ['edit_posts'],
            'delete' => ['delete_posts']
        ];

        return apply_filters("ultraguard_{$this->table_name}_capabilities",
            $default_caps[$operation] ?? ['manage_options'],
            $operation
        );
    }

    /**
     * Sanitize conditions array
     *
     * Sanitizes all keys and values in a conditions array for safe database use.
     *
     * @param array $conditions Raw conditions array
     * @return array Sanitized conditions
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_conditions(array $conditions)
    {
        $sanitized = [];

        foreach ($conditions as $field => $value) {
            // Split "field_name operator" keys (e.g. 'created_at >=', 'id !=', 'status LIKE')
            // The field name is sanitized; the operator is validated against an allowlist.
            $allowed_operators = [ '=', '!=', '<>', '<', '>', '<=', '>=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN' ];
            $operator          = '';

            if ( preg_match( '/^([a-z0-9_]+)\s+(.+)$/i', (string) $field, $m ) ) {
                $raw_op   = strtoupper( trim( $m[2] ) );
                if ( in_array( $raw_op, $allowed_operators, true ) ) {
                    $field    = sanitize_key( $m[1] );
                    $operator = $raw_op;
                } else {
                    $field = sanitize_key( (string) $field );
                }
            } else {
                $field = sanitize_key( (string) $field );
            }

            // Re-append the operator to preserve it through the pipeline
            $key = $operator ? "{$field} {$operator}" : $field;

            if (is_array($value)) {
                $sanitized[$key] = array_map([$this, 'sanitize_condition_value'], $value);
            } else {
                $sanitized[$key] = $this->sanitize_condition_value($value);
            }
        }

        return $sanitized;
    }

    /**
     * Sanitize condition value
     *
     * Sanitizes individual condition values based on their type.
     *
     * @param mixed $value Condition value to sanitize
     * @return mixed Sanitized value
     * @access private
     * @since  1.2.0
     */
    private function sanitize_condition_value($value)
    {
        if (is_numeric($value)) {
            return $value + 0; // Convert to int or float
        } elseif (is_string($value)) {
            return sanitize_text_field($value);
        } elseif (is_bool($value)) {
            return (bool) $value;
        }

        return $value;
    }

    /**
     * Sanitize order by field
     *
     * Sanitizes ORDER BY field name for safe SQL use.
     *
     * @param string $field Field name to sanitize
     * @return string Sanitized field name or 'id' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_orderby_field(string $field)
    {
        $field = sanitize_sql_orderby($field);
        return $field ?: 'id';
    }

    /**
     * Sanitize order direction
     *
     * Sanitizes ORDER direction (ASC/DESC) for safe SQL use.
     *
     * @param string $direction Order direction to sanitize
     * @return string 'ASC', 'DESC', or 'DESC' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_order_direction(string $direction)
    {
        $direction = strtoupper($direction);
        return in_array($direction, ['ASC', 'DESC']) ? $direction : 'DESC';
    }

    /**
     * Sanitize count field
     *
     * Sanitizes field name for COUNT operations.
     *
     * @param string $field Field name to sanitize
     * @return string Sanitized field name or 'id' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_count_field(string $field)
    {
        $field = sanitize_key($field);
        return $field ?: 'id';
    }

    /**
     * Sanitize date field
     *
     * Sanitizes date field name for date-related operations.
     *
     * @param string $field Date field name to sanitize
     * @return string Sanitized field name or 'created_at' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_date_field(string $field)
    {
        $field = sanitize_key($field);
        return $field ?: 'created_at';
    }

    /**
     * Sanitize group by period
     *
     * Validates and sanitizes grouping period for chart data.
     *
     * @param string $period Group by period to sanitize
     * @return string Valid period or 'day' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_group_by_period(string $period)
    {
        $valid_periods = ['hour', 'day', 'week', 'month', 'year'];
        return in_array($period, $valid_periods, true) ? $period : 'day';
    }

    /**
     * Sanitize aggregate function
     *
     * Validates and sanitizes SQL aggregate function names.
     *
     * @param string $function Aggregate function to sanitize
     * @return string Valid function or 'COUNT' if invalid
     * @access protected
     * @since  1.2.0
     */
    protected function sanitize_aggregate_function(string $function)
    {
        $function = strtoupper($function);
        $valid_functions = ['COUNT', 'SUM', 'AVG', 'MAX', 'MIN'];
        return in_array($function, $valid_functions, true) ? $function : 'COUNT';
    }

    // ============================================================
    // LOGGING AND ERROR HANDLING
    // ============================================================

    /**
     * Log message to WordPress error log
     *
     * Logs messages with timestamp, table name, and log level.
     *
     * @param string $message Log message content
     * @param string $level   Log level: 'info', 'warning', 'error', 'debug' (default: 'info')
     * @return void
     * @access protected
     * @since  1.2.0
     *
     * @example
     * $this->log('User created successfully', 'info');
     * $this->log('Failed to update record', 'error');
     */
    protected function log($message, $level = 'info')
    {
        $message = sanitize_text_field($message);
        $level = in_array($level, ['info', 'warning', 'error', 'debug']) ? $level : 'info';

        $log_message = sprintf(
            '[%s] [%s] %s: %s',
            current_time('mysql'),
            strtoupper($level),
            $this->table_name,
            $message
        );

        if (defined('WP_DEBUG') && WP_DEBUG) {
            \UltraguardPro\core\Logger::error( $log_message );
        }

        do_action('ultraguard_table_log', $this->table_name, $message, $level);
    }

    /**
     * Log capability error
     *
     * Logs unauthorized access attempts with user information.
     *
     * @param string $operation Operation that was attempted
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function log_capability_error(string $operation)
    {
        $current_user = wp_get_current_user();
        $username = $current_user->exists() ? $current_user->user_login : 'anonymous';

        $this->log(
            sprintf('User "%s" attempted unauthorized operation "%s" on table "%s"',
                $username,
                $operation,
                $this->table_name
            ),
            'warning'
        );
    }

    /**
     * Log validation error
     *
     * Logs data validation failures with sanitized data.
     *
     * @param array $data Data that failed validation
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function log_validation_error(array $data)
    {
        // Remove sensitive data before logging
        $safe_data = $this->remove_sensitive_data($data);

        $this->log(
            sprintf('Data validation failed for table "%s". Data: %s',
                $this->table_name,
                wp_json_encode($safe_data)
            ),
            'warning'
        );
    }

    /**
     * Remove sensitive data from array before logging
     *
     * Redacts sensitive fields (password, secret, token, etc.) from data before logging.
     *
     * @param array $data Data array to sanitize
     * @return array Safe data array with sensitive values redacted
     * @access private
     * @since  1.2.0
     */
    private function remove_sensitive_data(array $data)
    {
        $sensitive_fields = ['password', 'secret', 'token', 'key', 'auth', 'credential'];

        foreach ($data as $key => $value) {
            foreach ($sensitive_fields as $sensitive_field) {
                if (stripos($key, $sensitive_field) !== false) {
                    $data[$key] = '[REDACTED]';
                    break;
                }
            }
        }

        return $data;
    }

    // ============================================================
    // HELPER METHODS
    // ============================================================

    /**
     * Get searchable fields for this table
     *
     * Returns array of field names that should be included in search operations.
     * Child classes should override this method to specify searchable fields.
     *
     * @return array Searchable field names
     * @access protected
     * @since  1.2.0
     */
    protected function get_searchable_fields()
    {
        return ['id'];
    }

    /**
     * Add timestamps to data
     *
     * Automatically adds created_at and/or updated_at timestamps to data arrays.
     *
     * @param array  $data      Data array to add timestamps to
     * @param string $operation Operation type: 'insert' or 'update'
     * @return array Data with timestamps added
     * @access protected
     * @since  1.2.0
     */
    protected function add_timestamps(array $data, string $operation = 'insert')
    {
        // Use UTC (gmdate) so all timestamps are timezone-agnostic and comparable
        // to values written by domain methods that also use gmdate() / time().
        $timestamp = current_time( 'mysql', true ); // true = UTC

        if ($operation === 'insert') {
            // Use empty() not isset() — get_defaults() pre-populates these keys with ''
            // which would pass isset() and prevent the real timestamp from being written,
            // resulting in MySQL storing 0000-00-00 00:00:00.
            if ( empty( $data['created_at'] ) ) {
                $data['created_at'] = $timestamp;
            }
            if ( empty( $data['updated_at'] ) && $this->has_column( 'updated_at' ) ) {
                $data['updated_at'] = $timestamp;
            }
        } elseif ($operation === 'update') {
            // Only add updated_at if the column exists
            if ( $this->has_column( 'updated_at' ) ) {
                $data['updated_at'] = $timestamp;
            }
        }

        return $data;
    }

    /**
     * Add user references to data
     *
     * Automatically adds created_by and/or updated_by user IDs to data arrays.
     *
     * @param array  $data      Data array to add user references to
     * @param string $operation Operation type: 'insert' or 'update'
     * @return array Data with user references added
     * @access protected
     * @since  1.2.0
     */
    protected function add_user_references(array $data, string $operation = 'insert')
    {
        $user_id = get_current_user_id();

        if ($user_id > 0) {
            // Only inject created_by / updated_by when the column actually exists.
            // Login attempt tables, lockout tables, etc. deliberately omit these
            // columns — injecting phantom fields causes MySQL to reject the INSERT.
            if (
                $operation === 'insert'
                && ! isset( $data['created_by'] )
                && $this->has_column( 'created_by' )
            ) {
                $data['created_by'] = $user_id;
            }

            if (
                ! isset( $data['updated_by'] )
                && $this->has_column( 'updated_by' )
            ) {
                $data['updated_by'] = $user_id;
            }
        }

        return $data;
    }

    /**
     * Get SQL date format string based on grouping
     *
     * Returns MySQL DATE_FORMAT pattern for the specified grouping period.
     *
     * @param string $group_by Grouping type: 'hour', 'day', 'week', 'month', 'year'
     * @return string MySQL DATE_FORMAT pattern
     * @access protected
     * @since  1.2.0
     */
    protected function get_date_format_sql(string $group_by)
    {
        $formats = [
            'hour'   => '%Y-%m-%d %H:00:00',
            'day'    => '%Y-%m-%d',
            'week'   => '%Y-%u',
            'month'  => '%Y-%m',
            'year'   => '%Y',
        ];

        return $formats[$group_by] ?? $formats['day'];
    }

    /**
     * Format period label for display
     *
     * Formats period values from database into human-readable labels.
     *
     * @param string $period   Period value from database
     * @param string $group_by Grouping type
     * @return string Formatted label for display
     * @access protected
     * @since  1.2.0
     */
    protected function format_period_label(string $period, string $group_by)
    {
        if (empty($period)) {
            return __('Unknown', 'ultraguard-security-suite');
        }

        try {
            $timestamp = strtotime($period);

            if ($timestamp === false) {
                return $period;
            }

            switch ($group_by) {
                case 'hour':
                    return date_i18n('M j, g A', $timestamp);
                case 'day':
                    return date_i18n('M j', $timestamp);
                case 'week':
                    $week_number = (int) substr($period, -2);
                    /* translators: %d: integer */
                    return sprintf(__('Week %d', 'ultraguard-security-suite'), $week_number);
                case 'month':
                    return date_i18n('M Y', $timestamp);
                case 'year':
                    return date_i18n('Y', $timestamp);
                default:
                    return $period;
            }
        } catch (\Exception $e) {
            return $period;
        }
    }

    /**
     * Format category label for display
     *
     * Formats category values into human-readable labels.
     *
     * @param string $category Raw category value
     * @return string Formatted label for display
     * @access protected
     * @since  1.2.0
     */
    protected function format_category_label(string $category)
    {
        if (empty($category)) {
            return __('Unknown', 'ultraguard-security-suite');
        }

        return ucwords(str_replace(['_', '-'], ' ', $category));
    }

    /**
     * Build aggregation SQL
     *
     * Builds safe SQL aggregation expression for given function and field.
     *
     * @param string $function Aggregate function
     * @param string $field    Field to aggregate
     * @return string SQL aggregation expression
     * @access protected
     * @since  1.2.0
     */
    protected function build_aggregation_sql(string $function, string $field)
    {
        $function = $this->sanitize_aggregate_function($function);

        if ($function === 'COUNT' && $field === '*') {
            return 'COUNT(*)';
        }

        $field = sanitize_key($field);
        return "{$function}({$field})";
    }

    /**
     * Get color palette for charts
     *
     * Returns an array of hex color codes for use in chart visualizations.
     *
     * @return array Hex color codes
     * @access protected
     * @since  1.2.0
     */
    protected function get_color_palette()
    {
        return [
            '#4CAF50', '#2196F3', '#FF9800', '#F44336', '#9C27B0',
            '#00BCD4', '#FFEB3B', '#795548', '#607D8B', '#E91E63',
        ];
    }

    // ============================================================
    // HOOK METHODS (Can be overridden by child classes)
    // ============================================================

    /**
     * Hook called before inserting a record
     *
     * Child classes can override this method to modify data before insertion.
     * Filters are also applied for external modification.
     *
     * @param array $data Record data about to be inserted
     * @return array Modified data
     * @access protected
     * @since  1.2.0
     */
    protected function before_insert($data)
    {
        return apply_filters("ultraguard_{$this->table_name}_before_insert", $data);
    }

    /**
     * Hook called after inserting a record
     *
     * Child classes can override this method to perform actions after insertion.
     *
     * @param int   $id   Inserted record ID
     * @param array $data Record data that was inserted
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function after_insert($id, $data)
    {
        do_action("ultraguard_{$this->table_name}_after_insert", $id, $data);
    }

    /**
     * Hook called after bulk insert
     *
     * Child classes can override this method to perform actions after bulk insertion.
     *
     * @param array $records Array of records that were inserted
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function after_bulk_insert($records)
    {
        do_action("ultraguard_{$this->table_name}_after_bulk_insert", $records);
    }

    /**
     * Hook called before updating a record
     *
     * Child classes can override this method to modify data before update.
     * Filters are also applied for external modification.
     *
     * @param int   $id   Record ID being updated
     * @param array $data Update data
     * @return array Modified data
     * @access protected
     * @since  1.2.0
     */
    protected function before_update($id, $data)
    {
        return apply_filters("ultraguard_{$this->table_name}_before_update", $data, $id);
    }

    /**
     * Hook called after updating a record
     *
     * Child classes can override this method to perform actions after update.
     *
     * @param int   $id   Updated record ID
     * @param array $data Update data that was applied
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function after_update($id, $data)
    {
        do_action("ultraguard_{$this->table_name}_after_update", $id, $data);
    }

    /**
     * Hook called before deleting a record
     *
     * Child classes can override this method to perform actions before deletion.
     *
     * @param int $id Record ID being deleted
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function before_delete($id)
    {
        do_action("ultraguard_{$this->table_name}_before_delete", $id);
    }

    /**
     * Hook called after deleting a record
     *
     * Child classes can override this method to perform actions after deletion.
     *
     * @param int $id Deleted record ID
     * @return void
     * @access protected
     * @since  1.2.0
     */
    protected function after_delete($id)
    {
        do_action("ultraguard_{$this->table_name}_after_delete", $id);
    }

    // ============================================================
    // MISSING METHODS (Referenced in original code)
    // ============================================================

    /**
     * Build WHERE clause from conditions array
     *
     * Converts conditions array into SQL WHERE clause with proper escaping.
     *
     * @param array $conditions Where conditions as associative array
     * @return string SQL WHERE clause
     * @access protected
     * @since  1.2.0
     */
    protected function build_where_clause(array $conditions)
    {
        $conditions = $this->sanitize_conditions($conditions);

        if (empty($conditions)) {
            return '';
        }

        global $wpdb;
        $where_parts = [];
        $where_values = [];

        $allowed_operators = [ '=', '!=', '<>', '<', '>', '<=', '>=', 'LIKE', 'NOT LIKE' ];

        foreach ($conditions as $field => $value) {
            // Support "field OPERATOR" keys such as 'created_at >=' or 'status !='
            if ( preg_match( '/^([a-z0-9_]+)\s+(.+)$/i', (string) $field, $m ) ) {
                $col = sanitize_key( $m[1] );
                $op  = strtoupper( trim( $m[2] ) );
                if ( ! in_array( $op, $allowed_operators, true ) ) {
                    $op = '=';
                }
            } else {
                $col = sanitize_key( (string) $field );
                $op  = '=';
            }

            if ( is_array($value) && !empty($value) ) {
                // Force IN clause regardless of operator when value is an array
                $placeholders  = implode( ',', array_fill( 0, count($value), '%s' ) );
                $where_parts[] = "`{$col}` IN ({$placeholders})";
                $where_values  = array_merge( $where_values, $value );
            } else {
                $where_parts[] = "`{$col}` {$op} %s";
                $where_values[] = $value;
            }
        }

        if (empty($where_parts)) {
            return '';
        }

        $where_sql = 'WHERE ' . implode(' AND ', $where_parts);

        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- WHERE fragment is prepared independently before final query assembly.
            $where_sql = $wpdb->prepare($where_sql, $where_values);
        }

        return $where_sql;
    }

    /**
     * Build custom WHERE clause with operators
     *
     * Builds WHERE clause with support for advanced operators (IN, NOT IN, BETWEEN, etc.).
     *
     * @param array $conditions Conditions array with operator support
     * @return string WHERE clause
     * @access protected
     * @since  1.2.0
     */
    protected function build_advanced_where(array $conditions)
    {
        $conditions = $this->sanitize_conditions($conditions);

        if (empty($conditions)) {
            return '';
        }

        global $wpdb;
        $where_parts = [];
        $where_values = [];

        foreach ($conditions as $key => $condition) {
            $key = sanitize_key($key);

            if (is_array($condition) && isset($condition['operator'])) {
                $operator = strtoupper($condition['operator']);

                switch ($operator) {
                    case 'IN':
                    case 'NOT IN':
                        if (isset($condition['value']) && is_array($condition['value']) && !empty($condition['value'])) {
                            $placeholders = implode(',', array_fill(0, count($condition['value']), '%s'));
                            $where_parts[] = "{$key} {$operator} ({$placeholders})";
                            $where_values = array_merge($where_values, $condition['value']);
                        }
                        break;

                    case 'BETWEEN':
                        if (isset($condition['value']) && is_array($condition['value']) && count($condition['value']) === 2) {
                            $where_parts[] = "{$key} BETWEEN %s AND %s";
                            $where_values[] = $condition['value'][0];
                            $where_values[] = $condition['value'][1];
                        }
                        break;

                    case 'IS NULL':
                        $where_parts[] = "{$key} IS NULL";
                        break;

                    case 'IS NOT NULL':
                        $where_parts[] = "{$key} IS NOT NULL";
                        break;

                    case 'LIKE':
                    case 'NOT LIKE':
                        if (isset($condition['value'])) {
                            $where_parts[] = "{$key} {$operator} %s";
                            $where_values[] = $condition['value'];
                        }
                        break;

                    default:
                        if (isset($condition['value'])) {
                            $where_parts[] = "{$key} {$operator} %s";
                            $where_values[] = $condition['value'];
                        }
                }
            }
            elseif (is_array($condition) && !empty($condition)) {
                $placeholders = implode(',', array_fill(0, count($condition), '%s'));
                $where_parts[] = "{$key} IN ({$placeholders})";
                $where_values = array_merge($where_values, $condition);
            }
            else {
                $where_parts[] = "{$key} = %s";
                $where_values[] = $condition;
            }
        }

        if (empty($where_parts)) {
            return '';
        }

        $where_sql = 'WHERE ' . implode(' AND ', $where_parts);

        if (!empty($where_values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- WHERE fragment is prepared independently before final query assembly.
            $where_sql = $wpdb->prepare($where_sql, $where_values);
        }

        return $where_sql;
    }

    /**
     * Custom group by for non-COUNT aggregations
     *
     * Performs custom GROUP BY operations for aggregate functions other than COUNT.
     *
     * @param string $group_field  Field to group by
     * @param string $agg_function Aggregate function (SUM, AVG, MAX, MIN)
     * @param string $agg_field    Field to aggregate
     * @param array  $conditions   Where conditions
     * @param int    $limit        Result limit
     * @return array Results with group field and aggregate value
     * @access protected
     * @since  1.2.0
     */
    protected function custom_group_by(string $group_field, string $agg_function, string $agg_field,
                                      array $conditions, int $limit)
    {
        global $wpdb;
        $table_name = DatabaseSchema::get_table_name($this->table_name);

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

        $group_field = sanitize_key($group_field);
        $agg_function = $this->sanitize_aggregate_function($agg_function);
        $agg_field = sanitize_key($agg_field);

        $where_clause = '';
        $where_values = [];

        if (!empty($conditions)) {
            $where_parts = [];
            foreach ($conditions as $field => $value) {
                $field = sanitize_key($field);

                if (is_array($value) && !empty($value)) {
                    $placeholders = implode(',', array_fill(0, count($value), '%s'));
                    $where_parts[] = "{$field} IN ({$placeholders})";
                    $where_values = array_merge($where_values, $value);
                } else {
                    $where_parts[] = "{$field} = %s";
                    $where_values[] = $value;
                }
            }
            if (!empty($where_parts)) {
                $where_clause = 'WHERE ' . implode(' AND ', $where_parts);
            }
        }

        $query = "SELECT {$group_field}, {$agg_function}({$agg_field}) as aggregate_value
                 FROM {$table_name}
                 {$where_clause}
                 GROUP BY {$group_field}
                 ORDER BY aggregate_value DESC
                 LIMIT %d";

        $where_values[] = $limit;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$group_field}, {$agg_function}({$agg_field}) as aggregate_value
                 FROM {$table_name}
                 {$where_clause}
                 GROUP BY {$group_field}
                 ORDER BY aggregate_value DESC
                 LIMIT %d",
                $where_values
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.
        return is_array($results) ? $results : [];
    }

    /**
     * Group by field with aggregation and caching
     *
     * Groups records by a field with aggregation and caching support.
     *
     * @param string $group_field  Field to group by
     * @param string $agg_function Aggregate function (default: 'COUNT')
     * @param string $agg_field    Field to aggregate (default: '*')
     * @param array  $conditions   Where conditions (default: [])
     * @param int    $limit        Result limit (default: 100)
     * @param bool   $use_cache    Whether to use cache (default: true)
     * @return array Grouped results
     * @access public
     * @since  1.2.0
     */
    public function group_by(string $group_field, string $agg_function = 'COUNT', string $agg_field = '*',
                            array $conditions = [], int $limit = 100, bool $use_cache = true)
    {
        $group_field = sanitize_key($group_field);
        $agg_function = $this->sanitize_aggregate_function($agg_function);
        $agg_field = sanitize_key($agg_field);
        $limit = max(1, $limit);

        $cache_key = $this->get_cache_key('group', [$group_field, $agg_function, $agg_field, $conditions, $limit]);

        return $this->get_cached_data($cache_key, function() use ($group_field, $agg_function, $agg_field, $conditions, $limit) {
            return $this->get_grouped_data($group_field, $agg_function, $agg_field, $conditions, $limit, false);
        }, $this->short_cache_duration, $use_cache);
    }

    // ============================================================
    // ADDITIONAL METHODS (For completeness)
    // ============================================================

    /**
     * Get last database error
     *
     * Retrieves the last error message from DatabaseCrud operations.
     *
     * @return string Last error message or empty string if none
     * @access public
     * @since  1.2.0
     */
    public function get_last_error()
    {
        return DatabaseCrud::get_last_error();
    }

    /**
     * Get all database errors
     *
     * Retrieves all accumulated error messages from DatabaseCrud operations.
     *
     * @return array Array of error messages
     * @access public
     * @since  1.2.0
     */
    public function get_errors()
    {
        return DatabaseCrud::get_errors();
    }

    /**
     * Clear database errors
     *
     * Clears all accumulated error messages in DatabaseCrud.
     *
     * @return void
     * @access public
     * @since  1.2.0
     */
    public function clear_errors()
    {
        DatabaseCrud::clear_errors();
    }

    /**
     * Check if last operation was successful
     *
     * Determines if the last DatabaseCrud operation was successful.
     *
     * @return bool True if last operation succeeded, false otherwise
     * @access public
     * @since  1.2.0
     */
    public function is_success()
    {
        return DatabaseCrud::is_success();
    }

    /**
     * Get single row by custom field with caching
     *
     * Retrieves a single record by a specific field value with caching.
     *
     * @param string $field       Field name to search by
     * @param mixed  $value       Field value to match
     * @param string $output_type Output type: ARRAY_A or OBJECT (default: ARRAY_A)
     * @param bool   $use_cache   Whether to use cache (default: true)
     * @return array|null Record data or null if not found
     * @access public
     * @since  1.2.0
     */
    public function get_by_field(string $field, $value, string $output_type = ARRAY_A, bool $use_cache = true)
    {
        $field = sanitize_key($field);
        $output_type = in_array($output_type, [ARRAY_A, OBJECT]) ? $output_type : ARRAY_A;

        $cache_key = $this->get_cache_key('by_field', [$field, $value, $output_type]);

        return $this->get_cached_data($cache_key, function() use ($field, $value, $output_type) {
            $record = DatabaseCrud::get_by_field($this->table_name, $field, $value, $output_type);

            if ($record) {
                $record = is_array($record) ? $record : (array) $record;
                $record = $this->codec_decode( $record );
                $record = wp_parse_args($record, $this->get_defaults());
            }

            return $record;
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get single row with conditions with caching
     *
     * Retrieves the first record matching given conditions with caching.
     *
     * @param array  $conditions Where conditions (default: [])
     * @param string $output_type Output type: ARRAY_A or OBJECT (default: ARRAY_A)
     * @param bool   $use_cache   Whether to use cache (default: true)
     * @return array|null First matching record or null if none found
     * @access public
     * @since  1.2.0
     */
    public function get_row(array $conditions = [], string $output_type = ARRAY_A, bool $use_cache = true)
    {
        $output_type = in_array($output_type, [ARRAY_A, OBJECT]) ? $output_type : ARRAY_A;

        $cache_key = $this->get_cache_key('row', [$conditions, $output_type]);

        return $this->get_cached_data($cache_key, function() use ($conditions, $output_type) {
            $record = DatabaseCrud::get_row($this->table_name, $this->sanitize_conditions($conditions), $output_type);

            if ($record) {
                $record = is_array($record) ? $record : (array) $record;
                $record = $this->codec_decode( $record );
                $record = wp_parse_args($record, $this->get_defaults());
            }

            return $record;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get records by IDs with caching
     *
     * Retrieves multiple records by their IDs with caching.
     *
     * @param array $ids       Array of record IDs
     * @param bool  $use_cache Whether to use cache (default: true)
     * @return array Records keyed by ID (may not preserve input order)
     * @access public
     * @since  1.2.0
     */
    public function get_by_ids(array $ids, bool $use_cache = true)
    {
        $ids = array_filter(array_map('absint', $ids));

        if (empty($ids)) {
            return [];
        }

        sort($ids);
        $cache_key = $this->get_cache_key('by_ids', $ids);

        return $this->get_cached_data($cache_key, function() use ($ids) {
            return $this->get_by(['id' => $ids], count($ids), 'id', 'ASC', 0, false);
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get records created/updated within date range with caching
     *
     * Retrieves records within a specified date range with caching.
     *
     * @param string $start_date           Start date (Y-m-d H:i:s format)
     * @param string $end_date             End date (Y-m-d H:i:s format)
     * @param array  $additional_conditions Additional where conditions (default: [])
     * @param string $date_field           Date field to check (default: 'created_at')
     * @param bool   $use_cache            Whether to use cache (default: true)
     * @return array Records within date range
     * @access public
     * @since  1.2.0
     */
    public function get_by_date_range(string $start_date, string $end_date, array $additional_conditions = [],
                                     string $date_field = 'created_at', bool $use_cache = true)
    {
        $start_date = sanitize_text_field($start_date);
        $end_date = sanitize_text_field($end_date);
        $date_field = sanitize_text_field($date_field) ?: 'created_at';
        $additional_conditions = $this->sanitize_conditions($additional_conditions);

        $cache_key = $this->get_cache_key('date_range', [$start_date, $end_date, $additional_conditions, $date_field]);

        return $this->get_cached_data($cache_key, function() use ($start_date, $end_date, $additional_conditions, $date_field) {
            $conditions = array_merge($additional_conditions, [
                $date_field . ' >=' => $start_date,
                $date_field . ' <=' => $end_date
            ]);

            return $this->get_by($conditions, 999999, $date_field, 'DESC', 0, false);
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get records created/updated today with caching
     *
     * Retrieves records created or updated today with caching.
     *
     * @param string $date_field            Date field to check (default: 'created_at')
     * @param array  $additional_conditions Additional where conditions (default: [])
     * @param bool   $use_cache             Whether to use cache (default: true)
     * @return array Today's records
     * @access public
     * @since  1.2.0
     */
    public function get_today(string $date_field = 'created_at', array $additional_conditions = [], bool $use_cache = true)
    {
        $today_start = gmdate('Y-m-d 00:00:00');
        $today_end   = gmdate('Y-m-d 23:59:59');

        return $this->get_by_date_range($today_start, $today_end, $additional_conditions, $date_field, $use_cache);
    }

    /**
     * Get recent records with caching
     *
     * Retrieves the most recent records with caching.
     *
     * @param int    $limit      Number of recent records (default: 50)
     * @param string $date_field Date field to sort by (default: 'created_at')
     * @param array  $conditions Additional where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Most recent records
     * @access public
     * @since  1.2.0
     */
    public function get_recent(int $limit = 50, string $date_field = 'created_at', array $conditions = [], bool $use_cache = true)
    {
        $limit = absint($limit);
        $date_field = sanitize_text_field($date_field) ?: 'created_at';
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('recent', [$limit, $date_field, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($limit, $date_field, $conditions) {
            return $this->get_by($conditions, $limit, $date_field, 'DESC', 0, false);
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get oldest records with caching
     *
     * Retrieves the oldest records with caching.
     *
     * @param int    $limit      Number of oldest records (default: 50)
     * @param string $date_field Date field to sort by (default: 'created_at')
     * @param array  $conditions Additional where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Oldest records
     * @access public
     * @since  1.2.0
     */
    public function get_oldest(int $limit = 50, string $date_field = 'created_at', array $conditions = [], bool $use_cache = true)
    {
        $limit = absint($limit);
        $date_field = sanitize_text_field($date_field) ?: 'created_at';
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('oldest', [$limit, $date_field, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($limit, $date_field, $conditions) {
            return $this->get_by($conditions, $limit, $date_field, 'ASC', 0, false);
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Count unique/distinct values in a field with caching
     *
     * Counts the number of distinct values in a field for matching records.
     *
     * @param string $field      Field name to count distinct values
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return int Number of unique/distinct values
     * @access public
     * @since  1.2.0
     *
     * @example
     * $unique_users = $table->count_unique('user_id');
     * $unique_scans = $table->count_unique('scan_id', ['status' => 'completed']);
     */
    public function count_unique(string $field, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_key($field);
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('count_unique', [$field, $conditions]);

        return (int) $this->get_cached_data($cache_key, function() use ($field, $conditions) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

            if (!$table_name) {
                return 0;
            }

            $where_sql = $this->build_where_clause($conditions);

            $query = "SELECT COUNT(DISTINCT {$field})
                      FROM {$table_name}
                      {$where_sql}";

            return (int) $wpdb->get_var($query);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
        }, $this->short_cache_duration, $use_cache);
    }
    /**
     * Get distinct values for a field with caching
     *
     * Retrieves unique/distinct values for a field with caching.
     *
     * @param string $field      Field name
     * @param array  $conditions Where conditions (default: [])
     * @param int    $limit      Maximum number of distinct values (default: 100)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Distinct values
     * @access public
     * @since  1.2.0
     */
    public function get_distinct(string $field, array $conditions = [], int $limit = 100, bool $use_cache = true)
    {
        $field = sanitize_key($field);
        $conditions = $this->sanitize_conditions($conditions);
        $limit = absint($limit);

        $cache_key = $this->get_cache_key('distinct', [$field, $conditions, $limit]);

        return $this->get_cached_data($cache_key, function() use ($field, $conditions, $limit) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

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

            $where_sql = $this->build_where_clause($conditions);

            $query = "SELECT DISTINCT {$field}
                      FROM {$table_name}
                      {$where_sql}
                      ORDER BY {$field} ASC
                      LIMIT {$limit}";

            $results = $wpdb->get_col($query);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built from validated identifiers and cached query parts only.
            return $results ? $results : [];
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get random records with caching
     *
     * Retrieves random records from the table with caching.
     *
     * @param int   $limit      Number of random records (default: 10)
     * @param array $conditions Where conditions (default: [])
     * @param bool  $use_cache  Whether to use cache (default: true)
     * @return array Random records
     * @access public
     * @since  1.2.0
     */
    public function get_random(int $limit = 10, array $conditions = [], bool $use_cache = true)
    {
        $limit = absint($limit);
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('random', [$limit, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($limit, $conditions) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

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

            $where_sql = $this->build_where_clause($conditions);

            $query = "SELECT * FROM {$table_name} {$where_sql} ORDER BY RAND() LIMIT {$limit}";

            $results = $wpdb->get_results($query, ARRAY_A);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()

            if (!empty($results)) {
                foreach ($results as $key => $result) {
                    $results[$key] = wp_parse_args($result, $this->get_defaults());
                }
            }

            return $results ? $results : [];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get daily counts for a date field with caching
     *
     * Retrieves daily record counts for date-based analysis with caching.
     *
     * @param string $date_field Date field to analyze (default: 'created_at')
     * @param array  $conditions Where conditions (default: [])
     * @param int    $days       Number of days to retrieve (default: 30)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Daily counts keyed by date
     * @access public
     * @since  1.2.0
     */
    public function get_daily_count(string $date_field = 'created_at', array $conditions = [], int $days = 30, bool $use_cache = true)
    {
        $date_field = sanitize_text_field($date_field) ?: 'created_at';
        $conditions = $this->sanitize_conditions($conditions);
        $days = absint($days);

        $cache_key = $this->get_cache_key('daily_count', [$date_field, $conditions, $days]);

        return $this->get_cached_data($cache_key, function() use ($date_field, $conditions, $days) {
            $daily_counts = DatabaseCrud::get_daily_count($this->table_name, $date_field, $conditions);
            $daily_counts = is_array($daily_counts) ? $daily_counts : [];

            if (!empty($daily_counts) && count($daily_counts) > $days) {
                $daily_counts = array_slice($daily_counts, 0, $days);
            }

            return $daily_counts;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get grouped count by field with caching
     *
     * Retrieves counts grouped by a field with caching.
     *
     * @param string $field      Field to group by
     * @param array  $conditions Where conditions (default: [])
     * @param int    $limit      Maximum number of groups (default: 10)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Grouped counts
     * @access public
     * @since  1.2.0
     */
    public function get_grouped_count(string $field, array $conditions = [], int $limit = 10, bool $use_cache = true)
    {
        $field = sanitize_text_field($field);
        $conditions = $this->sanitize_conditions($conditions);
        $limit = absint($limit);

        $cache_key = $this->get_cache_key('grouped_count', [$field, $conditions, $limit]);

        return $this->get_cached_data($cache_key, function() use ($field, $conditions, $limit) {
            $grouped = DatabaseCrud::get_grouped_count($this->table_name, $field, $conditions, $limit);
            return is_array($grouped) ? $grouped : [];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get table statistics with caching
     *
     * Retrieves comprehensive table statistics including row count,
     * table size, and date ranges with caching.
     *
     * @param bool $use_cache Whether to use cache (default: true)
     * @return array Table statistics including:
     *               - total_records: Total number of records
     *               - table_size_mb: Table size in megabytes
     *               - table_rows: Approximate row count
     *               - oldest_record: Oldest record date
     *               - newest_record: Newest record date
     * @access public
     * @since  1.2.0
     */
    public function get_table_stats(bool $use_cache = true)
    {
        $cache_key = $this->get_cache_key('stats', 'table_overview');

        return $this->get_cached_data($cache_key, function() {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

            $size_info = $wpdb->get_row($wpdb->prepare("  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); interpolated table name comes from validated schema metadata.
                SELECT
                    table_name AS `table`,
                    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `size_mb`,
                    table_rows AS `rows`
                FROM information_schema.TABLES
                WHERE table_schema = DATABASE()
                AND table_name = %s
            ", $table_name));

            return [
                'total_records' => $this->count([], 'id', false),
                'table_size_mb' => $size_info ? (float) $size_info->size_mb : 0.0,
                'table_rows' => $size_info ? (int) $size_info->rows : 0,
                'oldest_record' => $this->min('created_at', [], false),
                'newest_record' => $this->max('created_at', [], false),
            ];
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get records that match multiple values for a field (IN clause) with caching
     *
     * Retrieves records where a field matches any of the specified values.
     *
     * @param string $field      Field name
     * @param array  $values     Array of values to match
     * @param int    $limit      Maximum records (default: 100)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Matching records
     * @access public
     * @since  1.2.0
     */
    public function get_where_in(string $field, array $values, int $limit = 100, bool $use_cache = true)
    {
        $field = sanitize_text_field($field);
        $values = array_map([$this, 'sanitize_condition_value'], $values);
        $limit = absint($limit);

        if (empty($values)) {
            return [];
        }

        sort($values);
        $cache_key = $this->get_cache_key('where_in', [$field, $values, $limit]);

        return $this->get_cached_data($cache_key, function() use ($field, $values, $limit) {
            return $this->get_by([$field => $values], $limit, 'id', 'ASC', 0, false);
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get records that don't match values for a field (NOT IN clause) with caching
     *
     * Retrieves records where a field does not match any of the specified values.
     *
     * @param string $field      Field name
     * @param array  $values     Array of values to exclude
     * @param int    $limit      Maximum records (default: 100)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Matching records
     * @access public
     * @since  1.2.0
     */
    public function get_where_not_in(string $field, array $values, int $limit = 100, bool $use_cache = true)
    {
        $field = sanitize_key($field);
        $values = array_map([$this, 'sanitize_condition_value'], $values);
        $limit = absint($limit);

        if (empty($values)) {
            return $this->get_by([], $limit, 'id', 'ASC', 0, false);
        }

        sort($values);
        $cache_key = $this->get_cache_key('where_not_in', [$field, $values, $limit]);

        return $this->get_cached_data($cache_key, function() use ($field, $values, $limit) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

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

            $placeholders = implode(',', array_fill(0, count($values), '%s'));

            $query = $wpdb->prepare(
                "SELECT * FROM {$table_name} WHERE {$field} NOT IN ({$placeholders}) LIMIT %d",
                array_merge($values, [$limit])
            );

            $results = $wpdb->get_results($query, ARRAY_A);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()

            if (!empty($results)) {
                foreach ($results as $key => $result) {
                    $results[$key] = wp_parse_args($result, $this->get_defaults());
                }
            }

            return $results ? $results : [];
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get first record matching conditions with caching
     *
     * Retrieves the first record matching conditions with specified ordering.
     *
     * @param array  $conditions Where conditions (default: [])
     * @param string $orderby    Order by field (default: 'id')
     * @param string $order      Order direction (default: 'ASC')
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array|null First matching record or null
     * @access public
     * @since  1.2.0
     */
    public function get_first(array $conditions = [], string $orderby = 'id', string $order = 'ASC', bool $use_cache = true)
    {
        $conditions = $this->sanitize_conditions($conditions);
        $orderby = $this->sanitize_orderby_field($orderby);
        $order = $this->sanitize_order_direction($order);

        $cache_key = $this->get_cache_key('first', [$conditions, $orderby, $order]);

        return $this->get_cached_data($cache_key, function() use ($conditions, $orderby, $order) {
            $results = $this->get_by($conditions, 1, $orderby, $order, 0, false);
            return !empty($results) ? $results[0] : null;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get last record matching conditions with caching
     *
     * Retrieves the last record matching conditions (ordered by specified field descending).
     *
     * @param array  $conditions Where conditions (default: [])
     * @param string $orderby    Order by field (default: 'id')
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array|null Last matching record or null
     * @access public
     * @since  1.2.0
     */
    public function get_last(array $conditions = [], string $orderby = 'id', bool $use_cache = true)
    {
        $conditions = $this->sanitize_conditions($conditions);
        $orderby = $this->sanitize_orderby_field($orderby);

        $cache_key = $this->get_cache_key('last', [$conditions, $orderby]);

        return $this->get_cached_data($cache_key, function() use ($conditions, $orderby) {
            $results = $this->get_by($conditions, 1, $orderby, 'DESC', 0, false);
            return !empty($results) ? $results[0] : null;
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Export records to CSV with caching
     *
     * Exports records to CSV format with field selection and caching.
     *
     * @param array  $conditions Where conditions (default: [])
     * @param array  $fields     Fields to export (empty = all fields)
     * @param int    $limit      Maximum records to export (default: 10000)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return string CSV data as string
     * @access public
     * @since  1.2.0
     */
    public function export_to_csv(array $conditions = [], array $fields = [], int $limit = 10000, bool $use_cache = true)
    {
        $conditions = $this->sanitize_conditions($conditions);
        $fields = array_map('sanitize_text_field', $fields);
        $limit = absint($limit);

        $cache_key = $this->get_cache_key('export_csv', [$conditions, $fields, $limit]);

        return $this->get_cached_data($cache_key, function() use ($conditions, $fields, $limit) {
            $records = $this->get_by($conditions, $limit, 'id', 'ASC', 0, false);

            if (empty($records)) {
                return '';
            }

            if (empty($fields)) {
                $fields = array_keys($records[0]);
            }

            $csv = [];
            $csv[] = implode(',', array_map(function($field) {
                return '"' . str_replace('"', '""', $field) . '"';
            }, $fields));

            foreach ($records as $record) {
                $row = [];
                foreach ($fields as $field) {
                    $value = isset($record[$field]) ? (string) $record[$field] : '';
                    $row[] = '"' . str_replace('"', '""', $value) . '"';
                }
                $csv[] = implode(',', $row);
            }

            return implode("\n", $csv);
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Get field value by ID with caching
     *
     * Retrieves a specific field value for a record by ID with caching.
     *
     * @param int    $id        Record ID
     * @param string $field     Field name
     * @param bool   $use_cache Whether to use cache (default: true)
     * @return mixed Field value or null if not found
     * @access public
     * @since  1.2.0
     */
    public function get_field_value(int $id, string $field, bool $use_cache = true)
    {
        $id = absint($id);
        $field = sanitize_text_field($field);

        $cache_key = $this->get_cache_key('field_value', [$id, $field]);

        return $this->get_cached_data($cache_key, function() use ($id, $field) {
            $record = $this->get($id, false);
            return isset($record[$field]) ? $record[$field] : null;
        }, $this->cache_duration, $use_cache);
    }

    /**
     * Check if any record exists matching conditions with caching
     *
     * Alias for exists() method for semantic clarity.
     *
     * @param array $conditions Where conditions
     * @param bool  $use_cache  Whether to use cache (default: true)
     * @return bool True if at least one matching record exists
     * @access public
     * @since  1.2.0
     * @see    exists()
     */
    public function has_any(array $conditions, bool $use_cache = true)
    {
        return $this->exists($conditions, $use_cache);
    }

    /**
     * Check if field value is unique with caching
     *
     * Checks if a field value is unique across the table (optionally excluding a specific ID).
     *
     * @param string $field      Field name to check
     * @param mixed  $value      Value to check for uniqueness
     * @param int    $exclude_id Record ID to exclude from check (default: 0 = don't exclude)
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return bool True if value is unique (or doesn't exist)
     * @access public
     * @since  1.2.0
     */
    public function is_unique(string $field, $value, int $exclude_id = 0, bool $use_cache = true)
    {
        $field = sanitize_text_field($field);
        $exclude_id = absint($exclude_id);

        $conditions = [$field => $value];

        if ($exclude_id > 0) {
            $conditions['id !='] = $exclude_id;
        }

        $cache_key = $this->get_cache_key('is_unique', [$field, $value, $exclude_id]);

        return $this->get_cached_data($cache_key, function() use ($conditions) {
            return !$this->exists($conditions, false);
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Increment field value
     *
     * Atomically increments a numeric field value for matching records.
     *
     * @param string $field      Field name to increment
     * @param array  $where      Where conditions (default: [])
     * @param int    $increment  Amount to increment by (default: 1)
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     */
    public function increment_field(string $field, array $where = [], int $increment = 1)
    {
        $field = sanitize_text_field($field);
        $where = $this->sanitize_conditions($where);
        $increment = intval($increment);

        $result = DatabaseCrud::increment_field($this->table_name, $field, $where, $increment);

        if ($result) {
            $this->invalidate_caches_after_bulk();
        }

        return (bool) $result;
    }

    /**
     * Decrement field value
     *
     * Atomically decrements a numeric field value for matching records.
     *
     * @param string $field      Field name to decrement
     * @param array  $where      Where conditions (default: [])
     * @param int    $decrement  Amount to decrement by (default: 1)
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     */
    public function decrement_field(string $field, array $where = [], int $decrement = 1)
    {
        $field = sanitize_text_field($field);
        $where = $this->sanitize_conditions($where);
        $decrement = intval($decrement);

        $result = DatabaseCrud::increment_field($this->table_name, $field, $where, -abs($decrement));

        if ($result) {
            $this->invalidate_caches_after_bulk();
        }

        return (bool) $result;
    }

    /**
     * Delete old records older than specified days
     *
     * Deletes records older than a specified number of days.
     *
     * @param int    $days                    Delete records older than N days (default: 90)
     * @param string $date_field              Date field to check (default: 'created_at')
     * @param array  $additional_conditions   Additional where conditions (default: [])
     * @return int Number of deleted rows
     * @access public
     * @since  1.2.0
     */
    public function delete_older_than(int $days = 90, string $date_field = 'created_at', array $additional_conditions = [])
    {
        $days = absint($days);
        $date_field = sanitize_text_field($date_field) ?: 'created_at';
        $additional_conditions = $this->sanitize_conditions($additional_conditions);

        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        $conditions = array_merge($additional_conditions, [
            $date_field . ' <' => $cutoff_date
        ]);

        return $this->delete_where($conditions);
    }

    /**
     * Soft delete record (if table has status/is_deleted field)
     *
     * Performs a soft delete by updating a status field instead of hard deletion.
     *
     * @param int    $id           Record ID to soft delete
     * @param string $status_field Status field name (default: 'status')
     * @param string $status_value Status value for deleted records (default: 'deleted')
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     * @see    restore()
     */
    public function soft_delete(int $id, string $status_field = 'status', string $status_value = 'deleted')
    {
        $id = absint($id);
        $status_field = sanitize_text_field($status_field) ?: 'status';
        $status_value = sanitize_text_field($status_value) ?: 'deleted';

        return $this->update($id, [$status_field => $status_value]);
    }

    /**
     * Restore soft deleted record
     *
     * Restores a previously soft-deleted record by updating its status field.
     *
     * @param int    $id           Record ID to restore
     * @param string $status_field Status field name (default: 'status')
     * @param string $status_value Status value for active records (default: 'active')
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     * @see    soft_delete()
     */
    public function restore($id, $status_field = 'status', $status_value = 'active')
    {
        $id = absint($id);
        $status_field = sanitize_text_field($status_field) ?: 'status';
        $status_value = sanitize_text_field($status_value) ?: 'active';

        return $this->update($id, [$status_field => $status_value]);
    }

    /**
     * Toggle boolean field value
     *
     * Toggles a boolean field value (0/1, true/false) for a record.
     *
     * @param int    $id    Record ID
     * @param string $field Boolean field name
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     */
    public function toggle_boolean(int $id, string $field)
    {
        $id = absint($id);
        $field = sanitize_text_field($field);

        $record = $this->get($id);
        if (!$record) {
            return false;
        }

        $new_value = isset($record[$field]) && $record[$field] ? 0 : 1;
        return $this->update($id, [$field => $new_value]);
    }

    /**
     * Duplicate a record
     *
     * Creates a copy of an existing record with optional data overrides.
     *
     * @param int   $id            Record ID to duplicate
     * @param array $override_data Data values to override in the duplicate (default: [])
     * @return int|false New record ID or false on failure
     * @access public
     * @since  1.2.0
     */
    public function duplicate(int $id, array $override_data = [])
    {
        $id = absint($id);
        $override_data = $this->sanitize_data($override_data);

        $record = $this->get($id);
        if (!$record) {
            return false;
        }

        unset($record['id']);
        unset($record['created_at']);
        unset($record['updated_at']);

        $record = array_merge($record, $override_data);

        return $this->insert($record);
    }

    /**
     * Update single field value
     *
     * Updates a single field value for a record.
     *
     * @param int    $id    Record ID
     * @param string $field Field name
     * @param mixed  $value New field value
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     */
    public function update_field(int $id, string $field, $value)
    {
        $id = absint($id);
        $field = sanitize_text_field($field);

        return $this->update($id, [$field => $value]);
    }

    /**
     * Batch update multiple records with different data
     *
     * Updates multiple records with different data in a single transaction.
     *
     * @param array $updates Array of update data arrays, each must contain 'id'
     * @return bool True if all updates succeeded, false otherwise
     * @access public
     * @since  1.2.0
     */
    public function batch_update(array $updates)
    {
        $updates = $this->sanitize_batch_updates($updates);

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

        global $wpdb;
        $wpdb->query('START TRANSACTION');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input

        try {
            foreach ($updates as $update) {
                $update = (array) $update;

                if (!isset($update['id'])) {
                    throw new \Exception(esc_html( 'ID required for batch update' ));
                }

                $id = absint($update['id']);
                unset($update['id']);

                if (!$this->update($id, $update)) {
                    throw new \Exception(esc_html( 'Update failed for ID: ' . $id ));
                }
            }

            $wpdb->query('COMMIT');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
            return true;

        } catch (\Exception $e) {
            $wpdb->query('ROLLBACK');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
            $this->log('Batch update failed: ' . $e->getMessage(), 'error');
            return false;
        }
    }

    /**
     * Sanitize batch updates
     *
     * Internal method to sanitize batch update data.
     *
     * @param array $updates Batch updates array
     * @return array Sanitized updates
     * @access private
     * @since  1.2.0
     */
    private function sanitize_batch_updates(array $updates)
    {
        $sanitized = [];

        foreach ($updates as $update) {
            $update = (array) $update;

            if (isset($update['id'])) {
                $update['id'] = absint($update['id']);
            }

            // Sanitize other fields
            foreach ($update as $key => $value) {
                if ($key !== 'id') {
                    $update[$key] = $this->sanitize_condition_value($value);
                }
            }

            $sanitized[] = $update;
        }

        return $sanitized;
    }

    /**
     * Truncate table (delete all records)
     *
     * Deletes all records from the table and resets auto-increment counter.
     *
     * @return bool True on success, false on failure
     * @access public
     * @since  1.2.0
     * @warning This operation cannot be undone
     */
    public function truncate()
    {
        global $wpdb;
        $table_name = DatabaseSchema::get_table_name($this->table_name);

        if (!$table_name) {
            return false;
        }

        $result = $wpdb->query("TRUNCATE TABLE {$table_name}");  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input

        if ($result !== false) {
            $this->clear_all_caches();
            do_action("ultraguard_{$this->table_name}_truncated");
        }

        return $result !== false;
    }

    /**
     * Import records from CSV
     *
     * Imports records from CSV data with optional header row.
     *
     * @param string $csv_data   CSV data as string
     * @param bool   $has_header Whether CSV has header row (default: true)
     * @return array Import results including:
     *               - success: Overall success status
     *               - total: Total records processed
     *               - imported: Successfully imported records
     *               - skipped: Skipped records
     *               - errors: Array of error messages
     * @access public
     * @since  1.2.0
     */
    public function import_from_csv(string $csv_data, bool $has_header = true)
    {
        $csv_data = sanitize_textarea_field($csv_data);
        $has_header = (bool) $has_header;

        $lines = explode("\n", trim($csv_data));

        if (empty($lines)) {
            return ['success' => false, 'error' => 'Empty CSV data'];
        }

        $results = [
            'total' => 0,
            'imported' => 0,
            'skipped' => 0,
            'errors' => []
        ];

        $headers = null;
        $start_line = 0;

        if ($has_header) {
            $headers = str_getcsv($lines[0]);
            $start_line = 1;
        }

        for ($i = $start_line; $i < count($lines); $i++) {
            $results['total']++;

            $values = str_getcsv($lines[$i]);

            if (empty($values)) {
                $results['skipped']++;
                continue;
            }

            if ($headers) {
                if (count($headers) !== count($values)) {
                    $results['skipped']++;
                    $results['errors'][] = "Line " . ($i + 1) . ": Column count mismatch (expected " . count($headers) . ", got " . count($values) . ")";
                    continue;
                }
                $data = array_combine($headers, $values);
                if ($data === false) {
                    $results['skipped']++;
                    $results['errors'][] = "Line " . ($i + 1) . ": Could not map columns";
                    continue;
                }
            } else {
                $data = $values;
            }

            $id = $this->insert($data);

            if ($id) {
                $results['imported']++;
            } else {
                $results['skipped']++;
                $results['errors'][] = "Line " . ($i + 1) . ": Insert failed";
            }
        }

        $results['success'] = $results['imported'] > 0;
        return $results;
    }

    /**
     * Warm up cache for frequently accessed data
     *
     * Pre-loads cache for frequently accessed data to improve performance.
     *
     * @param array $ids Specific record IDs to warm cache for (empty = general warmup)
     * @return void
     * @access public
     * @since  1.2.0
     */
    public function warm_cache(array $ids = [])
    {
        $ids = array_filter(array_map('absint', $ids));

        if (!empty($ids)) {
            foreach ($ids as $id) {
                $this->get($id, true);
            }
        } else {
            $this->get_recent(20);
            $this->count();
        }
    }

    /**
     * Get records with eager loading (load all at once)
     *
     * Retrieves multiple records by ID and returns them keyed by ID.
     *
     * @param array $ids Record IDs to retrieve
     * @return array Records keyed by ID
     * @access public
     * @since  1.2.0
     */
    public function get_many(array $ids)
    {
        $ids = array_filter(array_map('absint', $ids));

        if (empty($ids)) {
            return [];
        }

        $records = $this->get_by_ids($ids);

        $keyed_records = [];
        foreach ($records as $record) {
            $keyed_records[$record['id']] = $record;
        }

        return $keyed_records;
    }

    /**
     * Chunk large dataset processing
     *
     * Processes large datasets in manageable chunks to prevent memory issues.
     *
     * @param callable $callback   Function to call for each chunk
     * @param int      $chunk_size Number of records per chunk (default: 100)
     * @param array    $conditions Where conditions (default: [])
     * @return bool True if all chunks processed successfully, false otherwise
     * @access public
     * @since  1.2.0
     */
    public function chunk(callable $callback, int $chunk_size = 100, array $conditions = [])
    {
        $chunk_size = absint($chunk_size);
        $conditions = $this->sanitize_conditions($conditions);

        if (!is_callable($callback)) {
            return false;
        }

        $offset = 0;

        do {
            $records = $this->get_by($conditions, $chunk_size, 'id', 'ASC', $offset, false);

            if (empty($records)) {
                break;
            }

            $result = call_user_func($callback, $records);

            if ($result === false) {
                break;
            }

            $offset += $chunk_size;

        } while (count($records) === $chunk_size);

        return true;
    }

    /**
     * Get records in batches (generator for memory efficiency)
     *
     * Returns a generator that yields records in batches for memory-efficient processing.
     *
     * @param int   $batch_size Batch size (default: 100)
     * @param array $conditions Where conditions (default: [])
     * @return \Generator Yields arrays of records
     * @access public
     * @since  1.2.0
     */
    public function batch(int $batch_size = 100, array $conditions = []): \Generator
    {
        $batch_size = absint($batch_size);
        $conditions = $this->sanitize_conditions($conditions);

        $offset = 0;

        while (true) {
            $records = $this->get_by($conditions, $batch_size, 'id', 'ASC', $offset, false);

            if (empty($records)) {
                break;
            }

            yield $records;

            if (count($records) < $batch_size) {
                break;
            }

            $offset += $batch_size;
        }
    }

    /**
     * Execute raw SQL query
     *
     * Executes a raw SQL query and returns results with defaults applied.
     * Use with caution - no automatic sanitization is performed.
     *
     * @param string $query       Raw SQL query
     * @param string $output_type Output type: ARRAY_A or OBJECT (default: ARRAY_A)
     * @return array Query results
     * @access protected
     * @since  1.2.0
     * @warning Potential SQL injection risk - sanitize inputs before calling
     */
    protected function raw_query(string $query, string $output_type = ARRAY_A)
    {
        $output_type = in_array($output_type, [ARRAY_A, OBJECT]) ? $output_type : ARRAY_A;

        global $wpdb;

        $results = $wpdb->get_results($query, $output_type);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()

        if ($output_type === ARRAY_A && !empty($results)) {
            foreach ($results as $key => $result) {
                $results[$key] = wp_parse_args($result, $this->get_defaults());
            }
        }

        return $results ? $results : [];
    }

    /**
     * Get records with complex conditions using query builder
     *
     * Advanced query method with support for complex SQL features.
     *
     * @param array $args Query arguments including:
     *                    - select: SELECT clause (default: '*')
     *                    - where: WHERE conditions (default: [])
     *                    - orderby: ORDER BY field (default: 'id')
     *                    - order: ORDER direction (default: 'DESC')
     *                    - limit: Maximum records (default: 100)
     *                    - offset: Records to skip (default: 0)
     *                    - group_by: GROUP BY clause
     *                    - having: HAVING clause
     * @return array Query results
     * @access public
     * @since  1.2.0
     */
    public function query(array $args = [])
    {
        $defaults = [
            'select' => '*',
            'where' => [],
            'orderby' => 'id',
            'order' => 'DESC',
            'limit' => 100,
            'offset' => 0,
            'group_by' => '',
            'having' => '',
        ];

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

        global $wpdb;
        $table_name = DatabaseSchema::get_table_name($this->table_name);

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

        $select = $args['select'] === '*' ? '*' : sanitize_text_field($args['select']);
        $where_sql = $this->build_advanced_where($args['where']);
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        $limit = intval($args['limit']);
        $offset = intval($args['offset']);

        $query = "SELECT {$select} FROM {$table_name} {$where_sql}";

        if (!empty($args['group_by'])) {
            $group_by = sanitize_key($args['group_by']);
            $query .= " GROUP BY {$group_by}";
        }

        if (!empty($args['having'])) {
            $having = sanitize_text_field($args['having']);
            $query .= " HAVING {$having}";
        }

        if ($orderby) {
            $query .= " ORDER BY {$orderby}";
        }

        if ($limit > 0) {
            $query .= " LIMIT {$limit}";

            if ($offset > 0) {
                $query .= " OFFSET {$offset}";
            }
        }

        return $this->raw_query($query);
    }

    /**
     * Get data for sparkline (mini chart) with caching
     *
     * Generates compact data series for sparkline visualizations.
     *
     * @param string $date_field  Date field (default: 'created_at')
     * @param string $value_field Field to aggregate (default: 'id')
     * @param int    $points      Number of data points (default: 20)
     * @param string $interval    Interval: 'hour', 'day', 'week' (default: 'hour')
     * @param array  $conditions  Where conditions (default: [])
     * @param bool   $use_cache   Whether to use cache (default: true)
     * @return array Sparkline data values
     * @access public
     * @since  1.2.0
     */
    public function get_sparkline_data(string $date_field = 'created_at', string $value_field = 'id', int $points = 20,
                                      string $interval = 'hour', array $conditions = [], bool $use_cache = true)
    {
        $points = absint($points);
        $interval = in_array($interval, ['hour', 'day', 'week']) ? $interval : 'hour';
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('sparkline', [$date_field, $value_field, $points, $interval, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($date_field, $value_field, $points, $interval, $conditions) {
            $chart_data = $this->get_chart_data($date_field, $value_field, $interval, $conditions, $points, 'COUNT', false);
            return isset($chart_data['data']) ? $chart_data['data'] : [];
        }, $this->very_short_cache_duration, $use_cache);
    }

    /**
     * Get top N records by a metric with caching
     *
     * Retrieves top records ranked by a metric with aggregation.
     *
     * @param string $group_field  Field to group by
     * @param string $metric_field Field to measure (default: 'id')
     * @param string $agg_function Aggregate function (default: 'COUNT')
     * @param int    $limit        Top N records (default: 10)
     * @param array  $conditions   Where conditions (default: [])
     * @param bool   $use_cache    Whether to use cache (default: true)
     * @return array Top records data including labels, values, and raw results
     * @access public
     * @since  1.2.0
     */
    public function get_top_records(string $group_field, string $metric_field = 'id', string $agg_function = 'COUNT',
                                   int $limit = 10, array $conditions = [], bool $use_cache = true)
    {
        $group_field = sanitize_text_field($group_field);
        $metric_field = sanitize_text_field($metric_field) ?: 'id';
        $agg_function = $this->sanitize_aggregate_function($agg_function);
        $limit = absint($limit);
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('top', [$group_field, $metric_field, $agg_function, $limit, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($group_field, $metric_field, $agg_function, $limit, $conditions) {
            $results = $this->group_by($group_field, $agg_function, $metric_field, $conditions, $limit, false);

            usort($results, function($a, $b) {
                $a_val = isset($a['aggregate_value']) ? $a['aggregate_value'] : 0;
                $b_val = isset($b['aggregate_value']) ? $b['aggregate_value'] : 0;
                return $b_val <=> $a_val;
            });

            $labels = [];
            $values = [];

            foreach ($results as $row) {
                $group_value = isset($row[$group_field]) ? $row[$group_field] : '';
                $aggregate_value = isset($row['aggregate_value']) ? $row['aggregate_value'] : 0;

                $labels[] = $group_value;
                $values[] = (float) $aggregate_value;
            }

            return [
                'labels' => $labels,
                'values' => $values,
                'raw'    => $results,
            ];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Get trend data (comparing periods) with caching
     *
     * Calculates trend data comparing current and previous periods.
     *
     * @param string $metric     Metric to track: 'count' or field name for 'sum' (default: 'count')
     * @param string $period     Period to compare: 'today', 'week', 'month' (default: 'today')
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Trend data including current, previous, change, and trend direction
     * @access public
     * @since  1.2.0
     */
    public function get_trend_data(string $metric = 'count', string $period = 'today', array $conditions = [], bool $use_cache = true)
    {
        $metric = sanitize_text_field($metric) ?: 'count';
        $period = in_array($period, ['today', 'week', 'month']) ? $period : 'today';
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('trend', [$metric, $period, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($metric, $period, $conditions) {
            $ranges = $this->get_period_ranges($period);

            $current_conditions = array_merge($conditions, [
                'created_at >=' => $ranges['current_start'],
                'created_at <=' => $ranges['current_end'],
            ]);

            if ($metric === 'count') {
                $current_value = $this->count($current_conditions, 'id', false);
            } else {
                $current_value = $this->sum($metric, $current_conditions, false);
            }

            $previous_conditions = array_merge($conditions, [
                'created_at >=' => $ranges['previous_start'],
                'created_at <=' => $ranges['previous_end'],
            ]);

            if ($metric === 'count') {
                $previous_value = $this->count($previous_conditions, 'id', false);
            } else {
                $previous_value = $this->sum($metric, $previous_conditions, false);
            }

            $change = $current_value - $previous_value;
            $change_percent = $previous_value > 0 ? ($change / $previous_value) * 100 : 0;

            $trend = 'stable';
            if ($change > 0) {
                $trend = 'up';
            } elseif ($change < 0) {
                $trend = 'down';
            }

            return [
                'current'        => (float) $current_value,
                'previous'       => (float) $previous_value,
                'change'         => (float) $change,
                'change_percent' => round($change_percent, 2),
                'trend'          => $trend,
            ];
        }, $this->very_short_cache_duration, $use_cache);
    }

    /**
     * Get period date ranges for trend comparison
     *
     * Internal method to generate date ranges for trend analysis.
     *
     * @param string $period Period type: 'today', 'week', 'month'
     * @return array Date ranges for current and previous periods
     * @access private
     * @since  1.2.0
     */
    private function get_period_ranges(string $period)
    {
        $now = time(); // UTC Unix timestamp — matches UTC-stored DB datetimes.

        switch ($period) {
            case 'today':
                return [
                    'current_start'  => gmdate('Y-m-d 00:00:00', $now),
                    'current_end'    => gmdate('Y-m-d 23:59:59', $now),
                    'previous_start' => gmdate('Y-m-d 00:00:00', strtotime('-1 day', $now)),
                    'previous_end'   => gmdate('Y-m-d 23:59:59', strtotime('-1 day', $now)),
                ];

            case 'week':
                return [
                    'current_start'  => gmdate('Y-m-d 00:00:00', strtotime('-7 days', $now)),
                    'current_end'    => gmdate('Y-m-d 23:59:59', $now),
                    'previous_start' => gmdate('Y-m-d 00:00:00', strtotime('-14 days', $now)),
                    'previous_end'   => gmdate('Y-m-d 23:59:59', strtotime('-8 days', $now)),
                ];

            case 'month':
                return [
                    'current_start'  => gmdate('Y-m-01 00:00:00', $now),
                    'current_end'    => gmdate('Y-m-t 23:59:59', $now),
                    'previous_start' => gmdate('Y-m-01 00:00:00', strtotime('-1 month', $now)),
                    'previous_end'   => gmdate('Y-m-t 23:59:59', strtotime('-1 month', $now)),
                ];

            default:
                return $this->get_period_ranges('today');
        }
    }

    /**
     * Get distribution data with caching
     *
     * Generates data for distribution analysis across value ranges.
     *
     * @param string $field      Field to analyze
     * @param array  $ranges     Range definitions as ['label' => [min, max], ...]
     * @param array  $conditions Where conditions (default: [])
     * @param bool   $use_cache  Whether to use cache (default: true)
     * @return array Distribution data with labels and counts
     * @access public
     * @since  1.2.0
     */
    public function get_distribution_data(string $field, array $ranges, array $conditions = [], bool $use_cache = true)
    {
        $field = sanitize_text_field($field);
        $ranges = $this->sanitize_ranges($ranges);
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('distribution', [$field, $ranges, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($field, $ranges, $conditions) {
            $labels = [];
            $data = [];

            foreach ($ranges as $label => $range) {
                $range_conditions = array_merge($conditions, [
                    $field . ' >=' => isset($range[0]) ? $range[0] : 0,
                    $field . ' <=' => isset($range[1]) ? $range[1] : 0,
                ]);

                $labels[] = $label;
                $data[] = $this->count($range_conditions, 'id', false);
            }

            return [
                'labels' => $labels,
                'data'   => $data,
            ];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Sanitize ranges array
     *
     * Internal method to sanitize range definitions for distribution analysis.
     *
     * @param array $ranges Ranges array to sanitize
     * @return array Sanitized ranges
     * @access private
     * @since  1.2.0
     */
    private function sanitize_ranges(array $ranges)
    {
        $sanitized = [];

        foreach ($ranges as $label => $range) {
            $label = sanitize_text_field($label);
            $range = (array) $range;

            if (count($range) >= 2) {
                $sanitized[$label] = [
                    $this->sanitize_condition_value($range[0]),
                    $this->sanitize_condition_value($range[1])
                ];
            }
        }

        return $sanitized;
    }

    /**
     * Get data for heatmap visualization with caching
     *
     * Generates data for heatmap visualizations with two-dimensional grouping.
     *
     * @param string $x_field     Field for X-axis (default: 'HOUR(created_at)')
     * @param string $y_field     Field for Y-axis (default: 'DAYOFWEEK(created_at)')
     * @param string $value_field Field to aggregate (default: 'id')
     * @param array  $conditions  Where conditions (default: [])
     * @param bool   $use_cache   Whether to use cache (default: true)
     * @return array Heatmap data including labels, data matrix, and value ranges
     * @access public
     * @since  1.2.0
     */
    public function get_heatmap_data(string $x_field = 'HOUR(created_at)', string $y_field = 'DAYOFWEEK(created_at)',
                                    string $value_field = 'id', array $conditions = [], bool $use_cache = true)
    {
        $x_field = sanitize_text_field($x_field) ?: 'HOUR(created_at)';
        $y_field = sanitize_text_field($y_field) ?: 'DAYOFWEEK(created_at)';
        $value_field = sanitize_text_field($value_field) ?: 'id';
        $conditions = $this->sanitize_conditions($conditions);

        $cache_key = $this->get_cache_key('heatmap', [$x_field, $y_field, $value_field, $conditions]);

        return $this->get_cached_data($cache_key, function() use ($x_field, $y_field, $value_field, $conditions) {
            global $wpdb;
            $table_name = DatabaseSchema::get_table_name($this->table_name);

            if (!$table_name) {
                return [
                    'x_labels' => [],
                    'y_labels' => [],
                    'data' => [],
                    'min' => 0,
                    'max' => 0
                ];
            }

            $where_sql = $this->build_where_clause($conditions);

            $query = "SELECT {$x_field} as x_value,
                             {$y_field} as y_value,
                             COUNT({$value_field}) as value
                      FROM {$table_name}
                      {$where_sql}
                      GROUP BY x_value, y_value
                      ORDER BY x_value, y_value";

            $results = $wpdb->get_results($query, ARRAY_A);  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $results = is_array($results) ? $results : [];

            $data_matrix = [];
            $x_labels = [];
            $y_labels = [];
            $min_value = PHP_INT_MAX;
            $max_value = 0;

            foreach ($results as $row) {
                $x = isset($row['x_value']) ? (int) $row['x_value'] : 0;
                $y = isset($row['y_value']) ? (int) $row['y_value'] : 0;
                $val = isset($row['value']) ? (int) $row['value'] : 0;

                if (!in_array($x, $x_labels, true)) {
                    $x_labels[] = $x;
                }
                if (!in_array($y, $y_labels, true)) {
                    $y_labels[] = $y;
                }

                if (!isset($data_matrix[$y])) {
                    $data_matrix[$y] = [];
                }
                $data_matrix[$y][$x] = $val;

                $min_value = min($min_value, $val);
                $max_value = max($max_value, $val);
            }

            sort($x_labels);
            sort($y_labels);

            return [
                'x_labels' => $x_labels,
                'y_labels' => $y_labels,
                'data'     => $data_matrix,
                'min'      => empty( $results ) ? 0 : $min_value,
                'max'      => $max_value,
            ];
        }, $this->short_cache_duration, $use_cache);
    }

    /**
     * Transform records to key-value pairs
     *
     * Extracts key-value pairs from records for use in dropdowns or select fields.
     *
     * @param array  $records    Records array
     * @param string $key_field  Field to use as key (default: 'id')
     * @param string $value_field Field to use as value (default: 'name')
     * @return array Key-value pairs
     * @access protected
     * @since  1.2.0
     */
    protected function pluck(array $records, string $key_field = 'id', string $value_field = 'name')
    {
        $key_field = sanitize_text_field($key_field) ?: 'id';
        $value_field = sanitize_text_field($value_field) ?: 'name';

        $result = [];

        foreach ($records as $record) {
            if (isset($record[$key_field]) && isset($record[$value_field])) {
                $result[$record[$key_field]] = $record[$value_field];
            }
        }

        return $result;
    }

    /**
     * Group records by field value
     *
     * Groups records by a specific field value.
     *
     * @param array  $records    Records array
     * @param string $group_field Field to group by
     * @return array Records grouped by field value
     * @access protected
     * @since  1.2.0
     */
    protected function group_records(array $records, string $group_field)
    {
        $group_field = sanitize_text_field($group_field);

        $grouped = [];

        foreach ($records as $record) {
            if (isset($record[$group_field])) {
                $group_value = $record[$group_field];

                if (!isset($grouped[$group_value])) {
                    $grouped[$group_value] = [];
                }

                $grouped[$group_value][] = $record;
            }
        }

        return $grouped;
    }

    /**
     * Extract single column from records
     *
     * Extracts a single column/field from records array.
     *
     * @param array  $records Records array
     * @param string $field   Field name to extract
     * @return array Column values
     * @access protected
     * @since  1.2.0
     */
    protected function column($records, $field)
    {
        $field = sanitize_text_field($field);

        $values = [];

        foreach ($records as $record) {
            if (isset($record[$field])) {
                $values[] = $record[$field];
            }
        }

        return $values;
    }

    /**
     * Validate email field
     *
     * Validates an email address using WordPress is_email() function.
     *
     * @param string $email Email address to validate
     * @return bool True if email is valid
     * @access protected
     * @since  1.2.0
     */
    protected function validate_email(string $email)
    {
        return is_email($email);
    }

    /**
     * Validate URL field
     *
     * Validates a URL using PHP filter_var().
     *
     * @param string $url URL to validate
     * @return bool True if URL is valid
     * @access protected
     * @since  1.2.0
     */
    protected function validate_url(string $url)
    {
        return filter_var($url, FILTER_VALIDATE_URL) !== false;
    }

    /**
     * Validate IP address field
     *
     * Validates an IP address using PHP filter_var().
     *
     * @param string $ip IP address to validate
     * @return bool True if IP is valid
     * @access protected
     * @since  1.2.0
     */
    protected function validate_ip($ip)
    {
        return filter_var($ip, FILTER_VALIDATE_IP) !== false;
    }

    /**
     * Validate date field
     *
     * Validates a date string against specified format.
     *
     * @param string $date   Date string to validate
     * @param string $format Date format (default: 'Y-m-d H:i:s')
     * @return bool True if date is valid
     * @access protected
     * @since  1.2.0
     */
    protected function validate_date($date, $format = 'Y-m-d H:i:s')
    {
        $d = \DateTime::createFromFormat($format, $date);
        return $d && $d->format($format) === $date;
    }

    /**
     * Validate enum field
     *
     * Validates that a value is within allowed values (enum-like validation).
     *
     * @param mixed $value         Value to validate
     * @param array $allowed_values Array of allowed values
     * @return bool True if value is allowed
     * @access protected
     * @since  1.2.0
     */
    protected function validate_enum($value, array $allowed_values)
    {
        return in_array($value, $allowed_values, true);
    }

    /**
     * Validate required fields
     *
     * Validates that all required fields are present and non-empty in data array.
     *
     * @param array $data           Data array to validate
     * @param array $required_fields Array of required field names
     * @return bool True if all required fields are present and non-empty
     * @access protected
     * @since  1.2.0
     */
    protected function validate_required(array $data, array $required_fields)
    {
        foreach ($required_fields as $field) {
            if (!isset($data[$field]) || $data[$field] === '' || $data[$field] === null) {
                return false;
            }
        }
        return true;
    }

    /**
     * Begin database transaction
     *
     * Starts a database transaction for atomic operations.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     * @see    commit()
     * @see    rollback()
     */
    protected function begin_transaction()
    {
        global $wpdb;
        $wpdb->query('START TRANSACTION');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
    }

    /**
     * Commit database transaction
     *
     * Commits the current database transaction.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     * @see    begin_transaction()
     * @see    rollback()
     */
    protected function commit()
    {
        global $wpdb;
        $wpdb->query('COMMIT');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
    }

    /**
     * Rollback database transaction
     *
     * Rolls back the current database transaction.
     *
     * @return void
     * @access protected
     * @since  1.2.0
     * @see    begin_transaction()
     * @see    commit()
     */
    protected function rollback()
    {
        global $wpdb;
        $wpdb->query('ROLLBACK');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
    }

    /**
     * Execute operations in transaction
     *
     * Executes operations within a database transaction with automatic rollback on failure.
     *
     * @param callable $callback Function to execute within transaction
     * @return bool True if transaction succeeded, false if failed
     * @access public
     * @since  1.2.0
     */
    public function transaction(callable $callback)
    {
        if (!is_callable($callback)) {
            return false;
        }

        $this->begin_transaction();

        try {
            $result = call_user_func($callback, $this);

            if ($result === false) {
                throw new \Exception(esc_html( 'Transaction callback returned false' ));
            }

            $this->commit();
            return true;

        } catch (\Exception $e) {
            $this->rollback();
            $this->log('Transaction failed: ' . $e->getMessage(), 'error');
            return false;
        }
    }

    // =========================================================================
    // SHARED QUERY HELPERS — available to all child table classes
    // =========================================================================

    /**
     * Toggle the 'is_active' boolean field for a record.
     * Child classes no longer need to define their own toggle_status().
     *
     * @param  int  $id  Record ID
     * @return bool
     */
    public function toggle_status($id)
    {
        return (bool) $this->toggle_boolean($id, 'is_active');
    }

    /**
     * Fetch records by IP address (works for any table that has ip_address column).
     *
     * @param  string $ip_address  IPv4 or IPv6 address
     * @param  int    $limit       Max records (default 100)
     * @param  string $orderby     Column to order by (default 'created_at')
     * @return array
     */
    public function get_by_ip( string $ip_address, int $limit = 100, string $orderby = 'created_at' ): array
    {
        $ip_address = sanitize_text_field( $ip_address );
        $limit      = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'ip_address' => $ip_address ], $limit, $orderby, 'DESC' );
    }

    /**
     * Fetch records by user_id (works for any table with a user_id column).
     *
     * @param  int    $user_id
     * @param  int    $limit
     * @param  string $orderby
     * @return array
     */
    public function get_by_user( int $user_id, int $limit = 100, string $orderby = 'created_at' ): array
    {
        $user_id = absint( $user_id );
        $limit   = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'user_id' => $user_id ], $limit, $orderby, 'DESC' );
    }

    /**
     * Fetch records by severity column.
     *
     * @param  string $severity  e.g. 'low','medium','high','critical'
     * @param  int    $limit
     * @return array
     */
    public function get_by_severity( string $severity, int $limit = 100 ): array
    {
        $severity = sanitize_key( $severity );
        $limit    = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'severity' => $severity ], $limit, 'created_at', 'DESC' );
    }

    /**
     * Fetch records by status column.
     *
     * @param  string $status
     * @param  int    $limit
     * @return array
     */
    public function get_by_status( string $status, int $limit = 100 ): array
    {
        $status = sanitize_text_field( $status );
        $limit  = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'status' => $status ], $limit, 'created_at', 'DESC' );
    }

    /**
     * Fetch records by type/event_type column.
     *
     * @param  string $type
     * @param  int    $limit
     * @return array
     */
    public function get_by_type( string $type, int $limit = 100 ): array
    {
        $type  = sanitize_text_field( $type );
        $limit = max( 1, min( 5000, absint( $limit ) ) );
        // Support both 'type' and 'event_type' columns
        $col = $this->has_column( 'event_type' ) ? 'event_type' : 'type';
        return (array) $this->get_by( [ $col => $type ], $limit, 'created_at', 'DESC' );
    }

    /**
     * Fetch records by country_code column.
     *
     * @param  string $country_code  ISO 3166-1 alpha-2 code
     * @param  int    $limit
     * @return array
     */
    public function get_by_country( string $country_code, int $limit = 100 ): array
    {
        $country_code = strtoupper( sanitize_text_field( $country_code ) );
        $limit        = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'country_code' => $country_code ], $limit, 'created_at', 'DESC' );
    }

    /**
     * Fetch records by threat_level column.
     *
     * @param  string $threat_level  e.g. 'low','medium','high','critical'
     * @param  int    $limit
     * @return array
     */
    public function get_by_threat_level( string $threat_level, int $limit = 100 ): array
    {
        $threat_level = sanitize_key( $threat_level );
        $limit        = max( 1, min( 5000, absint( $limit ) ) );
        return (array) $this->get_by( [ 'threat_level' => $threat_level ], $limit, 'created_at', 'DESC' );
    }

    /**
     * Generic delete-old-records by date column.
     * Child classes may call this or override for custom logic.
     *
     * @param  int    $days       Records older than this many days are deleted
     * @param  string $date_col   Date column to check (default 'created_at')
     * @return int                Number of rows deleted
     */
    public function delete_old_records($days = 90, $date_col = 'created_at')
    {
        global $wpdb;

        $days     = max(1, absint($days));
        $date_col = $this->sanitize_orderby_field($date_col);
        $table    = DatabaseSchema::get_table_name( $this->table_name ) ?: ( $wpdb->prefix . $this->table_name );
        $cutoff   = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $deleted = $wpdb->query(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "DELETE FROM `{$table}` WHERE `{$date_col}` < %s",
                $cutoff
            )
        );

        if ($deleted) {
            $this->clear_list_caches();
        }

        return (int) $deleted;
    }

    /**
     * Return standard severity levels used across security tables.
     *
     * @return array<string,string>
     */
    public function get_severity_levels()
    {
        return [
            'debug'    => __('Debug',    'ultraguard-security-suite'),
            'info'     => __('Info',     'ultraguard-security-suite'),
            'low'      => __('Low',      'ultraguard-security-suite'),
            'medium'   => __('Medium',   'ultraguard-security-suite'),
            'high'     => __('High',     'ultraguard-security-suite'),
            'critical' => __('Critical', 'ultraguard-security-suite'),
        ];
    }

    /**
     * Return standard threat levels used across security tables.
     *
     * @return array<string,string>
     */
    public function get_threat_levels()
    {
        return [
            'none'     => __('None',     'ultraguard-security-suite'),
            'low'      => __('Low',      'ultraguard-security-suite'),
            'medium'   => __('Medium',   'ultraguard-security-suite'),
            'high'     => __('High',     'ultraguard-security-suite'),
            'critical' => __('Critical', 'ultraguard-security-suite'),
        ];
    }

    /**
     * Check whether a column exists in this table's schema.
     * Uses information_schema for reliable detection; result is cached per request.
     *
     * @param  string $column_name
     * @return bool
     */
    public function has_column(string $column_name): bool
    {
        static $column_cache = [];

        $cache_key = $this->table_name . '.' . $column_name;
        if (isset($column_cache[$cache_key])) {
            return $column_cache[$cache_key];
        }

        global $wpdb;
        $full_table = DatabaseSchema::get_table_name( $this->table_name ) ?: ( $wpdb->prefix . $this->table_name );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $exists = (bool) $wpdb->get_var(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = %s
                   AND COLUMN_NAME  = %s",
                $full_table,
                $column_name
            )
        );

        $column_cache[$cache_key] = $exists;
        return $exists;
    }
}
