<?php
namespace UltraguardPro\core;

use UltraguardPro\core\CacheManager;

/**
 * Enhanced Database CRUD Operations for Ultraguard Pro Security Plugin
 *
 * Provides secure, performant CRUD (Create, Read, Update, Delete) operations
 * for all Ultraguard Pro database tables with comprehensive validation,
 * caching, and error handling. This class enforces security best practices
 * including SQL injection prevention, capability checks, and data sanitization.
 *
 * @package    UltraguardPro\core
 * @author     Ultraguard Pro Team <support@ultraguardpro.com>
 * @copyright  2024 Ultraguard Pro
 * @license    GPL-3.0-or-later
 * @version    1.2.0
 * @since      1.2.0
 * @link       https://ultraguardpro.com
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Central low-level DB abstraction layer for the plugin. Queries here are intentional and wrapped by validation/caching helpers.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- This CRUD layer builds queries from validated table/column fragments and prepared values by design.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Generic query-builder methods assemble validated SQL identifiers/fragments before handing values to prepare().

/**
 * Enhanced Database CRUD Operations
 *
 * Complete CRUD operations for Ultraguard Pro tables with security validation,
 * caching, batch operations, and comprehensive error handling. This class
 * provides a secure abstraction layer for all database interactions with
 * the following key features:
 * - SQL injection prevention through prepared statements
 * - Comprehensive data validation and sanitization
 * - WordPress object caching integration
 * - Batch operation support with transactions
 * - Capability-based access control
 * - Detailed error logging and handling
 * - Schema-aware data processing
 *
 * @package    UltraguardPro\core
 * @author     Ultraguard Pro Team <support@ultraguardpro.com>
 * @version    1.2.0
 * @since      1.2.0
 *
 * @example
 * // Insert a record
 * $id = DatabaseCrud::insert('security_logs', [
 *     'event_type' => 'firewall',
 *     'severity' => 'warning',
 *     'message' => 'Blocked suspicious request'
 * ]);
 *
 * // Get a record by ID
 * $record = DatabaseCrud::get_by_id('security_logs', 123);
 *
 * // Search records
 * $results = DatabaseCrud::search_records('security_logs', 'injection');
 *
 * // Get paginated results
 * $paginated = DatabaseCrud::get_paginated_records('security_logs', [
 *     'page' => 1,
 *     'per_page' => 20
 * ]);
 */
class DatabaseCrud {

    /**
     * Error messages collected during operations
     *
     * @var string[]
     * @access protected
     * @static
     */
    protected static $errors = [];

    /**
     * Cache for database schema lookups to reduce repeated queries
     *
     * @var array<string, mixed>
     * @access protected
     * @static
     */
    protected static $schema_cache = [];

    /**
     * Flag indicating batch mode for error collection
     *
     * When true, errors are collected in batch_errors instead of errors.
     *
     * @var bool
     * @access protected
     * @static
     */
    protected static $batch_mode = false;

    /**
     * Batch errors collection for bulk operations
     *
     * @var string[]
     * @access protected
     * @static
     */
    protected static $batch_errors = [];

    /**
     * Insert record into table with comprehensive validation
     *
     * Inserts a single record into the specified table with full security
     * validation, capability checks, and data sanitization.
     *
     * @param string $table_key Table identifier (e.g., 'security_logs')
     * @param array  $data      Associative array of column => value pairs
     * @param array  $format    Optional array of format specifiers (%s, %d, %f)
     *
     * @return int|false Inserted record ID or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @throws \Exception If database operation fails
     * @see    wpdb::insert()
     * @see    self::sanitize_data()
     * @see    self::validate_required_fields()
     *
     * @example
     * $id = DatabaseCrud::insert('security_logs', [
     *     'event_type' => 'firewall',
     *     'severity' => 'warning',
     *     'message' => 'Blocked request',
     *     'ip_address' => '192.168.1.1'
     * ]);
     * if ($id) {
     *     echo "Record inserted with ID: {$id}";
     * }
     */
    public static function insert($table_key, $data, $format = null, bool $internal = false) {
        // $internal = true bypasses the manage_options capability check for trusted
        // plugin-infrastructure callers (e.g. Logger) that run in non-admin contexts
        // (firewall blocking a public visitor, cron task without a logged-in user, etc.).
        // Never pass $internal = true from user-facing AJAX handlers or controllers.
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            self::handle_database_error(__('Invalid table key', 'ultraguard-security-suite'));
            return false;
        }

        $table_key = sanitize_key($table_key);
        $data = is_array($data) ? $data : [];

        // Security: capability check — skipped for trusted internal callers ($internal = true).
        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error(__('Insufficient permissions for database insert operation', 'ultraguard-security-suite'));
            return false;
        }

        try {
            // Fire before insert hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/before_database_insert', $table_key, $data);
            }

            // Get table name with caching
            $table_name = self::get_validated_table_name($table_key);
            if (!$table_name) {
                self::handle_database_error(__('Invalid table name', 'ultraguard-security-suite'));
                return false;
            }

            // SECURE: Enhanced data sanitization with early validation
            $data = self::sanitize_data($table_key, $data);
            if (empty($data)) {
                self::handle_database_error(__('No valid data to insert', 'ultraguard-security-suite'));
                return false;
            }

            // Allow filtering data before insert
            $data = apply_filters('ultraguard_pro/before_insert_data', $data, $table_key);

            // Security: Validate required fields
            if (!self::validate_required_fields($table_key, $data)) {
                self::handle_database_error(__('Required fields missing', 'ultraguard-security-suite'));
                return false;
            }

            // Security: Use proper format specifiers
            if ($format === null) {
                $format = self::get_format_specifiers($table_key, $data);
            }

            $result = $wpdb->insert($table_name, $data, $format);

            if ($result === false) {
                $error_message = !empty($wpdb->last_error) ? $wpdb->last_error : __('Unknown database error', 'ultraguard-security-suite');
                /* translators: %s: string */
                throw new \Exception(sprintf(__('Database insert operation failed: %s', 'ultraguard-security-suite'), $error_message));
            }

            $insert_id = !empty($wpdb->insert_id) ? $wpdb->insert_id : false;

            // Clear table cache on successful insert
            self::clear_table_cache($table_key);

            // Fire after insert hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/after_database_insert', $table_key, $data, $insert_id, $result);
            }

            return $insert_id;

        } catch (\Exception $e) {
            self::handle_database_error($e->getMessage());
            return false;
        }
    }

    /**
     * Bulk insert multiple records with optimized performance
     *
     * Inserts multiple records in a single ATOMIC MySQL transaction.
     * ALL-OR-NOTHING: if any record fails validation or INSERT fails,
     * the entire transaction is ROLLED BACK — no partial results are kept.
     * For partial-success semantics, call insert() per record instead.
     *
     * @param string $table_key   Table identifier (e.g., 'security_logs')
     * @param array  $data_array  Array of associative arrays containing data
     * @param array  $format      Optional array of format specifiers
     *
     * @return bool True if all records inserted successfully, false otherwise
     * @access public
     * @static
     * @since  1.2.0
     *
     * @throws \Exception If database operation fails catastrophically
     * @see    self::insert()
     * @see    self::start_batch()
     * @see    self::end_batch()
     *
     * @example
     * $records = [
     *     ['event_type' => 'firewall', 'severity' => 'warning'],
     *     ['event_type' => 'login', 'severity' => 'info'],
     *     ['event_type' => 'scan', 'severity' => 'error']
     * ];
     * $success = DatabaseCrud::bulk_insert('security_logs', $records);
     */
    public static function bulk_insert($table_key, $data_array, $format = null, bool $internal = false) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            self::handle_database_error(__('Invalid table key', 'ultraguard-security-suite'));
            return false;
        }

        $table_key = sanitize_key($table_key);
        $data_array = is_array($data_array) ? $data_array : [];

        if (empty($data_array)) {
            self::handle_database_error(__('No data provided for bulk insert', 'ultraguard-security-suite'));
            return false;
        }

        // Security: Verify user capabilities
        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error(__('Insufficient permissions for database bulk insert operation', 'ultraguard-security-suite'));
            return false;
        }

        try {
            // Enter batch mode for error handling
            self::start_batch();

            // Fire before bulk insert hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/before_bulk_insert', $table_key, $data_array);
            }

            // Get table name with caching
            $table_name = self::get_validated_table_name($table_key);
            if (!$table_name) {
                throw new \Exception(__('Invalid table name', 'ultraguard-security-suite'));
            }

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

            $inserted_count = 0;
            $success = true;

            // Pre-calculate format if not provided
            $default_format = ($format === null && !empty($data_array[0])) ?
                self::get_format_specifiers($table_key, $data_array[0]) :
                $format;

            foreach ($data_array as $index => $data) {
                // SECURE: Ensure data is always an array
                $data = is_array($data) ? $data : [];

                // Security: Enhanced data sanitization
                $data = self::sanitize_data($table_key, $data);
                if (empty($data)) {
                    self::handle_database_error(__('Invalid data in bulk insert at index: ', 'ultraguard-security-suite') . $index, false);
                    $success = false;
                    continue;
                }

                // Allow filtering individual data before insert
                $data = apply_filters('ultraguard_pro/before_bulk_insert_item', $data, $table_key);

                // Security: Validate required fields
                if (!self::validate_required_fields($table_key, $data)) {
                    self::handle_database_error(__('Required fields missing in bulk insert at index: ', 'ultraguard-security-suite') . $index, false);
                    $success = false;
                    continue;
                }

                // Use pre-calculated format or calculate per item
                $current_format = ($format !== null) ? $format : $default_format;
                if ($current_format === null) {
                    $current_format = self::get_format_specifiers($table_key, $data);
                }

                $result = $wpdb->insert($table_name, $data, $current_format);

                if ($result === false) {
                    $error_message = !empty($wpdb->last_error) ? $wpdb->last_error : __('Unknown database error', 'ultraguard-security-suite');
                    /* translators: %1$d: integer, %2$s: string */
                    self::handle_database_error(sprintf(__('Bulk insert operation failed at index %1$d: %2$s', 'ultraguard-security-suite'), $index, $error_message), false);
                    $success = false;
                    continue;
                }

                $inserted_count++;
            }

            if ($success) {
                $wpdb->query('COMMIT');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
                // Clear cache only if transaction succeeded
                self::clear_table_cache($table_key);
            } else {
                $wpdb->query('ROLLBACK');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
            }

            // Exit batch mode and consolidate errors
            self::end_batch();

            // Fire after bulk insert hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/after_bulk_insert', $table_key, $inserted_count);
            }

            return $success;

        } catch (\Exception $e) {
            $wpdb->query('ROLLBACK');  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- static query, no user input
            self::end_batch();
            self::handle_database_error($e->getMessage());
            return false;
        }
    }

    /**
     * Update record in table with comprehensive validation
     *
     * Updates existing records matching the WHERE conditions with
     * full security validation and data sanitization.
     *
     * @param string $table_key    Table identifier (e.g., 'security_logs')
     * @param array  $data         Associative array of column => value pairs to update
     * @param array  $where        Associative array of WHERE conditions
     * @param array  $format       Optional array of format specifiers for data
     * @param array  $where_format Optional array of format specifiers for WHERE
     *
     * @return bool|int Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @throws \Exception If database operation fails
     * @see    wpdb::update()
     * @see    self::sanitize_data()
     *
     * @example
     * $updated = DatabaseCrud::update('security_logs',
     *     ['severity' => 'critical'],
     *     ['id' => 123]
     * );
     * if ($updated) {
     *     echo "Updated {$updated} record(s)";
     * }
     */
    public static function update($table_key, $data, $where, $format = null, $where_format = null, bool $internal = false) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            self::handle_database_error(__('Invalid table key', 'ultraguard-security-suite'));
            return false;
        }

        $table_key = sanitize_key($table_key);
        $data = is_array($data) ? $data : [];
        $where = is_array($where) ? $where : [];

        if (empty($data) || empty($where)) {
            self::handle_database_error(__('Update requires data and where conditions', 'ultraguard-security-suite'));
            return false;
        }

        // Security: Verify user capabilities
        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error(__('Insufficient permissions for database update operation', 'ultraguard-security-suite'));
            return false;
        }

        try {
            // Fire before update hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/before_database_update', $table_key, $data, $where);
            }

            // Get table name with caching
            $table_name = self::get_validated_table_name($table_key);
            if (!$table_name) {
                self::handle_database_error(__('Invalid table name', 'ultraguard-security-suite'));
                return false;
            }

            // Security: Enhanced data sanitization
            $data = self::sanitize_data($table_key, $data);
            $where = self::sanitize_data($table_key, $where);

            // Allow filtering data before update
            $data = apply_filters('ultraguard_pro/before_update_data', $data, $table_key, $where);

            // Security: Use proper format specifiers
            if ($format === null) {
                $format = self::get_format_specifiers($table_key, $data);
            }
            if ($where_format === null) {
                $where_format = self::get_format_specifiers($table_key, $where);
            }

            $result = $wpdb->update($table_name, $data, $where, $format, $where_format);

            if ($result === false) {
                $error_message = !empty($wpdb->last_error) ? $wpdb->last_error : __('Unknown database error', 'ultraguard-security-suite');
                /* translators: %s: string */
                throw new \Exception(sprintf(__('Database update operation failed: %s', 'ultraguard-security-suite'), $error_message));
            }

            // Clear table cache on successful update
            self::clear_table_cache($table_key);

            // Fire after update hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/after_database_update', $table_key, $data, $where, $result);
            }

            return $result;

        } catch (\Exception $e) {
            self::handle_database_error($e->getMessage());
            return false;
        }
    }

    /**
     * Update multiple records with conditions
     *
     * Alias for update() method. Updates all records matching WHERE conditions.
     *
     * @param string $table_key    Table identifier
     * @param array  $data         Data to update
     * @param array  $where        WHERE conditions
     * @param array  $format       Format specifiers for data
     * @param array  $where_format Format specifiers for WHERE
     *
     * @return int|false Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::update()
     */
    public static function update_multiple($table_key, $data, $where, $format = null, $where_format = null) {
        return self::update($table_key, $data, $where, $format, $where_format);
    }

    /**
     * Delete record from table with validation
     *
     * Deletes records matching WHERE conditions with full security
     * validation and capability checks.
     *
     * @param string $table_key    Table identifier (e.g., 'security_logs')
     * @param array  $where        Associative array of WHERE conditions
     * @param array  $where_format Optional array of format specifiers
     *
     * @return bool|int Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @throws \Exception If database operation fails
     * @see    wpdb::delete()
     * @see    self::sanitize_data()
     *
     * @example
     * $deleted = DatabaseCrud::delete('security_logs', ['id' => 123]);
     * if ($deleted) {
     *     echo "Deleted {$deleted} record(s)";
     * }
     */
    public static function delete($table_key, $where, $where_format = null, bool $internal = false) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            self::handle_database_error(__('Invalid table key', 'ultraguard-security-suite'));
            return false;
        }

        $table_key = sanitize_key($table_key);
        $where = is_array($where) ? $where : [];

        if (empty($where)) {
            self::handle_database_error(__('Delete requires where conditions', 'ultraguard-security-suite'));
            return false;
        }

        // Security: Verify user capabilities
        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error(__('Insufficient permissions for database delete operation', 'ultraguard-security-suite'));
            return false;
        }

        try {
            // Fire before delete hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/before_database_delete', $table_key, $where);
            }

            // Get table name with caching
            $table_name = self::get_validated_table_name($table_key);
            if (!$table_name) {
                self::handle_database_error(__('Invalid table name', 'ultraguard-security-suite'));
                return false;
            }

            // Security: Enhanced where clause sanitization
            $where = self::sanitize_data($table_key, $where);
            // Security: Use proper format specifiers
            if ($where_format === null) {
                $where_format = self::get_format_specifiers($table_key, $where);
            }

            $result = $wpdb->delete($table_name, $where, $where_format);

            if ($result === false) {
                $error_message = !empty($wpdb->last_error) ? $wpdb->last_error : __('Unknown database error', 'ultraguard-security-suite');
                /* translators: %s: string */
                throw new \Exception(sprintf(__('Database delete operation failed: %s', 'ultraguard-security-suite'), $error_message));
            }

            // Clear table cache on successful delete
            self::clear_table_cache($table_key);

            // Fire after delete hook only if needed
            if (defined('ULTRA_GUARD_PRO_DEBUG') && ULTRA_GUARD_PRO_DEBUG) {
                do_action('ultraguard_pro/after_database_delete', $table_key, $where, $result);
            }

            return $result;

        } catch (\Exception $e) {
            self::handle_database_error($e->getMessage());
            return false;
        }
    }

    /**
     * Delete multiple records with conditions
     *
     * Alias for delete() method. Deletes all records matching WHERE conditions.
     *
     * @param string $table_key    Table identifier
     * @param array  $where        WHERE conditions
     * @param array  $where_format Format specifiers for WHERE
     *
     * @return int|false Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::delete()
     */
    public static function delete_multiple($table_key, $where, $where_format = null) {
        return self::delete($table_key, $where, $where_format);
    }

    /**
     * Soft delete record (set status to deleted)
     *
     * Performs a soft delete by updating a status column instead of
     * physically removing the record from the database.
     *
     * @param string $table_key     Table identifier (e.g., 'security_logs')
     * @param int    $id            Record ID to soft delete
     * @param string $status_column Column name for status (default: 'status')
     *
     * @return bool True on success, false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::update()
     *
     * @example
     * $success = DatabaseCrud::soft_delete('security_logs', 123);
     * if ($success) {
     *     echo "Record soft deleted (status set to 'deleted')";
     * }
     */
    public static function soft_delete($table_key, $id, $status_column = 'status') {
        // SECURE: Safe parameter validation
        $table_key = is_string($table_key) ? sanitize_key($table_key) : '';
        $id = intval($id);
        $status_column = is_string($status_column) ? sanitize_key($status_column) : 'status';

        $data = [$status_column => 'deleted'];
        $where = ['id' => $id];

        return self::update($table_key, $data, $where);
    }

    /**
     * Get single record by ID
     *
     * Retrieves a single record by its primary key ID with caching
     * to improve performance for frequently accessed records.
     *
     * @param string $table_key   Table identifier (e.g., 'security_logs')
     * @param int    $id          Record ID to retrieve
     * @param string $output_type Output format (OBJECT or ARRAY_A)
     *
     * @return object|array|null Record data or null if not found
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::get_row()
     * @see    wp_cache_get()
     * @see    wp_cache_set()
     *
     * @example
     * $record = DatabaseCrud::get_by_id('security_logs', 123);
     * if ($record) {
     *     echo "Found record: " . $record->event_type;
     * }
     */
    public static function get_by_id($table_key, $id, $output_type = OBJECT) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return null;
        }

        $table_key = sanitize_key($table_key);
        $id = intval($id);
        $output_type = in_array($output_type, [OBJECT, ARRAY_A]) ? $output_type : OBJECT;

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return null;
        }

        // Cache key for this query
        $cache_key = $table_key . '_get_by_id_' . $id . '_' . $output_type;
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

        // Security: Use prepared statement
        $query = $wpdb->prepare("
            SELECT * FROM {$table_name}
            WHERE id = %d
        ", $id);

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

        // Cache via CacheManager (TTL controlled from one place)
        if ($result !== null) {
            CacheManager::get_instance()->set_db($cache_key, $result);
        }

        // Allow filtering result
        $result = apply_filters('ultraguard_pro/get_by_id_result', $result, $table_key, $id);

        return $result;
    }

    /**
     * Get single record by custom field
     *
     * Retrieves a single record by matching a specific field value
     * with caching to improve performance.
     *
     * @param string $table_key   Table identifier (e.g., 'security_logs')
     * @param string $field       Field name to search by
     * @param mixed  $value       Value to search for
     * @param string $output_type Output format (OBJECT or ARRAY_A)
     *
     * @return object|array|null Record data or null if not found
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::get_row()
     * @see    self::column_exists()
     *
     * @example
     * $record = DatabaseCrud::get_by_field('security_logs', 'event_type', 'firewall');
     * if ($record) {
     *     echo "Found firewall event";
     * }
     */
    public static function get_by_field($table_key, $field, $value, $output_type = OBJECT) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($field) || $field === '') {
            return null;
        }

        $table_key = sanitize_key($table_key);
        $field = sanitize_key($field);
        $output_type = in_array($output_type, [OBJECT, ARRAY_A]) ? $output_type : OBJECT;

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return null;
        }

        if (!self::column_exists($table_key, $field)) {
            return null;
        }

        // Cache key for this query
        $cache_key = $table_key . '_get_by_field_' . $field . '_' . md5(serialize($value)) . '_' . $output_type;
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

        // Security: Use prepared statement
        $query = $wpdb->prepare("
            SELECT * FROM {$table_name}
            WHERE {$field} = %s
        ", $value);

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

        // Cache via CacheManager (TTL controlled from one place)
        if ($result !== null) {
            CacheManager::get_instance()->set_db($cache_key, $result);
        }

        // Allow filtering result
        $result = apply_filters('ultraguard_pro/get_by_field_result', $result, $table_key, $field, $value);

        return $result;
    }

    /**
     * Get multiple records with filters and caching
     *
     * Retrieves multiple records with filtering, ordering, and pagination
     * options. Results are cached to improve performance.
     *
     * @param string $table_key Table identifier (e.g., 'security_logs')
     * @param array  $args      Query arguments with the following keys:
     *                          - select: Fields to select (default: '*')
     *                          - where: WHERE conditions as array
     *                          - orderby: Field to order by (default: 'id')
     *                          - order: Sort order 'ASC' or 'DESC' (default: 'DESC')
     *                          - limit: Maximum records to return (default: 0 = no limit)
     *                          - offset: Number of records to skip (default: 0)
     *                          - output_type: OBJECT or ARRAY_A (default: OBJECT)
     *
     * @return array Array of records
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::get_results()
     * @see    wpdb::get_row()
     * @see    self::validate_select_fields()
     * @see    self::validate_orderby_field()
     *
     * @example
     * $records = DatabaseCrud::get_records('security_logs', [
     *     'where' => ['severity' => 'critical'],
     *     'orderby' => 'created_at',
     *     'order' => 'DESC',
     *     'limit' => 10
     * ]);
     * foreach ($records as $record) {
     *     echo "Critical event: " . $record->message . "\n";
     * }
     */
    public static function get_records($table_key, $args = []) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return [];
        }

        $table_key = sanitize_key($table_key);
        $args = is_array($args) ? $args : [];

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return [];
        }

        // SECURE: Comprehensive defaults
        $defaults = [
            'select' => '*',
            'where' => [],
            'orderby' => 'id',
            'order' => 'DESC',
            'limit' => 0,
            'offset' => 0,
            'output_type' => OBJECT
        ];

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

        // Extract with validation
        $select = $args['select'];
        $where = $args['where'];
        $orderby = $args['orderby'];
        $order = $args['order'];
        $limit = intval($args['limit']);
        $offset = intval($args['offset']);
        $output_type = in_array($args['output_type'], [OBJECT, ARRAY_A]) ? $args['output_type'] : OBJECT;

        // Cache key generation
        $cache_key = $table_key . '_get_records_' . md5(serialize($args));
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

        // Security: Validate select fields
        if ($select !== '*') {
            $select = self::validate_select_fields($table_key, $select);
        }

        // Build WHERE clause with prepared statements
        $where_clause = '';
        $where_values = [];

        if (!empty($where)) {
            $where_parts = [];
            foreach ($where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                if (is_array($value)) {
                    // Handle IN clause
                    if (empty($value)) {
                        continue;
                    }
                    $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);
            }
        }

        // Build ORDER BY clause
        $orderby_clause = '';
        if (!empty($orderby)) {
            $orderby = self::validate_orderby_field($table_key, $orderby);
            $order = in_array(strtoupper($order), ['ASC', 'DESC']) ? $order : 'DESC';
            $orderby_clause = "ORDER BY {$orderby} {$order}";
        }

        // Build LIMIT clause
        $limit_clause = '';
        if ($limit > 0) {
            $limit_clause = "LIMIT %d";
            $where_values[] = $limit;

            if ($offset > 0) {
                $limit_clause .= " OFFSET %d";
                $where_values[] = $offset;
            }
        }

        // Build and execute query
        if ($limit == 1) {
            if (!empty($where_values)) {
                $results = $wpdb->get_row(
                    $wpdb->prepare(
                        "SELECT {$select} FROM {$table_name} {$where_clause} {$orderby_clause} {$limit_clause}",
                        $where_values
                    ),
                    $output_type
                ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- identifiers and SQL fragments are validated before assembly.
            } else {
                $results = $wpdb->get_row(
                    "SELECT {$select} FROM {$table_name} {$where_clause} {$orderby_clause} {$limit_clause}",
                    $output_type
                ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query has no dynamic value placeholders in this branch.
            }
            $results = $results !== null ? [$results] : [];
        } else {
            if (!empty($where_values)) {
                $results = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT {$select} FROM {$table_name} {$where_clause} {$orderby_clause} {$limit_clause}",
                        $where_values
                    ),
                    $output_type
                ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- identifiers and SQL fragments are validated before assembly.
            } else {
                $results = $wpdb->get_results(
                    "SELECT {$select} FROM {$table_name} {$where_clause} {$orderby_clause} {$limit_clause}",
                    $output_type
                ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query has no dynamic value placeholders in this branch.
            }
        }

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

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $results);

        // Allow filtering results
        $results = apply_filters('ultraguard_pro/get_records_results', $results, $table_key, $args);

        return $results;
    }

    /**
     * Get records with pagination and caching
     *
     * Retrieves paginated records with metadata including total count,
     * page numbers, and navigation information.
     *
     * @param string $table_key Table identifier (e.g., 'security_logs')
     * @param array  $args      Pagination arguments with the following keys:
     *                          - page: Current page number (default: 1)
     *                          - per_page: Records per page (default: 20)
     *                          - where: WHERE conditions as array
     *                          - orderby: Field to order by (default: 'id')
     *                          - order: Sort order 'ASC' or 'DESC' (default: 'DESC')
     *                          - output_type: OBJECT or ARRAY_A (default: OBJECT)
     *
     * @return array Associative array with 'records' and 'pagination' keys
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::get_records()
     * @see    self::get_count()
     *
     * @example
     * $result = DatabaseCrud::get_paginated_records('security_logs', [
     *     'page' => 1,
     *     'per_page' => 10,
     *     'where' => ['severity' => 'warning']
     * ]);
     * echo "Showing page " . $result['pagination']['current_page'] .
     *      " of " . $result['pagination']['total_pages'];
     * foreach ($result['records'] as $record) {
     *     echo $record->message . "\n";
     * }
     */
    public static function get_paginated_records($table_key, $args = []) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return [
                'records' => [],
                'pagination' => []
            ];
        }

        $table_key = sanitize_key($table_key);
        $args = is_array($args) ? $args : [];

        // SECURE: Comprehensive defaults
        $defaults = [
            'page' => 1,
            'per_page' => 20,
            'where' => [],
            'orderby' => 'id',
            'order' => 'DESC',
            'output_type' => OBJECT
        ];

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

        // Extract with validation
        $page = max(1, intval($args['page']));
        $per_page = max(1, intval($args['per_page']));
        $where = $args['where'];
        $orderby = $args['orderby'];
        $order = $args['order'];
        $output_type = in_array($args['output_type'], [OBJECT, ARRAY_A]) ? $args['output_type'] : OBJECT;

        $offset = ($page - 1) * $per_page;

        // Cache key generation
        $cache_key = $table_key . '_paginated_' . md5(serialize($args));
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

        // Get records
        $records = self::get_records($table_key, [
            'where' => $where,
            'orderby' => $orderby,
            'order' => $order,
            'limit' => $per_page,
            'offset' => $offset,
            'output_type' => $output_type
        ]);

        // Get total count (cached)
        $total = self::get_count($table_key, $where);
        $total_pages = $per_page > 0 ? ceil($total / $per_page) : 0;

        $result = [
            'records' => is_array($records) ? $records : [],
            'pagination' => [
                'current_page' => $page,
                'per_page' => $per_page,
                'total_records' => $total,
                'total_pages' => $total_pages,
                'has_previous' => $page > 1,
                'has_next' => $page < $total_pages
            ]
        ];

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $result);

        // Allow filtering paginated results
        $result = apply_filters('ultraguard_pro/get_paginated_records_result', $result, $table_key, $args);

        return $result;
    }

    /**
     * Get count of records with caching
     *
     * Counts records matching WHERE conditions with caching to
     * improve performance for frequently counted queries.
     *
     * @param string $table_key Table identifier (e.g., 'security_logs')
     * @param array  $where     WHERE conditions as associative array
     *
     * @return int Number of matching records
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::get_var()
     *
     * @example
     * $count = DatabaseCrud::get_count('security_logs', ['severity' => 'critical']);
     * echo "Found {$count} critical events";
     */
    public static function get_count($table_key, $where = []) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return 0;
        }

        $table_key = sanitize_key($table_key);
        $where = is_array($where) ? $where : [];

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return 0;
        }

        // Cache key generation
        $cache_key = $table_key . '_count_' . md5(serialize($where));
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return (int) $cached;
        }

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

        if (!empty($where)) {
            $where_parts = [];
            foreach ($where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                if (is_array($value)) {
                    if (empty($value)) {
                        continue;
                    }
                    $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);
            }
        }

        if (!empty($where_values)) {
            $count = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$table_name} {$where_clause}",
                    $where_values
                )
            ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.
        } else {
            $count = $wpdb->get_var(
                "SELECT COUNT(*) FROM {$table_name} {$where_clause}"
            ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query has no dynamic value placeholders in this branch.
        }
        $count = (int) $count;

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $count);

        return $count;
    }

    /**
     * Search records with multiple fields and caching
     *
     * Performs a full-text search across multiple fields with LIKE
     * patterns. Automatically searches common text fields if no
     * specific fields are provided.
     *
     * @param string $table_key        Table identifier (e.g., 'security_logs')
     * @param string $search_term      Term to search for
     * @param array  $search_fields    Specific fields to search (empty = auto-detect)
     * @param array  $additional_where Additional WHERE conditions
     * @param int    $limit            Maximum records to return (default: 50)
     *
     * @return array Array of matching records
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::esc_like()
     * @see    self::column_exists()
     *
     * @example
     * $results = DatabaseCrud::search_records('security_logs', 'injection',
     *     ['message', 'event_type'], ['severity' => 'critical'], 25);
     * foreach ($results as $result) {
     *     echo "Found: " . $result->message . "\n";
     * }
     */
    public static function search_records($table_key, $search_term, $search_fields = [], $additional_where = [], $limit = 50) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return [];
        }

        $table_key = sanitize_key($table_key);
        $search_term = is_string($search_term) ? sanitize_text_field($search_term) : '';
        $search_fields = is_array($search_fields) ? $search_fields : [];
        $additional_where = is_array($additional_where) ? $additional_where : [];
        $limit = intval($limit);

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

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return [];
        }

        // Cache key generation
        $cache_key = $table_key . '_search_' . md5($search_term . serialize($search_fields) . serialize($additional_where) . $limit);
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

        // If no specific fields provided, search in common text fields
        if (empty($search_fields)) {
            $table_definition = self::get_table_definition($table_key);
            $search_fields = [];

            if (is_array($table_definition) && isset($table_definition['columns'])) {
                foreach ($table_definition['columns'] as $column => $definition) {
                    $definition = is_string($definition) ? $definition : '';
                    if (strpos($definition, 'varchar') !== false ||
                        strpos($definition, 'text') !== false) {
                        $search_fields[] = $column;
                    }
                }
            }
        }

        // Validate search fields
        $valid_search_fields = [];
        foreach ($search_fields as $field) {
            $field = is_string($field) ? sanitize_key($field) : '';
            if (self::column_exists($table_key, $field)) {
                $valid_search_fields[] = $field;
            }
        }

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

        // Build search conditions
        $search_conditions = [];
        $search_values = [];

        foreach ($valid_search_fields as $field) {
            $search_conditions[] = "{$field} LIKE %s";
            $search_values[] = '%' . $wpdb->esc_like($search_term) . '%';
        }

        $search_clause = '(' . implode(' OR ', $search_conditions) . ')';

        // Build additional WHERE conditions
        $where_conditions = [$search_clause];
        $where_values = $search_values;

        if (!empty($additional_where)) {
            foreach ($additional_where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                $where_conditions[] = "{$field} = %s";
                $where_values[] = $value;
            }
        }

        $where_clause = 'WHERE ' . implode(' AND ', $where_conditions);

        // Build and execute query
        $query = $wpdb->prepare("
            SELECT * FROM {$table_name}
            {$where_clause}
            ORDER BY id DESC
            LIMIT %d
        ", array_merge($where_values, [$limit]));

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

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

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $results);

        // Allow filtering search results
        $results = apply_filters('ultraguard_pro/search_records_results', $results, $table_key, $search_term);

        return $results;
    }

    /**
     * Get aggregated data (SUM, AVG, COUNT, etc.) with caching
     *
     * Performs aggregate functions (SUM, AVG, COUNT, MIN, MAX) on
     * specified fields with WHERE filtering and caching.
     *
     * @param string $table_key          Table identifier (e.g., 'security_logs')
     * @param string $aggregate_function Aggregate function: COUNT, SUM, AVG, MIN, MAX
     * @param string $aggregate_field    Field to aggregate
     * @param array  $where              WHERE conditions as associative array
     *
     * @return mixed|null Aggregated value or null on error
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wpdb::get_var()
     * @see    self::column_exists()
     *
     * @example
     * $total = DatabaseCrud::get_aggregate('security_logs', 'COUNT', '*');
     * $avg = DatabaseCrud::get_aggregate('security_logs', 'AVG', 'severity_level');
     * $max = DatabaseCrud::get_aggregate('security_logs', 'MAX', 'response_time');
     */
    public static function get_aggregate($table_key, $aggregate_function, $aggregate_field, $where = []) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' ||
            !is_string($aggregate_function) || !is_string($aggregate_field)) {
            return null;
        }

        $table_key = sanitize_key($table_key);
        $aggregate_function = strtoupper($aggregate_function);
        $aggregate_field = sanitize_key($aggregate_field);
        $where = is_array($where) ? $where : [];

        // Get table name with caching
        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return null;
        }

        if (!self::column_exists($table_key, $aggregate_field)) {
            return null;
        }

        // Validate aggregate function
        $valid_functions = ['COUNT', 'SUM', 'AVG', 'MIN', 'MAX'];
        if (!in_array($aggregate_function, $valid_functions)) {
            return null;
        }

        // Cache key generation
        $cache_key = $table_key . '_aggregate_' . $aggregate_function . '_' . $aggregate_field . '_' . md5(serialize($where));
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

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

        if (!empty($where)) {
            $where_parts = [];
            foreach ($where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                $where_parts[] = "{$field} = %s";
                $where_values[] = $value;
            }

            if (!empty($where_parts)) {
                $where_clause = 'WHERE ' . implode(' AND ', $where_parts);
            }
        }

        if (!empty($where_values)) {
            $result = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT {$aggregate_function}({$aggregate_field}) FROM {$table_name} {$where_clause}",
                    $where_values
                )
            ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.
        } else {
            $result = $wpdb->get_var(
                "SELECT {$aggregate_function}({$aggregate_field}) FROM {$table_name} {$where_clause}"
            ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query has no dynamic value placeholders in this branch.
        }

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $result);

        return $result;
    }

    /**
     * Sanitize data for database operations
     *
     * Sanitizes data based on column definitions to prevent SQL injection
     * and ensure data integrity. Uses type-specific sanitization for
     * integers, floats, and text fields.
     *
     * @param string $table_key Table identifier
     * @param array  $data      Raw data to sanitize
     *
     * @return array Sanitized data
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    sanitize_text_field()
     * @see    wp_strip_all_tags()
     * @see    esc_sql()
     * @see    self::get_table_definition()
     */
    private static function sanitize_data($table_key, $data) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_array($data) || empty($data)) {
            return [];
        }

        $table_key = sanitize_key($table_key);
        $sanitized = [];
        $table_definition = self::get_table_definition($table_key);

        if (!is_array($table_definition) || !isset($table_definition['columns'])) {
            return $data;
        }

        foreach ($data as $key => $value) {
            $key = is_string($key) ? sanitize_key($key) : '';

            // Skip if column doesn't exist
            if (!isset($table_definition['columns'][$key])) {
                continue;
            }

            $column_definition = $table_definition['columns'][$key];
            $column_definition = is_string($column_definition) ? $column_definition : '';

            // Type-specific sanitization
            if (strpos($column_definition, 'int') !== false) {
                $sanitized[$key] = intval($value);
            } elseif (strpos($column_definition, 'decimal') !== false ||
                      strpos($column_definition, 'float') !== false) {
                $sanitized[$key] = floatval($value);
            } elseif (strpos($column_definition, 'text') !== false ||
                      strpos($column_definition, 'varchar') !== false) {
                // Security: Sanitize text fields.
                // NOTE: Do NOT call esc_sql() here. All callers (insert/update/delete)
                // pass data through $wpdb->prepare() or $wpdb->insert/update/delete which
                // apply prepared-statement escaping internally. A second esc_sql() call
                // would double-escape apostrophes and backslashes, corrupting stored data.
                $sanitized[$key] = sanitize_text_field(strval($value));
                $sanitized[$key] = wp_strip_all_tags($sanitized[$key]);
            } else {
                $sanitized[$key] = $value;
            }
        }

        return $sanitized;
    }

    /**
     * Get format specifiers for wpdb operations
     *
     * Determines the appropriate format specifiers (%s, %d, %f) for
     * wpdb operations based on column definitions.
     *
     * @param string $table_key Table identifier
     * @param array  $data      Data to determine formats for
     *
     * @return array Array of format specifiers
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::get_table_definition()
     */
    private static function get_format_specifiers($table_key, $data) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_array($data) || empty($data)) {
            return array_fill(0, count($data), '%s');
        }

        $table_key = sanitize_key($table_key);
        $formats = [];
        $table_definition = self::get_table_definition($table_key);

        if (!is_array($table_definition) || !isset($table_definition['columns'])) {
            return array_fill(0, count($data), '%s');
        }

        foreach (array_keys($data) as $key) {
            $key = is_string($key) ? sanitize_key($key) : '';

            if (!isset($table_definition['columns'][$key])) {
                $formats[] = '%s';
                continue;
            }

            $column_definition = $table_definition['columns'][$key];
            $column_definition = is_string($column_definition) ? $column_definition : '';

            if (strpos($column_definition, 'int') !== false) {
                $formats[] = '%d';
            } elseif (strpos($column_definition, 'decimal') !== false ||
                      strpos($column_definition, 'float') !== false) {
                $formats[] = '%f';
            } else {
                $formats[] = '%s';
            }
        }

        return $formats;
    }

    /**
     * Validate required fields for table
     *
     * Checks if all required (NOT NULL with no DEFAULT) fields are
     * present in the provided data.
     *
     * @param string $table_key Table identifier
     * @param array  $data      Data to validate
     *
     * @return bool True if all required fields are present, false otherwise
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::get_table_definition()
     */
    private static function validate_required_fields($table_key, $data) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_array($data)) {
            return false;
        }

        $table_key = sanitize_key($table_key);
        $table_definition = self::get_table_definition($table_key);

        if (!is_array($table_definition) || !isset($table_definition['columns'])) {
            return false;
        }

        foreach ($table_definition['columns'] as $column => $definition) {
            $definition = is_string($definition) ? $definition : '';

            // Check if column is required (NOT NULL and no default value)
            if (strpos($definition, 'NOT NULL') !== false &&
                strpos($definition, 'DEFAULT') === false &&
                $column !== 'id') {

                if (!isset($data[$column]) || empty($data[$column])) {
                    return false;
                }
            }
        }

        return true;
    }

    /**
     * Get validated table name from cache
     *
     * Retrieves table name from cache or DatabaseSchema, validating
     * and sanitizing the table name for security.
     *
     * @param string $table_key Table identifier
     *
     * @return string|false Validated table name or false on error
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    DatabaseSchema::get_table_names()
     */
    private static function get_validated_table_name($table_key) {
        // Check cache first
        if (isset(self::$schema_cache['table_names'][$table_key])) {
            return self::$schema_cache['table_names'][$table_key];
        }

        $table_name = false;
        $tables = DatabaseSchema::get_table_names();

        if ( is_array( $tables ) && isset( $tables[ $table_key ] ) ) {
            $table_name = $tables[ $table_key ];
        } elseif ( is_string( $table_key ) && strpos( $table_key, 'ultraguard_' ) === 0 ) {
            // Bootstrap-safe fallback for module-owned UltraGuard tables during
            // activation, before the live module registry has finished booting.
            $table_name = DatabaseSchema::get_table_name( $table_key );
        }

        if ( ! $table_name ) {
            return false;
        }

        // Security: Validate table name
        $table_name = preg_replace('/[^a-zA-Z0-9_]/', '', $table_name);

        // Cache the result
        if (!isset(self::$schema_cache['table_names'])) {
            self::$schema_cache['table_names'] = [];
        }
        self::$schema_cache['table_names'][$table_key] = $table_name;

        return $table_name;
    }

    /**
     * Validate select fields for security
     *
     * Validates and sanitizes SELECT field names to prevent SQL injection.
     * Supports field names, aliases, and aggregate functions.
     *
     * @param string $table_key     Table identifier
     * @param string $select_fields Comma-separated field list
     *
     * @return string Validated field list or '*' on error
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::column_exists()
     */
    private static function validate_select_fields($table_key, $select_fields) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($select_fields) || $select_fields === '') {
            return '*';
        }

        $table_key = sanitize_key($table_key);
        $valid_fields = [];
        $fields = explode(',', $select_fields);

        foreach ($fields as $field) {
            $field = trim($field);

            // Allow aggregate functions and aliases
            if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*(\s+as\s+[a-zA-Z_][a-zA-Z0-9_]*)?$/i', $field) ||
                preg_match('/^(COUNT|SUM|AVG|MIN|MAX)\([a-zA-Z_*][a-zA-Z0-9_]*\)(\s+as\s+[a-zA-Z_][a-zA-Z0-9_]*)?$/i', $field)) {
                $valid_fields[] = $field;
                continue;
            }

            // Validate regular field names
            if (self::column_exists($table_key, $field)) {
                $valid_fields[] = $field;
            }
        }

        return empty($valid_fields) ? '*' : implode(', ', $valid_fields);
    }

    /**
     * Validate orderby field
     *
     * Validates and sanitizes ORDER BY field names to prevent SQL injection.
     * Supports table aliases in field names.
     *
     * @param string $table_key Table identifier
     * @param string $orderby   Field to order by
     *
     * @return string Validated field name or 'id' on error
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::column_exists()
     */
    private static function validate_orderby_field($table_key, $orderby) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($orderby) || $orderby === '') {
            return 'id';
        }

        $table_key = sanitize_key($table_key);
        $orderby = $orderby;

        // Allow complex orderby with table aliases
        if (strpos($orderby, '.') !== false) {
            $parts = explode('.', $orderby);
            $field = end($parts);
        } else {
            $field = $orderby;
        }

        // Remove any potential SQL injection
        $field = preg_replace('/[^a-zA-Z0-9_]/', '', $field);

        if (self::column_exists($table_key, $field)) {
            return $orderby;
        }

        // Default to safe field
        return 'id';
    }

    /**
     * Check if column exists with caching
     *
     * Checks if a column exists in a table with caching to reduce
     * repeated schema queries.
     *
     * @param string $table_key Table identifier
     * @param string $column    Column name to check
     *
     * @return bool True if column exists, false otherwise
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    DatabaseSchema::column_exists()
     */
    private static function column_exists($table_key, $column) {
        // Check cache first
        $cache_key = 'column_exists_' . $table_key . '_' . $column;
        if (isset(self::$schema_cache[$cache_key])) {
            return self::$schema_cache[$cache_key];
        }

        $exists = DatabaseSchema::column_exists($table_key, $column);

        // Cache the result
        self::$schema_cache[$cache_key] = $exists;

        return $exists;
    }

    /**
     * Get table definition with caching
     *
     * Retrieves table definition (columns, indexes, etc.) from
     * DatabaseSchema with caching to reduce repeated queries.
     *
     * @param string $table_key Table identifier
     *
     * @return array Table definition or empty array on error
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    DatabaseSchema::get_table_definition()
     */
    private static function get_table_definition($table_key) {
        // Check cache first
        if (isset(self::$schema_cache['table_definitions'][$table_key])) {
            return self::$schema_cache['table_definitions'][$table_key];
        }

        $definition = DatabaseSchema::get_table_definition($table_key);

        // Cache the result
        if (!isset(self::$schema_cache['table_definitions'])) {
            self::$schema_cache['table_definitions'] = [];
        }
        self::$schema_cache['table_definitions'][$table_key] = $definition;

        return $definition;
    }

    /**
     * Handle database errors
     *
     * Centralized error handling for database operations with
     * batch mode support, WordPress error logging, and file logging.
     *
     * @param string $message      Error message
     * @param bool   $log_to_file  Whether to log to file (if enabled)
     *
     * @return void
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    error_log()
     * @see    file_put_contents()
     */
    private static function handle_database_error($message, $log_to_file = true) {
        global $wpdb;

        // SECURE: Safe parameter validation
        $message = is_string($message) ? $message : __('Unknown database error', 'ultraguard-security-suite');

        $full_message = 'Ultraguard Pro Database Error: ' . $message;

        if (!empty($wpdb->last_error)) {
            $full_message .= ' | MySQL Error: ' . $wpdb->last_error;
        }

        if (self::$batch_mode) {
            self::$batch_errors[] = $full_message;
        } else {
            self::$errors[] = $full_message;
        }

        // Log to the PHP error log only in debug mode. Do not route database
        // failures back through the DB-backed plugin logger or they can recurse.
        if (defined('WP_DEBUG') && WP_DEBUG) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Database bootstrap/runtime errors must avoid the DB-backed logger to prevent recursion.
            error_log( '[UltraGuard Pro][ERROR] ' . $full_message );
        }

        // Fire error handling hook
        do_action('ultraguard_pro/database_crud_error', $full_message, $message, $wpdb->last_error);

        // Log to security log file if enabled
        if ($log_to_file && defined('ULTRAGUARD_PRO_LOG_DB_ERRORS') && ULTRAGUARD_PRO_LOG_DB_ERRORS) {
            $log_file = WP_CONTENT_DIR . '/ultraguard-security-suite-db-errors.log';
            $timestamp = current_time('mysql');
            // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_is_writable -- lightweight writability probe for optional debug logging.
            if (is_writable(dirname($log_file))) {
                @file_put_contents($log_file, "[{$timestamp}] {$full_message}\n", FILE_APPEND | LOCK_EX);
            }
        }
    }

    /**
     * Start batch error collection
     *
     * Enters batch mode for error collection during bulk operations.
     * In batch mode, errors are collected separately to avoid
     * polluting the main error array.
     *
     * @return void
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::end_batch()
     */
    private static function start_batch() {
        self::$batch_mode = true;
        self::$batch_errors = [];
    }

    /**
     * End batch error collection
     *
     * Exits batch mode and consolidates batch errors into the main
     * error array if any errors occurred.
     *
     * @return void
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    self::start_batch()
     */
    private static function end_batch() {
        if (!empty(self::$batch_errors)) {
            self::$errors[] = __('Batch operation errors: ', 'ultraguard-security-suite') . implode('; ', self::$batch_errors);
        }
        self::$batch_mode = false;
        self::$batch_errors = [];
    }

    /**
     * Check user capabilities for database operations
     *
     * Verifies that the current user has sufficient permissions to
     * perform database operations. Allows cron and AJAX requests.
     *
     * @return bool True if user has required capabilities, false otherwise
     * @access private
     * @static
     * @since  1.2.0
     *
     * @see    current_user_can()
     * @see    wp_doing_cron()
     * @see    wp_doing_ajax()
     */
    private static function check_capabilities() {
        // 1. Logged-in admin — always allowed.
        if ( function_exists( '\\UltraguardPro\\ultraguard_current_user_can_manage' ) && \UltraguardPro\ultraguard_current_user_can_manage() ) {
            return true;
        }

        // 2. WP-Cron — runs without a user context but is trusted server-side.
        if ( wp_doing_cron() ) {
            return true;
        }

        // 3. WordPress login hooks (wp_login_failed, wp_login, login_init) fire
        //    BEFORE the user is authenticated, so current_user_can() is always false
        //    at that point.  We detect this by checking whether execution is currently
        //    inside the wp-login.php request flow.  Security modules that log attempts
        //    or create lockouts MUST be allowed to write records during this phase.
        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Server path is unslashed and normalized immediately for an internal login-flow path check.
        $script = wp_normalize_path( wp_unslash( $_SERVER['SCRIPT_FILENAME'] ?? '' ) );
        $login  = wp_normalize_path( ABSPATH . 'wp-login.php' );
        if ( $script && $script === $login ) {
            return true;
        }
        // Also cover Nginx-style setups that route through index.php
        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Request URI is unslashed and only inspected for the internal wp-login.php segment.
        $uri = wp_unslash( $_SERVER['REQUEST_URI'] ?? '' );
        if ( $uri && str_contains( $uri, '/wp-login.php' ) ) {
            return true;
        }

        // 4. AJAX requests — the calling handler has already verified nonce + capability.
        //    Allowing writes here does NOT bypass security: the AJAX handler performs
        //    check_ajax_referer() + current_user_can('manage_options') before any DB call.
        if ( wp_doing_ajax() ) {
            return true;
        }

        return false;
    }

    /**
     * Get last error
     *
     * Returns the most recent error message from the error stack.
     *
     * @return string Last error message or empty string if no errors
     * @access public
     * @static
     * @since  1.2.0
     *
     * @example
     * $id = DatabaseCrud::insert('security_logs', []);
     * if (!$id) {
     *     echo "Error: " . DatabaseCrud::get_last_error();
     * }
     */
    public static function get_last_error() {
        return !empty(self::$errors) ? end(self::$errors) : '';
    }

    /**
     * Get single row with conditions
     *
     * Convenience method to get a single record matching WHERE conditions.
     *
     * @param string $table_key   Table identifier
     * @param array  $where       WHERE conditions
     * @param string $output_type Output format (OBJECT or ARRAY_A)
     *
     * @return object|array|null Record or null if not found
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::get_records()
     *
     * @example
     * $row = DatabaseCrud::get_row('security_logs', [
     *     'event_type' => 'firewall',
     *     'severity' => 'critical'
     * ]);
     */
    public static function get_row($table_key, $where = [], $output_type = OBJECT) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return null;
        }

        $table_key = sanitize_key($table_key);
        $where = is_array($where) ? $where : [];
        $output_type = in_array($output_type, [OBJECT, ARRAY_A]) ? $output_type : OBJECT;

        $results = self::get_records($table_key, [
            'where' => $where,
            'limit' => 1,
            'output_type' => $output_type
        ]);

        return !empty($results) ? $results[0] : null;
    }

    /**
     * Check if record exists
     *
     * Checks if any records exist matching WHERE conditions.
     *
     * @param string $table_key Table identifier
     * @param array  $where     WHERE conditions
     *
     * @return bool True if at least one matching record exists
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::get_count()
     *
     * @example
     * if (DatabaseCrud::record_exists('security_logs', ['ip_address' => '192.168.1.1'])) {
     *     echo "IP address exists in logs";
     * }
     */
    public static function record_exists($table_key, $where = []) {
        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '') {
            return false;
        }

        $table_key = sanitize_key($table_key);
        $where = is_array($where) ? $where : [];

        return self::get_count($table_key, $where) > 0;
    }

    /**
     * Increment field value
     *
     * Atomically increments a numeric field value for records
     * matching WHERE conditions.
     *
     * @param string $table_key Table identifier
     * @param string $field     Field to increment
     * @param array  $where     WHERE conditions
     * @param int    $increment Amount to increment by (default: 1)
     *
     * @return bool|int Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @throws \Exception If field doesn't exist or operation fails
     * @see    self::clear_table_cache()
     *
     * @example
     * $affected = DatabaseCrud::increment_field('login_attempts',
     *     'attempt_count', ['user_id' => 123]);
     */
    public static function increment_field($table_key, $field, $where = [], $increment = 1) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($field) || $field === '') {
            return false;
        }

        $table_key = sanitize_key($table_key);
        $field = sanitize_key($field);
        $where = is_array($where) ? $where : [];
        $increment = intval($increment);

        try {
            $table_name = self::get_validated_table_name($table_key);
            if (!$table_name) {
                throw new \Exception(__('Invalid table name', 'ultraguard-security-suite'));
            }

            if (!self::column_exists($table_key, $field)) {
                throw new \Exception(__('Invalid field specified', 'ultraguard-security-suite'));
            }

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

            if (!empty($where)) {
                $where_parts = [];
                foreach ($where as $where_field => $value) {
                    $where_field = is_string($where_field) ? sanitize_key($where_field) : '';
                    if (!self::column_exists($table_key, $where_field)) {
                        continue;
                    }

                    $where_parts[] = "{$where_field} = %s";
                    $where_values[] = $value;
                }

                if (!empty($where_parts)) {
                    $where_clause = 'WHERE ' . implode(' AND ', $where_parts);
                }
            }

            array_unshift($where_values, $increment);

            $result = $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$table_name} SET {$field} = {$field} + %d {$where_clause}",
                    $where_values
                )
            ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.

            if ($result === false) {
                $error_message = !empty($wpdb->last_error) ? $wpdb->last_error : __('Unknown database error', 'ultraguard-security-suite');
                /* translators: %s: string */
                throw new \Exception(sprintf(__('Increment field operation failed: %s', 'ultraguard-security-suite'), $error_message));
            }

            // Clear relevant caches
            self::clear_table_cache($table_key);

            return $result;

        } catch (\Exception $e) {
            self::handle_database_error($e->getMessage());
            return false;
        }
    }

    /**
     * Decrement field value
     *
     * Atomically decrements a numeric field value for records
     * matching WHERE conditions.
     *
     * @param string $table_key Table identifier
     * @param string $field     Field to decrement
     * @param array  $where     WHERE conditions
     * @param int    $decrement Amount to decrement by (default: 1)
     *
     * @return bool|int Number of affected rows or false on failure
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    self::increment_field()
     */
    public static function decrement_field($table_key, $field, $where = [], $decrement = 1) {
        // SECURE: Safe parameter validation
        $decrement = intval($decrement);
        return self::increment_field($table_key, $field, $where, -$decrement);
    }

    /**
     * Get all errors
     *
     * Returns all error messages collected during operations.
     *
     * @return string[] Array of error messages
     * @access public
     * @static
     * @since  1.2.0
     *
     * @example
     * $errors = DatabaseCrud::get_errors();
     * if (!empty($errors)) {
     *     echo "Errors occurred:\n";
     *     foreach ($errors as $error) {
     *         echo "- {$error}\n";
     *     }
     * }
     */
    public static function get_errors() {
        return self::$errors;
    }

    /**
     * Clear errors
     *
     * Clears all collected error messages.
     *
     * @return void
     * @access public
     * @static
     * @since  1.2.0
     */
    public static function clear_errors() {
        self::$errors = [];
    }

    /**
     * Check if last operation was successful
     *
     * Checks if any errors were recorded during the last operation.
     *
     * @return bool True if no errors occurred, false otherwise
     * @access public
     * @static
     * @since  1.2.0
     *
     * @example
     * $id = DatabaseCrud::insert('security_logs', $data);
     * if (DatabaseCrud::is_success()) {
     *     echo "Operation succeeded";
     * } else {
     *     echo "Operation failed";
     * }
     */
    public static function is_success() {
        return empty(self::$errors);
    }

    /**
     * Get grouped count of records by field with caching
     *
     * Groups records by a field and returns counts for each group,
     * sorted by count in descending order.
     *
     * @param string $table_key   Table identifier
     * @param string $group_field Field to group by
     * @param array  $where       WHERE conditions
     * @param int    $limit       Maximum groups to return (default: 10)
     *
     * @return array[] Array of objects with group_field and count properties
     * @access public
     * @static
     * @since  1.2.0
     *
     * @example
     * $groups = DatabaseCrud::get_grouped_count('security_logs', 'severity');
     * foreach ($groups as $group) {
     *     echo "Severity {$group->severity}: {$group->count} events\n";
     * }
     */
    public static function get_grouped_count($table_key, $group_field, $where = [], $limit = 10) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($group_field) || $group_field === '') {
            return [];
        }

        $table_key = sanitize_key($table_key);
        $group_field = sanitize_key($group_field);
        $where = is_array($where) ? $where : [];
        $limit = intval($limit);

        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return [];
        }

        if (!self::column_exists($table_key, $group_field)) {
            return [];
        }

        // Cache key generation
        $cache_key = $table_key . '_grouped_count_' . $group_field . '_' . md5(serialize($where)) . '_' . $limit;
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

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

        if (!empty($where)) {
            $where_parts = [];
            foreach ($where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                $where_parts[] = "{$field} = %s";
                $where_values[] = $value;
            }

            if (!empty($where_parts)) {
                $where_clause = 'WHERE ' . implode(' AND ', $where_parts);
            }
        }

        array_push($where_values, $limit);
        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$group_field}, COUNT(*) as count
                 FROM {$table_name}
                 {$where_clause}
                 GROUP BY {$group_field}
                 ORDER BY count DESC
                 LIMIT %d",
                $where_values
            ),
            OBJECT
        ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.

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

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $results);

        return $results;
    }

    /**
     * Get daily count of records for a date field with caching
     *
     * Groups records by date (extracted from a datetime field) and
     * returns counts for each day, sorted by date descending.
     *
     * @param string $table_key  Table identifier
     * @param string $date_field Datetime field to extract date from
     * @param array  $where      WHERE conditions
     *
     * @return array[] Array of objects with date and count properties
     * @access public
     * @static
     * @since  1.2.0
     *
     * @example
     * $daily_counts = DatabaseCrud::get_daily_count('security_logs', 'created_at');
     * foreach ($daily_counts as $day) {
     *     echo "Date {$day->date}: {$day->count} events\n";
     * }
     */
    public static function get_daily_count($table_key, $date_field, $where = []) {
        global $wpdb;

        // SECURE: Safe parameter validation with early returns
        if (!is_string($table_key) || $table_key === '' || !is_string($date_field) || $date_field === '') {
            return [];
        }

        $table_key = sanitize_key($table_key);
        $date_field = sanitize_key($date_field);
        $where = is_array($where) ? $where : [];

        $table_name = self::get_validated_table_name($table_key);
        if (!$table_name) {
            return [];
        }

        if (!self::column_exists($table_key, $date_field)) {
            return [];
        }

        // Cache key generation
        $cache_key = $table_key . '_daily_count_' . $date_field . '_' . md5(serialize($where));
        $cache_group = 'ultraguard_crud';
        $cached = CacheManager::get_instance()->get_db($cache_key);

        if ($cached !== false) {
            return $cached;
        }

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

        if (!empty($where)) {
            $where_parts = [];
            foreach ($where as $field => $value) {
                $field = is_string($field) ? sanitize_key($field) : '';
                if (!self::column_exists($table_key, $field)) {
                    continue;
                }

                $where_parts[] = "{$field} = %s";
                $where_values[] = $value;
            }

            if (!empty($where_parts)) {
                $where_clause = 'WHERE ' . implode(' AND ', $where_parts);
            }
        }

        if (!empty($where_values)) {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT DATE({$date_field}) as date, COUNT(*) as count
                     FROM {$table_name}
                     {$where_clause}
                     GROUP BY DATE({$date_field})
                     ORDER BY date DESC",
                    $where_values
                ),
                OBJECT
            ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers and SQL fragments are validated before assembly.
        } else {
            $results = $wpdb->get_results(
                "SELECT DATE({$date_field}) as date, COUNT(*) as count
                 FROM {$table_name}
                 {$where_clause}
                 GROUP BY DATE({$date_field})
                 ORDER BY date DESC",
                OBJECT
            ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query has no dynamic value placeholders in this branch.
        }

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

        // Cache via CacheManager
        CacheManager::get_instance()->set_db($cache_key, $results);

        return $results;
    }

    /**
     * Clear cache for a specific table
     *
     * Clears WordPress object cache entries for a specific table.
     * Used after insert/update/delete operations to ensure cache consistency.
     *
     * @param string $table_key Table identifier
     *
     * @return void
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wp_cache_flush_group()
     */
    public static function clear_table_cache($table_key) {
        // Route through CacheManager for unified invalidation
        CacheManager::get_instance()->invalidate_db(is_string($table_key) ? $table_key : null);
        // Also clear local schema cache
        self::$schema_cache = [];
    }

    /**
     * Clear all CRUD cache
     *
     * Clears all cache entries used by DatabaseCrud operations,
     * including schema cache and WordPress object cache.
     *
     * @return void
     * @access public
     * @static
     * @since  1.2.0
     *
     * @see    wp_cache_flush_group()
     */
    public static function clear_all_cache() {
        self::$schema_cache = [];
        CacheManager::get_instance()->invalidate_db();
    }

    // =========================================================================
    // EXTENDED API — time-range, expiry-aware, column, and raw-insert helpers
    // =========================================================================

    /**
     * COUNT(*) with exact equality WHERE conditions PLUS a lower-bound datetime filter.
     *
     * Replaces direct $wpdb queries like:
     *   SELECT COUNT(*) FROM t WHERE ip=? AND status='failed' AND created_at >= ?
     *
     * @param string $table_key   Table identifier.
     * @param array  $where       Exact equality conditions (field => value).
     * @param string $date_field  Datetime column to filter on.
     * @param string $since       Lower-bound datetime string (inclusive, >=).
     * @return int
     */
    public static function count_since(
        string $table_key,
        array  $where,
        string $date_field,
        string $since
    ): int {
        global $wpdb;

        $table_key = sanitize_key( $table_key );
        $table     = self::get_validated_table_name( $table_key );
        if ( ! $table ) { return 0; }

        $date_field = sanitize_key( $date_field );
        if ( ! self::column_exists( $table_key, $date_field ) ) { return 0; }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        $parts[]  = "{$date_field} >= %s";
        $values[] = $since;

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_count_since_' . md5( serialize( $where ) . $date_field . $since );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (int) $cached; }

        $count = (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); table name and SQL fragments are built from validated identifiers only.
            "SELECT COUNT(*) FROM `{$table}` {$where_sql}", ...$values
        ) );

        CacheManager::get_instance()->set_db( $cache_key, $count );
        return $count;
    }

    /**
     * COUNT(DISTINCT field) with exact equality WHERE conditions plus a lower-bound datetime filter.
     *
     * @param string $table_key      Table identifier.
     * @param string $distinct_field Column to count distinctly.
     * @param array  $where          Exact equality conditions.
     * @param string $date_field     Datetime column to filter on.
     * @param string $since          Lower-bound datetime string (inclusive, >=).
     * @return int
     */
    public static function count_distinct_since(
        string $table_key,
        string $distinct_field,
        array  $where,
        string $date_field,
        string $since
    ): int {
        global $wpdb;

        $table_key      = sanitize_key( $table_key );
        $distinct_field = sanitize_key( $distinct_field );
        $date_field     = sanitize_key( $date_field );
        $table          = self::get_validated_table_name( $table_key );

        if ( ! $table ) { return 0; }
        if ( ! self::column_exists( $table_key, $distinct_field ) || ! self::column_exists( $table_key, $date_field ) ) {
            return 0;
        }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        $parts[]  = "{$date_field} >= %s";
        $values[] = $since;

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_count_distinct_since_' . md5( $distinct_field . serialize( $where ) . $date_field . $since );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );

        if ( false !== $cached ) {
            return (int) $cached;
        }

        $count = (int) $wpdb->get_var(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT COUNT(DISTINCT `{$distinct_field}`) FROM `{$table}` {$where_sql}",
                ...$values
            )
        );

        CacheManager::get_instance()->set_db( $cache_key, $count );

        return $count;
    }

    /**
     * AVG(field) with exact equality WHERE conditions plus a lower-bound datetime filter.
     *
     * @param string $table_key   Table identifier.
     * @param string $avg_field   Numeric column to average.
     * @param array  $where       Exact equality conditions.
     * @param string $date_field  Datetime column to filter on.
     * @param string $since       Lower-bound datetime string (inclusive, >=).
     * @param int    $min_value   Optional minimum numeric value.
     * @return float
     */
    public static function average_since(
        string $table_key,
        string $avg_field,
        array  $where,
        string $date_field,
        string $since,
        int $min_value = 0
    ): float {
        global $wpdb;

        $table_key  = sanitize_key( $table_key );
        $avg_field  = sanitize_key( $avg_field );
        $date_field = sanitize_key( $date_field );
        $table      = self::get_validated_table_name( $table_key );

        if ( ! $table ) { return 0.0; }
        if ( ! self::column_exists( $table_key, $avg_field ) || ! self::column_exists( $table_key, $date_field ) ) {
            return 0.0;
        }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        $parts[]  = "{$date_field} >= %s";
        $values[] = $since;

        if ( $min_value > 0 ) {
            $parts[]  = "{$avg_field} >= %d";
            $values[] = $min_value;
        }

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_avg_since_' . md5( $avg_field . serialize( $where ) . $date_field . $since . $min_value );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );

        if ( false !== $cached ) {
            return (float) $cached;
        }

        $avg = (float) $wpdb->get_var(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT AVG(`{$avg_field}`) FROM `{$table}` {$where_sql}",
                ...$values
            )
        );

        CacheManager::get_instance()->set_db( $cache_key, $avg );

        return $avg;
    }

    /**
     * Return TRUE if at least one row matches $where AND the optional expiry
     * field has not yet passed.
     *
     * Handles both nullable expiry (NULL = never expires) and non-nullable
     * (always compared against the current UTC time).
     *
     * SQL generated:
     *   WHERE col1=? ... AND (expire_field IS NULL OR expire_field >= ?)
     *
     * @param string      $table_key    Table identifier.
     * @param array       $where        Exact equality conditions.
     * @param string|null $expire_field Optional datetime column to check for expiry.
     * @return bool
     */
    public static function record_exists_not_expired(
        string  $table_key,
        array   $where,
        ?string $expire_field = null
    ): bool {
        global $wpdb;

        $table_key = sanitize_key( $table_key );
        $table     = self::get_validated_table_name( $table_key );
        if ( ! $table ) { return false; }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        if ( $expire_field ) {
            $expire_field = sanitize_key( $expire_field );
            if ( self::column_exists( $table_key, $expire_field ) ) {
                $now      = gmdate( 'Y-m-d H:i:s' );
                $parts[]  = "({$expire_field} IS NULL OR {$expire_field} >= %s)";
                $values[] = $now;
            }
        }

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

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_rene_' . md5( serialize( $where ) . (string) $expire_field );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (bool) $cached; }

        $result = (bool) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); table name and SQL fragments are built from validated identifiers only.
            "SELECT id FROM `{$table}` {$where_sql} LIMIT 1", ...$values
        ) );

        CacheManager::get_instance()->set_db( $cache_key, (int) $result );
        return $result;
    }

    /**
     * Return a single ARRAY_A row that matches $where AND has not expired,
     * using the same nullable-expiry logic as record_exists_not_expired().
     *
     * Returns null when no matching row is found.
     *
     * @param string      $table_key    Table identifier.
     * @param array       $where        Exact equality conditions.
     * @param string|null $expire_field Optional expiry datetime column.
     * @return array|null
     */
    public static function get_row_not_expired(
        string  $table_key,
        array   $where,
        ?string $expire_field = null
    ): ?array {
        global $wpdb;

        $table_key = sanitize_key( $table_key );
        $table     = self::get_validated_table_name( $table_key );
        if ( ! $table ) { return null; }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        if ( $expire_field ) {
            $expire_field = sanitize_key( $expire_field );
            if ( self::column_exists( $table_key, $expire_field ) ) {
                $now      = gmdate( 'Y-m-d H:i:s' );
                $parts[]  = "({$expire_field} IS NULL OR {$expire_field} >= %s)";
                $values[] = $now;
            }
        }

        if ( empty( $parts ) ) { return null; }

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_rown_' . md5( serialize( $where ) . (string) $expire_field );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return is_array( $cached ) ? $cached : null; }

        $row = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); table name and SQL fragments are built from validated identifiers only.
            "SELECT * FROM `{$table}` {$where_sql} LIMIT 1", ...$values
        ), ARRAY_A );

        CacheManager::get_instance()->set_db( $cache_key, $row ?? [] );
        return is_array( $row ) ? $row : null;
    }

    /**
     * Fetch all values of a single column as a flat PHP array.
     *
     * Replaces: $wpdb->get_col("SELECT col FROM table WHERE ...")
     *
     * @param string $table_key Table identifier.
     * @param string $field     Column to retrieve.
     * @param array  $args {
     *     Optional query modifiers.
     *     @type array       $where        Exact equality WHERE conditions.
     *     @type string|null $expire_field Optional nullable-expiry column filter.
     *     @type int         $limit        Maximum rows (0 = no limit).
     *     @type string      $orderby      ORDER BY column (default 'id').
     *     @type string      $order        'ASC' or 'DESC'.
     * }
     * @return array  Flat array of column values.
     */
    public static function get_column(
        string $table_key,
        string $field,
        array  $args = []
    ): array {
        global $wpdb;

        $table_key = sanitize_key( $table_key );
        $field     = sanitize_key( $field );
        $table     = self::get_validated_table_name( $table_key );

        if ( ! $table || ! self::column_exists( $table_key, $field ) ) {
            return [];
        }

        $where        = $args['where']        ?? [];
        $expire_field = $args['expire_field'] ?? null;
        $limit        = max( 0, (int) ( $args['limit'] ?? 0 ) );
        $orderby      = sanitize_key( $args['orderby'] ?? 'id' );
        $order        = strtoupper( $args['order'] ?? 'ASC' ) === 'DESC' ? 'DESC' : 'ASC';

        $parts  = [];
        $values = [];

        foreach ( (array) $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        if ( $expire_field ) {
            $ef = sanitize_key( $expire_field );
            if ( self::column_exists( $table_key, $ef ) ) {
                $now      = gmdate( 'Y-m-d H:i:s' );
                $parts[]  = "({$ef} IS NULL OR {$ef} >= %s)";
                $values[] = $now;
            }
        }

        $where_sql   = $parts ? 'WHERE ' . implode( ' AND ', $parts ) : '';
        $orderby_sql = self::column_exists( $table_key, $orderby )
                       ? "ORDER BY {$orderby} {$order}"
                       : '';
        $limit_sql   = $limit > 0 ? "LIMIT %d" : '';

        if ( $limit > 0 ) { $values[] = $limit; }

        $sql = trim( "SELECT `{$field}` FROM `{$table}` {$where_sql} {$orderby_sql} {$limit_sql}" );

        $cache_key = $table_key . '_getcol_' . md5( $sql . serialize( $values ) );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (array) $cached; }

        $rows = $values
            ? $wpdb->get_col( $wpdb->prepare( $sql, ...$values ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); SQL built from validated identifiers only.
            : $wpdb->get_col( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input; SQL built from validated identifiers only.

        $rows = is_array( $rows ) ? $rows : [];
        CacheManager::get_instance()->set_db( $cache_key, $rows );
        return $rows;
    }

    /**
     * GROUP BY a field, count occurrences, filtered to rows created since a
     * given datetime cutoff.  Returns the top-N groups ordered by count DESC.
     *
     * Replaces queries like:
     *   SELECT group_field, COUNT(*) as count FROM t
     *   WHERE status=? AND created_at >= ?
     *   GROUP BY group_field ORDER BY count DESC LIMIT N
     *
     * @param string $table_key   Table identifier.
     * @param string $group_field Column to GROUP BY.
     * @param array  $where       Exact equality conditions applied before the GROUP.
     * @param string $date_field  Datetime column for the time-window filter.
     * @param string $cutoff      Lower-bound datetime (inclusive).
     * @param int    $limit       Max rows (default 10).
     * @return array  Each element is an ARRAY_A with the group_field value and 'count'.
     */
    public static function get_grouped_count_since(
        string $table_key,
        string $group_field,
        array  $where,
        string $date_field,
        string $cutoff,
        int    $limit = 10
    ): array {
        global $wpdb;

        $table_key   = sanitize_key( $table_key );
        $group_field = sanitize_key( $group_field );
        $date_field  = sanitize_key( $date_field );
        $table       = self::get_validated_table_name( $table_key );

        if ( ! $table
             || ! self::column_exists( $table_key, $group_field )
             || ! self::column_exists( $table_key, $date_field ) ) {
            return [];
        }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        $parts[]  = "{$date_field} >= %s";
        $values[] = $cutoff;
        $values[] = max( 1, $limit );

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_ggcs_' . md5( $group_field . serialize( $where ) . $date_field . $cutoff . $limit );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (array) $cached; }

        $rows = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); identifiers are validated before SQL assembly.
            "SELECT `{$group_field}`, COUNT(*) AS count
               FROM `{$table}` {$where_sql}
              GROUP BY `{$group_field}`
              ORDER BY count DESC
              LIMIT %d",
            ...$values
        ), ARRAY_A );

        $rows = is_array( $rows ) ? $rows : [];
        CacheManager::get_instance()->set_db( $cache_key, $rows );
        return $rows;
    }

    /**
     * Get records where a datetime field is before a given cutoff.
     *
     * @param string $table_key    Table identifier.
     * @param string $date_field   Datetime column to compare.
     * @param string $cutoff       Upper-bound datetime (strictly before).
     * @param array  $where        Exact equality conditions.
     * @param int    $limit        Max rows to return.
     * @param string $orderby      Order-by column.
     * @param string $order        Sort direction.
     * @param string $output_type  OBJECT or ARRAY_A.
     * @param bool   $allow_null   Whether NULL values should be excluded explicitly.
     * @return array
     */
    public static function get_records_before_datetime(
        string $table_key,
        string $date_field,
        string $cutoff,
        array $where = [],
        int $limit = 100,
        string $orderby = 'id',
        string $order = 'DESC',
        $output_type = ARRAY_A,
        bool $allow_null = false
    ): array {
        global $wpdb;

        $table_key  = sanitize_key( $table_key );
        $date_field = sanitize_key( $date_field );
        $table      = self::get_validated_table_name( $table_key );

        if ( ! $table || ! self::column_exists( $table_key, $date_field ) ) {
            return [];
        }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        if ( $allow_null ) {
            $parts[] = "{$date_field} IS NOT NULL";
        }

        $parts[]  = "{$date_field} < %s";
        $values[] = $cutoff;

        $orderby = self::column_exists( $table_key, sanitize_key( $orderby ) ) ? sanitize_key( $orderby ) : 'id';
        $order   = strtoupper( $order ) === 'ASC' ? 'ASC' : 'DESC';
        $limit   = max( 1, $limit );
        $values[] = $limit;

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_records_before_' . md5( $date_field . $cutoff . serialize( $where ) . $limit . $orderby . $order . (string) $output_type . (int) $allow_null );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (array) $cached; }

        $rows = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT * FROM `{$table}` {$where_sql} ORDER BY `{$orderby}` {$order} LIMIT %d",
                ...$values
            ),
            $output_type
        );

        $rows = is_array( $rows ) ? $rows : [];
        CacheManager::get_instance()->set_db( $cache_key, $rows );
        return $rows;
    }

    /**
     * Get records where a datetime field is NULL or not yet expired.
     *
     * @param string $table_key    Table identifier.
     * @param string $expire_field Expiry datetime column.
     * @param array  $where        Exact equality conditions.
     * @param int    $limit        Max rows to return.
     * @param string $orderby      Order-by column.
     * @param string $order        Sort direction.
     * @param string $output_type  OBJECT or ARRAY_A.
     * @return array
     */
    public static function get_records_not_expired(
        string $table_key,
        string $expire_field,
        array $where = [],
        int $limit = 100,
        string $orderby = 'id',
        string $order = 'DESC',
        $output_type = ARRAY_A
    ): array {
        global $wpdb;

        $table_key    = sanitize_key( $table_key );
        $expire_field = sanitize_key( $expire_field );
        $table        = self::get_validated_table_name( $table_key );

        if ( ! $table || ! self::column_exists( $table_key, $expire_field ) ) {
            return [];
        }

        $parts  = [];
        $values = [];

        foreach ( $where as $col => $val ) {
            $col = sanitize_key( (string) $col );
            if ( ! self::column_exists( $table_key, $col ) ) { continue; }
            $parts[]  = "{$col} = %s";
            $values[] = $val;
        }

        $parts[]  = "({$expire_field} IS NULL OR {$expire_field} >= %s)";
        $values[] = gmdate( 'Y-m-d H:i:s' );

        $orderby = self::column_exists( $table_key, sanitize_key( $orderby ) ) ? sanitize_key( $orderby ) : 'id';
        $order   = strtoupper( $order ) === 'ASC' ? 'ASC' : 'DESC';
        $limit   = max( 1, $limit );
        $values[] = $limit;

        $where_sql = 'WHERE ' . implode( ' AND ', $parts );
        $cache_key = $table_key . '_records_not_expired_' . md5( $expire_field . serialize( $where ) . $limit . $orderby . $order . (string) $output_type );
        $cached    = CacheManager::get_instance()->get_db( $cache_key );
        if ( false !== $cached ) { return (array) $cached; }

        $rows = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
            $wpdb->prepare(
                "SELECT * FROM `{$table}` {$where_sql} ORDER BY `{$orderby}` {$order} LIMIT %d",
                ...$values
            ),
            $output_type
        );

        $rows = is_array( $rows ) ? $rows : [];
        CacheManager::get_instance()->set_db( $cache_key, $rows );
        return $rows;
    }

    /**
     * Delete all rows where a datetime column is older than N days.
     *
     * Replaces:
     *   DELETE FROM t WHERE date_field < DATE_SUB(NOW(), INTERVAL N DAY)
     *
     * Returns the number of rows deleted, or false on error.
     *
     * @param string $table_key  Table identifier.
     * @param string $date_field Datetime column to compare.
     * @param int    $days       Rows older than this many days are removed.
     * @return int|false
     */
    public static function delete_old_records(
        string $table_key,
        string $date_field,
        int    $days,
        bool   $internal = false
    ) {
        global $wpdb;

        $table_key = sanitize_key( $table_key );
        $date_field = sanitize_key( $date_field );
        $days = max( 1, $days );

        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error( __( 'Insufficient permissions for delete_old_records', 'ultraguard-security-suite' ) );
            return false;
        }

        $table = self::get_validated_table_name( $table_key );
        if ( ! $table || ! self::column_exists( $table_key, $date_field ) ) {
            return false;
        }

        // Keep retention math in UTC so rows written with UTC timestamps are
        // cleaned consistently regardless of the MySQL server timezone.
        $cutoff = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp', true ) - ( $days * DAY_IN_SECONDS ) );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $result = $wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); identifiers are validated before SQL assembly.
            "DELETE FROM `{$table}` WHERE `{$date_field}` < %s",
            $cutoff
        ) );

        if ( false === $result ) {
            self::handle_database_error( $wpdb->last_error ?: __( 'delete_old_records failed', 'ultraguard-security-suite' ) );
            return false;
        }

        self::clear_table_cache( $table_key );
        return (int) $result;
    }

    /**
     * Delete all records from a table.
     *
     * @param string $table_key Table identifier.
     * @param bool   $internal  Whether to bypass capability checks for trusted callers.
     * @return int|false
     */
    public static function delete_all(
        string $table_key,
        bool $internal = false
    ) {
        global $wpdb;

        $table_key = sanitize_key( $table_key );

        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error( __( 'Insufficient permissions for delete_all', 'ultraguard-security-suite' ) );
            return false;
        }

        $table = self::get_validated_table_name( $table_key );
        if ( ! $table ) {
            return false;
        }

        $result = $wpdb->query( "DELETE FROM `{$table}`" );  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query built via prepare()
        if ( false === $result ) {
            self::handle_database_error( $wpdb->last_error ?: __( 'delete_all failed', 'ultraguard-security-suite' ) );
            return false;
        }

        self::clear_table_cache( $table_key );
        return (int) $result;
    }

    /**
     * Delete records whose datetime field is older than a provided cutoff.
     *
     * @param string $table_key   Table identifier.
     * @param string $date_field  Datetime column to compare.
     * @param string $cutoff      Cutoff datetime; rows older than this are removed.
     * @param bool   $allow_null  Whether NULL values should be preserved.
     * @param bool   $internal    Whether to bypass capability checks for trusted callers.
     * @return int|false
     */
    public static function delete_before_datetime(
        string $table_key,
        string $date_field,
        string $cutoff,
        bool $allow_null = false,
        bool $internal = false
    ) {
        global $wpdb;

        $table_key  = sanitize_key( $table_key );
        $date_field = sanitize_key( $date_field );

        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error( __( 'Insufficient permissions for delete_before_datetime', 'ultraguard-security-suite' ) );
            return false;
        }

        $table = self::get_validated_table_name( $table_key );
        if ( ! $table || ! self::column_exists( $table_key, $date_field ) ) {
            return false;
        }

        $sql = $allow_null
            ? "DELETE FROM `{$table}` WHERE `{$date_field}` IS NOT NULL AND `{$date_field}` < %s"
            : "DELETE FROM `{$table}` WHERE `{$date_field}` < %s";

        $result = $wpdb->query( $wpdb->prepare( $sql, $cutoff ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); identifiers are validated before SQL assembly.
        if ( false === $result ) {
            self::handle_database_error( $wpdb->last_error ?: __( 'delete_before_datetime failed', 'ultraguard-security-suite' ) );
            return false;
        }

        self::clear_table_cache( $table_key );
        return (int) $result;
    }

    /**
     * Insert a row bypassing the varchar/text sanitize_text_field() pass.
     *
     * WHEN TO USE: Only for data that has been pre-validated by the caller and
     * that would be corrupted by sanitize_text_field() — primarily regex patterns
     * containing HTML tokens (e.g. /<script[\s>]/i) stored in the malware-
     * signatures and attack-patterns tables.
     *
     * SECURITY GUARANTEES PRESERVED:
     *   • Table key is still validated against the schema registry.
     *   • Capability check is still enforced.
     *   • $wpdb->insert() uses prepared statements (%s/%d/%f) — no SQL injection.
     *   • DB-query cache is invalidated after a successful insert.
     *
     * ALL OTHER INSERTS must use insert() so sanitization is applied.
     *
     * @param string     $table_key Table identifier.
     * @param array      $data      Column => value pairs (pre-validated by caller).
     * @param array|null $format    wpdb format specifiers ('%s','%d','%f'). If omitted,
     *                              format is inferred from the column definition.
     * @return int|false  New row ID or false on failure.
     */
    public static function insert_raw(
        string $table_key,
        array  $data,
        ?array $format = null,
        bool   $internal = false
    ) {
        global $wpdb;

        $table_key = sanitize_key( $table_key );

        if ( ! $internal && ! self::check_capabilities() ) {
            self::handle_database_error( __( 'Insufficient permissions for insert_raw', 'ultraguard-security-suite' ) );
            return false;
        }

        $table = self::get_validated_table_name( $table_key );
        if ( ! $table ) {
            self::handle_database_error( __( 'Invalid table name', 'ultraguard-security-suite' ) );
            return false;
        }

        if ( empty( $data ) ) {
            self::handle_database_error( __( 'No data provided to insert_raw', 'ultraguard-security-suite' ) );
            return false;
        }

        if ( $format === null ) {
            $format = self::get_format_specifiers( $table_key, $data );
        }

        $result = $wpdb->insert( $table, $data, $format );

        if ( false === $result ) {
            self::handle_database_error(
                $wpdb->last_error ?: __( 'insert_raw failed', 'ultraguard-security-suite' )
            );
            return false;
        }

        $insert_id = $wpdb->insert_id ?: false;
        self::clear_table_cache( $table_key );
        return $insert_id;
    }
}
