<?php
// Exit if accessed directly
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * A database abstraction layer - Handles database operations for the plugin.
 *
 * @since      1.0.0
 */

class ACTLOGPRO_Database {

    /**
     * Safely builds WHERE clause with proper placeholder count to prevent SQL injection.
     *
     * @param array $actlogpro_where_conditions Array of WHERE conditions with placeholders (e.g., 'user_id = %d').
     * @param array $actlogpro_where_values Array of values corresponding to WHERE conditions.
     * @return string WHERE clause with correct number of placeholders for wpdb::prepare().
     */
    private static function actlogpro_build_safe_where_clause($actlogpro_where_conditions, $actlogpro_where_values) {
        if (empty($actlogpro_where_conditions)) {
            return '1=1';
        }

        // Join WHERE conditions with AND - conditions already contain proper placeholders
        return implode(' AND ', $actlogpro_where_conditions);
    }

    /**
     * Get activity logs with filtering and pagination.
     *
     * @param array $args Query arguments.
     * @return array Array containing logs and pagination info.
     */
    public static function get_logs($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Default arguments
        $defaults = array(
            'per_page' => 20,
            'page' => 1,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'user_id' => 0,
            'event_type' => '',
            'action' => '',
            'date_from' => '',
            'date_to' => '',
            'search' => '',
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Create cache key based on the query parameters
        $cache_key = 'actlogpro_logs_' . md5(serialize($args));
        
        // Try to get from cache first
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false !== $result) {
            return $result;
        }
        
        // Build query
        $where = array('1=1');
        $values = array();
        
        // Filter by user
        if (!empty($args['user_id'])) {
            $where[] = 'user_id = %d';
            $values[] = $args['user_id'];
        }
        
        // Filter by event type
        if (!empty($args['event_type'])) {
            $where[] = 'event_type = %s';
            $values[] = $args['event_type'];
        }
        
        // Filter by action
        if (!empty($args['action'])) {
            $where[] = 'action = %s';
            $values[] = $args['action'];
        }
        
        // Filter by date range
        if (!empty($args['date_from'])) {
            $where[] = 'created_at >= %s';
            $values[] = gmdate('Y-m-d 00:00:00', strtotime($args['date_from']));
        }
        
        if (!empty($args['date_to'])) {
            $where[] = 'created_at <= %s';
            $values[] = gmdate('Y-m-d 23:59:59', strtotime($args['date_to']));
        }
        
        // Filter by IP address
        if (!empty($args['ip_address'])) {
            $where[] = 'ip_address = %s';
            $values[] = $args['ip_address'];
        }
        
        // Search
        if (!empty($args['search'])) {
            $search_term = '%' . $wpdb->esc_like($args['search']) . '%';
            $where[] = '(user_login LIKE %s OR user_display_name LIKE %s OR ip_address LIKE %s OR event_type LIKE %s OR action LIKE %s OR object_name LIKE %s OR description LIKE %s)';
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
        }
        
        // Count total items with secure query preparation
        // Build complete parameter array: table name first, then all WHERE values
        $actlogpro_count_params = array_merge(array($table_name), $values);
        
        // Build secure WHERE clause - uses only placeholders, no direct user input
        $actlogpro_safe_where_clause = self::actlogpro_build_safe_where_clause($where, $values);
        
        // Build SQL - WHERE clause contains only safe placeholders (%s, %d, %i)
        $actlogpro_count_sql = "SELECT COUNT(*) FROM %i WHERE " . $actlogpro_safe_where_clause;
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is safely built with placeholders via actlogpro_build_safe_where_clause(). All user input is properly escaped via esc_like() and passed through prepare() with placeholders.
        $total = $wpdb->get_var($wpdb->prepare($actlogpro_count_sql, ...$actlogpro_count_params));
        // phpcs:enable
        
        // Order by
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        
        // Pagination
        $offset = ($args['page'] - 1) * $args['per_page'];
        
        // Build the SQL query with proper placeholders including %i for table name
        // Create full array of prepared statement parameters - table name first, then WHERE values, then LIMIT/OFFSET
        $actlogpro_select_params = array_merge(array($table_name), $values, array($args['per_page'], $offset));
        
        // Build SQL string - WHERE clause contains only safe placeholders (%s, %d, %i)
        $actlogpro_select_sql = "SELECT * FROM %i WHERE " . $actlogpro_safe_where_clause . " ORDER BY " . $orderby . " LIMIT %d OFFSET %d";
        
        // Execute the prepared query with all parameters - avoid concatenation for security
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is safely built with placeholders via actlogpro_build_safe_where_clause(). All user input is properly escaped via esc_like() and passed through prepare() with placeholders.
        $logs = $wpdb->get_results($wpdb->prepare($actlogpro_select_sql, ...$actlogpro_select_params), ARRAY_A);
        // phpcs:enable
        
        $result = array(
            'logs' => $logs,
            'total' => $total
        );
        
        // Cache the result for 5 minutes
        wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        
        return $result;
    }

    /**
     * Delete logs older than a specified timeframe.
     *
     * @param int $days Number of days to keep logs.
     * @return int|false Number of rows deleted or false on error.
     */
    public static function delete_old_logs($days = 30) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key for tracking when logs were last deleted
        $cache_key = 'actlogpro_logs_last_deleted';
        
        // Check if we've recently performed this operation
        $last_deleted = wp_cache_get($cache_key, 'activity-log-pro');
        
        // If we have deleted logs in the last hour, return the cached result
        if (false !== $last_deleted && isset($last_deleted['timestamp']) && isset($last_deleted['result'])) {
            if ((time() - $last_deleted['timestamp']) < HOUR_IN_SECONDS) {
                return $last_deleted['result'];
            }
        }
        
        $date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $result = $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE created_at < %s", $table_name, $date));
        // phpcs:enable
        
        // If we successfully deleted records, invalidate the logs cache
        if ($result !== false) {
            self::invalidate_log_caches();
            
            // Store the result and timestamp in cache
            wp_cache_set($cache_key, array(
                'result' => $result,
                'timestamp' => time(),
                'days' => $days
            ), 'activity-log-pro', DAY_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get event types for filtering.
     *
     * @return array Array of event types.
     */
    public static function get_event_types() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_event_types';
        
        // Check if result is in cache
        $event_types = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false !== $event_types) {
            return $event_types;
        }
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $results = $wpdb->get_results($wpdb->prepare("SELECT DISTINCT event_type FROM %i ORDER BY event_type ASC", $table_name));
        // phpcs:enable
        
        $event_types = array();
        foreach ($results as $result) {
            $event_types[] = $result->event_type;
        }
        
        // Cache the result for 1 hour (event types don't change frequently)
        wp_cache_set($cache_key, $event_types, 'activity-log-pro', HOUR_IN_SECONDS);
        
        return $event_types;
    }

    /**
     * Get users who have activity logs.
     *
     * @return array Array of users.
     */
    public static function get_users_with_logs() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_users_with_logs';
        
        // Check if result is in cache
        $users = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false !== $users) {
            return $users;
        }
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $users = $wpdb->get_results($wpdb->prepare("SELECT DISTINCT user_id, user_login, user_display_name FROM %i WHERE user_id > 0 ORDER BY user_display_name ASC", $table_name));
        // phpcs:enable
        
        // Cache the result for 15 minutes
        wp_cache_set($cache_key, $users, 'activity-log-pro', 15 * MINUTE_IN_SECONDS);
        
        return $users;
    }
    
    /**
     * Get log statistics including total count and table size.
     *
     * @return array Array containing log count and table size.
     */
    public static function get_log_statistics() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_log_statistics';
        
        // Check if result is in cache
        $statistics = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false !== $statistics) {
            return $statistics;
        }
        
        // Get total count
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $table_name));
        // phpcs:enable
        
        // Format the count
        $formatted_count = ($count !== null) ? number_format($count) : '0';
        
        // Get table size - using prepare since we have placeholders
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $size = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT SUM(data_length + index_length) AS size FROM information_schema.TABLES WHERE table_schema = %s AND table_name = %s",
                DB_NAME,
                $table_name
            )
        );
        // phpcs:enable
        
        // Format the size
        if ($size !== null) {
            if ($size < 1024) {
                $size_formatted = $size . ' B';
            } elseif ($size < 1024 * 1024) {
                $size_formatted = round($size / 1024, 2) . ' KB';
            } else {
                $size_formatted = round($size / (1024 * 1024), 2) . ' MB';
            }
        } else {
            $size_formatted = 'N/A';
        }
        
        $statistics = array(
            'count' => $formatted_count,
            'size' => $size_formatted
        );
        
        // Cache the result for 5 minutes
        wp_cache_set($cache_key, $statistics, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        
        return $statistics;
    }
    
    /**
     * Get a single log entry by ID.
     *
     * @param int $log_id The ID of the log entry to retrieve.
     * @return array|null Log entry as an associative array or null if not found.
     */
    public static function get_log_by_id($log_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_log_' . $log_id;
        
        // Check if result is in cache
        $log = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false !== $log) {
            return $log;
        }
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $log = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d", $table_name, $log_id), ARRAY_A);
        // phpcs:enable
        
        // Cache the result for 1 hour (individual log entries rarely change)
        if ($log) {
            wp_cache_set($cache_key, $log, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $log;
    }
    
    /**
     * Clear all logs from the database.
     *
     * @return int|false Number of rows deleted or false on error.
     */
    public static function clear_all_logs() {
        global $wpdb;
        
        // Create cache key for tracking clear logs operation
        $cache_key = 'actlogpro_clear_logs_status';
        
        // Check if we've recently performed this operation
        $clear_status = wp_cache_get($cache_key, 'activity-log-pro');
        
        // If we have cleared logs in the last 5 minutes, return the cached result
        if (false !== $clear_status && isset($clear_status['timestamp']) && isset($clear_status['result'])) {
            if ((time() - $clear_status['timestamp']) < 5 * MINUTE_IN_SECONDS) {
                return $clear_status['result'];
            }
        }
        
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $result = $wpdb->query($wpdb->prepare("DELETE FROM %i", $table_name));
        // phpcs:enable
        
        // If we successfully deleted records, invalidate all caches
        if ($result !== false) {
            self::invalidate_log_caches();
            wp_cache_flush();
            
            // Store the result and timestamp in cache
            wp_cache_set($cache_key, array(
                'result' => $result,
                'timestamp' => time(),
                'status' => 'completed'
            ), 'activity-log-pro', HOUR_IN_SECONDS);
        } else {
            // Cache failure result to prevent repeated failed attempts
            wp_cache_set($cache_key, array(
                'result' => false,
                'timestamp' => time(),
                'status' => 'failed'
            ), 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Check if a table exists in the database.
     *
     * @param string $table_name The name of the table to check.
     * @return bool True if the table exists, false otherwise.
     */
    public static function table_exists($table_name) {
        // Use the cached check_table_exists method to avoid duplication
        return self::check_table_exists($table_name);
    }
    
    /**
     * Get the structure of a table.
     *
     * @param string $table_name The name of the table.
     * @return array|null The table structure or null if the table doesn't exist.
     */
    public static function get_table_structure($table_name) {
        global $wpdb;
        
        // Create cache key
        $cache_key = 'actlogpro_table_structure_' . md5($table_name);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Not in cache, get from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_results($wpdb->prepare("DESCRIBE %i", $table_name), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 1 hour - table structure rarely changes
            wp_cache_set($cache_key, $result, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get the number of records in a table.
     *
     * @param string $table_name The name of the table.
     * @param string $where Optional WHERE clause with placeholders (e.g., 'user_id = %d AND status = %s').
     * @param array $where_values Optional values for the WHERE clause placeholders.
     * @return int The number of records.
     */
    public static function count_records($table_name, $where = '', $where_values = array()) {
        global $wpdb;
        
        // Create cache key
        $cache_key = 'actlogpro_count_records_' . md5($table_name . $where . serialize($where_values));
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Execute the query with appropriate preparation
            if (empty($where)) {
                // No WHERE clause, simple query
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
                $result = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $table_name));
                // phpcs:enable
            } else {
                // Validate WHERE clause contains only placeholders to prevent SQL injection
                if (!preg_match('/^[a-zA-Z0-9_\s\(\)\=\>\<\!\'\"\%\,\.\-\s]*$/', $where)) {
                    // Use WordPress logging function if debugging is enabled
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
                        error_log('ACTLOGPRO_Database::count_records() - Invalid WHERE clause detected: ' . $where);
                    }
                    return 0;
                }
                
                // Ensure WHERE clause contains placeholder patterns and not direct values
                $placeholder_count = substr_count($where, '%s') + substr_count($where, '%d') + substr_count($where, '%f');
                if ($placeholder_count !== count($where_values)) {
                    // Use WordPress logging function if debugging is enabled
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
                        error_log('ACTLOGPRO_Database::count_records() - Placeholder count mismatch. WHERE: ' . $where . ' Values: ' . wp_json_encode($where_values));
                    }
                    return 0;
                }
                
                // Build secure SQL with validated WHERE clause
                $actlogpro_count_params = array_merge(array($table_name), $where_values);
                $actlogpro_count_sql = "SELECT COUNT(*) FROM %i WHERE " . $where;
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding and validated WHERE clause
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is pre-validated and contains only placeholders, passed through prepare()
                $result = $wpdb->get_var($wpdb->prepare($actlogpro_count_sql, ...$actlogpro_count_params));
                // phpcs:enable
            }
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get logs for export with filtering.
     *
     * @param array $args Query arguments including date_from, date_to, user, event_type.
     * @return array Array of logs.
     */
    public static function get_logs_for_export($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Default arguments
        $defaults = array(
            'date_from' => '',
            'date_to' => '',
            'user' => 0,
            'event_type' => '',
            'orderby' => 'created_at',
            'order' => 'DESC',
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Build query using safe array-based pattern
        $where = array('1=1');
        $values = array();
        
        // Add date filters
        if (!empty($args['date_from'])) {
            $where[] = 'created_at >= %s';
            $values[] = gmdate('Y-m-d 00:00:00', strtotime($args['date_from']));
        }
        
        if (!empty($args['date_to'])) {
            $where[] = 'created_at <= %s';
            $values[] = gmdate('Y-m-d 23:59:59', strtotime($args['date_to']));
        }
        
        // Add user filter
        if (!empty($args['user'])) {
            $where[] = 'user_id = %d';
            $values[] = $args['user'];
        }
        
        // Add event type filter
        if (!empty($args['event_type'])) {
            $where[] = 'event_type = %s';
            $values[] = $args['event_type'];
        }
        
        // Build secure WHERE clause - uses only placeholders, no direct user input
        $safe_where_clause = self::actlogpro_build_safe_where_clause($where, $values);
        
        // Order by
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        
        // Create cache key
        $cache_key = 'actlogpro_export_logs_' . md5($safe_where_clause . $orderby . serialize($values));
        
        // Check if result is in cache
        $logs = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $logs) {
            // Query with WHERE clause parameters - build secure SQL to avoid concatenation in prepare()
            $actlogpro_export_params = array_merge(array($table_name), $values);
            $actlogpro_export_sql = "SELECT * FROM %i WHERE " . $safe_where_clause . " ORDER BY " . $orderby;
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is safely built with placeholders via actlogpro_build_safe_where_clause(). All user input is properly passed through prepare() with placeholders.
            $logs = $wpdb->get_results($wpdb->prepare($actlogpro_export_sql, ...$actlogpro_export_params), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $logs, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $logs;
    }

    /**
     * Get count of logs for a specific date range.
     *
     * @param string $start_date Start date and time in Y-m-d H:i:s format.
     * @param string $end_date End date and time in Y-m-d H:i:s format.
     * @return int Number of logs in the specified date range.
     */
    public static function get_logs_count_by_date_range($start_date, $end_date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_logs_count_' . md5($start_date . $end_date);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get count from database - execute query directly without intermediate variable
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE created_at BETWEEN %s AND %s",
                $table_name,
                $start_date,
                $end_date
            ));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get the earliest log date.
     *
     * @return string|null The earliest log date or null if no logs found.
     */
    public static function get_earliest_log_date() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_earliest_log_date';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get earliest date from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare("SELECT MIN(created_at) FROM %i", $table_name));
            // phpcs:enable
            
            // Cache the result for 1 hour - this rarely changes
            wp_cache_set($cache_key, $result, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get total count of logs.
     *
     * @return int Total number of logs.
     */
    public static function get_total_logs_count() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_total_logs_count';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get count from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $table_name));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get unique users count by date range.
     *
     * @param string $start_date Start date and time in Y-m-d H:i:s format.
     * @param string $end_date End date and time in Y-m-d H:i:s format.
     * @return int Number of unique users in the specified date range.
     */
    public static function get_unique_users_count_by_date_range($start_date, $end_date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_unique_users_' . md5($start_date . $end_date);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get count from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT user_id) FROM %i WHERE created_at BETWEEN %s AND %s",
                $table_name,
                $start_date,
                $end_date
            ));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get event types with counts.
     *
     * @param int $limit Maximum number of event types to return (default 8).
     * @return array Array of event types with counts.
     */
    public static function get_event_types_with_counts($limit = 8) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        $limit = absint($limit); // Ensure limit is a safe integer
        
        // Create cache key
        $cache_key = 'actlogpro_event_types_counts_' . $limit;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_results($wpdb->prepare(
                "SELECT event_type, COUNT(*) as count FROM %i GROUP BY event_type ORDER BY count DESC LIMIT %d",
                $table_name,
                $limit
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get action types with counts.
     *
     * @param int $limit Maximum number of action types to return (default 8).
     * @return array Array of action types with counts.
     */
    public static function get_action_types_with_counts($limit = 8) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        $limit = absint($limit); // Ensure limit is a safe integer
        
        // Create cache key
        $cache_key = 'actlogpro_action_types_counts_' . $limit;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_results($wpdb->prepare(
                "SELECT action, COUNT(*) as count FROM %i GROUP BY action ORDER BY count DESC LIMIT %d",
                $table_name,
                $limit
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get activity by day and hour.
     *
     * @return array Array of activity data by day of week and hour.
     */
    public static function get_activity_by_day_and_hour() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_activity_day_hour';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_results($wpdb->prepare(
                "SELECT DAYOFWEEK(created_at) - 1 as day_of_week, HOUR(created_at) as hour_of_day, COUNT(*) as count FROM %i GROUP BY day_of_week, hour_of_day ORDER BY count DESC",
                $table_name
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 15 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 15 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get activity data for a date range by day.
     *
     * @param int $days Number of days to get data for.
     * @return array Array containing labels, counts, and users data for the specified days.
     */
    public static function get_activity_data_by_days($days = 30) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_activity_data_days_' . $days;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            $labels = array();
            $counts = array();
            $users = array();
            
            for ($i = $days - 1; $i >= 0; $i--) {
                $date = gmdate('Y-m-d', strtotime("-$i days"));
                $date_display = date_i18n(get_option('date_format'), strtotime($date));
                $date_start = $date . ' 00:00:00';
                $date_end = $date . ' 23:59:59';
                
                // Get total activity count for this day
                $count = self::get_logs_count_by_date_range($date_start, $date_end);
                
                // Get unique users count for this day
                $users_count = self::get_unique_users_count_by_date_range($date_start, $date_end);
                
                $labels[] = $date_display;
                $counts[] = $count;
                $users[] = $users_count;
            }
            
            $result = array(
                'labels' => $labels,
                'counts' => $counts,
                'users' => $users
            );
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get daily stats including count of logs, unique users, top event types, and failed logins.
     *
     * @param string $date Date in Y-m-d format.
     * @return array Array of daily statistics.
     */
    public static function get_daily_stats($date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create date range
        $date_start = $date . ' 00:00:00';
        $date_end = $date . ' 23:59:59';
        
        // Create cache key
        $cache_key = 'actlogpro_daily_stats_' . $date;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get logs count
            $logs_count = self::get_logs_count_by_date_range($date_start, $date_end);
            
            // Get unique users count
            $users_count = self::get_unique_users_count_by_date_range($date_start, $date_end);
            
            // Get most active event type
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $top_event = $wpdb->get_row($wpdb->prepare(
                "SELECT event_type, COUNT(*) as count FROM %i WHERE created_at BETWEEN %s AND %s 
                GROUP BY event_type 
                ORDER BY count DESC 
                LIMIT 1",
                $table_name,
                $date_start,
                $date_end
            ), ARRAY_A);
            // phpcs:enable
            
            // Get failed logins count (including both known and unknown usernames)
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $failed_logins = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'security' AND (action = 'login_failed' OR action = 'login_failed_nonexistent') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            // phpcs:enable
            
            $result = array(
                'logs_count' => $logs_count,
                'users_count' => $users_count,
                'top_event' => $top_event,
                'failed_logins' => $failed_logins
            );
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get unique event types count.
     *
     * @return int Number of unique event types.
     */
    public static function get_unique_event_types_count() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_unique_event_types_count';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get count from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT event_type) FROM %i", $table_name));
            // phpcs:enable
            
            // Cache the result for 15 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 15 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get the top event type by date range.
     *
     * @param string $start_date Start date and time in Y-m-d H:i:s format.
     * @param string $end_date End date and time in Y-m-d H:i:s format.
     * @return array|null Top event type data or null if none found.
     */
    public static function get_top_event_type_by_date_range($start_date, $end_date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_top_event_' . md5($start_date . $end_date);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $row = $wpdb->get_row($wpdb->prepare(
                "SELECT event_type, COUNT(*) as count FROM %i 
                WHERE created_at BETWEEN %s AND %s 
                GROUP BY event_type 
                ORDER BY count DESC 
                LIMIT 1",
                $table_name,
                $start_date,
                $end_date
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $row, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
            $result = $row;
        }
        
        return $result;
    }
    
    /**
     * Get failed logins count by date range.
     *
     * @param string $start_date Start date and time in Y-m-d H:i:s format.
     * @param string $end_date End date and time in Y-m-d H:i:s format.
     * @return int Number of failed logins in the specified date range.
     */
    public static function get_failed_logins_count_by_date_range($start_date, $end_date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_failed_logins_' . md5($start_date . $end_date);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get count from database (including both known and unknown usernames)
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'security' AND (action = 'login_failed' OR action = 'login_failed_nonexistent') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $start_date, $end_date
            ));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get event types for display in dropdown.
     *
     * @return array Array of event types.
     */
    public static function get_event_types_for_dropdown() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_event_types_dropdown';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT event_type FROM %i ORDER BY event_type ASC", $table_name));
            // phpcs:enable
            
            // Cache the result for 1 hour (since event types don't change often)
            wp_cache_set($cache_key, $result, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get action types for display in dropdown.
     *
     * @return array Array of action types.
     */
    public static function get_action_types_for_dropdown() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_action_types_dropdown';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT action FROM %i WHERE action IS NOT NULL AND action != '' ORDER BY action ASC", $table_name));
            // phpcs:enable
            
            // Cache the result for 1 hour (since action types don't change often)
            wp_cache_set($cache_key, $result, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Clean up database during plugin uninstallation.
     *
     * @return bool True on success, false on failure.
     */
    public static function cleanup_on_uninstall() {
        global $wpdb;
        
        try {
            // Create cache key for tracking cleanup status
            $cache_key = 'actlogpro_cleanup_status';
            
            // Check if cleanup is already in progress or completed recently
            $cleanup_status = wp_cache_get($cache_key, 'activity-log-pro');
            if (false !== $cleanup_status && isset($cleanup_status['timestamp'])) {
                // If cleanup was done within the last 5 minutes, return cached result
                if ((time() - $cleanup_status['timestamp']) < 5 * MINUTE_IN_SECONDS) {
                    return $cleanup_status['result'];
                }
            }
            
            // Set cleanup in progress status
            wp_cache_set($cache_key, array(
                'status' => 'in_progress',
                'timestamp' => time()
            ), 'activity-log-pro', 10 * MINUTE_IN_SECONDS);
            
            // Drop the activity log table
            $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
            
            // Cache keys to be deleted
            $cache_keys = array(
                            'actlogpro_logs_*',
            'actlogpro_event_types*',
            'actlogpro_users_with_logs',
            'actlogpro_log_statistics',
            'actlogpro_log_*',
            'actlogpro_table_exists_*',
            'actlogpro_table_columns_*',
            'actlogpro_count_records_*',
            'actlogpro_export_logs_*',
            'actlogpro_logs_count_*',
            'actlogpro_earliest_log_date',
            'actlogpro_total_logs_count',
            'actlogpro_unique_users_*',
            'actlogpro_activity_*',
            'actlogpro_daily_stats_*',
            'actlogpro_unique_event_types_count',
            'actlogpro_top_event_*',
            'actlogpro_failed_logins_*',
            'actlogpro_feed_logs_*',
            'actlogpro_filtered_logs_*',
            'actlogpro_wc_attribute_*',
            'actlogpro_transients_*',
            'actlogpro_logs_last_deleted',
            'actlogpro_ip_addresses_dropdown'
            );
            
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- This is a database abstraction class that needs to make direct queries. Schema change is necessary for plugin uninstallation cleanup.
            $result = $wpdb->query($wpdb->prepare("DROP TABLE IF EXISTS %i", $table_name));
            // phpcs:enable
            
            // Delete all transients - use prepared query with placeholders
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $deleted_transients = $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s",
                    '%_transient_actlogpro_%'
                )
            );
            // phpcs:enable
            
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $deleted_timeouts = $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s",
                    '%_transient_timeout_actlogpro_%'
                )
            );
            // phpcs:enable
            
            // Delete any feed tokens or security-related options
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $deleted_tokens = $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s",
                    'actlogpro_feed_token_%'
                )
            );
            // phpcs:enable
            
            // Delete all cache data
            foreach ($cache_keys as $cache_key) {
                if (strpos($cache_key, '*') !== false) {
                    // For wildcard keys, flush the group
                    wp_cache_flush();
                    break;
                } else {
                    wp_cache_delete($cache_key, 'activity-log-pro');
                }
            }
            
            // Update cleanup status with success
            wp_cache_set('actlogpro_cleanup_status', array(
                'result' => true,
                'timestamp' => time(),
                'status' => 'completed'
            ), 'activity-log-pro', HOUR_IN_SECONDS);
            
            return true;
        } catch (Exception $e) {
            // Use WordPress logging function if debugging is enabled
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
                error_log('Activity Log Pro: Error during uninstall cleanup - ' . $e->getMessage());
            }
            
            // Update cleanup status with failure
            wp_cache_set('actlogpro_cleanup_status', array(
                'result' => false,
                'timestamp' => time(),
                'status' => 'failed',
                'error' => $e->getMessage()
            ), 'activity-log-pro', HOUR_IN_SECONDS);
            
            return false;
        }
    }
    
    /**
     * Checks if a table exists in the database with caching.
     *
     * @param string $table_name Table name to check for existence.
     * @return bool True if table exists, false otherwise.
     */
    public static function check_table_exists($table_name) {
        global $wpdb;
        
        // Create cache key
        $cache_key = 'actlogpro_table_exists_' . md5($table_name);
        
        // Try to get from cache first
        $exists = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $exists) {
            // Check if table exists
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) == $table_name;
            // phpcs:enable
            
            // Cache the result for 1 hour (table existence rarely changes)
            wp_cache_set($cache_key, $exists, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $exists;
    }
    
    /**
     * Get table columns with caching.
     *
     * @param string $table_name Table name to get columns for.
     * @return array List of column objects or empty array if table doesn't exist.
     */
    public static function get_table_columns($table_name) {
        global $wpdb;
        
        // Create a cache key
        $cache_key = 'actlogpro_table_columns_' . md5($table_name);
        
        // Try to get from cache first
        $columns = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $columns) {
            // Check if table exists first
            if (!self::check_table_exists($table_name)) {
                return array();
            }
            
            // Get columns
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $columns = $wpdb->get_results($wpdb->prepare("SHOW COLUMNS FROM %i", $table_name));
            // phpcs:enable
            
            // Cache the result for 1 hour (table structure rarely changes)
            wp_cache_set($cache_key, $columns, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $columns;
    }
    
    /**
     * Get column names from table columns.
     *
     * @param array $columns List of column objects.
     * @return array List of column names.
     */
    public static function get_column_names($columns) {
        $column_names = array();
        
        foreach ($columns as $column) {
            $column_names[] = $column->Field;
        }
        
        return $column_names;
    }
    
    /**
     * Decode log metadata from the database (PHP serialize or JSON).
     *
     * @param mixed $raw Raw metadata column value.
     * @return array<string, mixed>
     */
    public static function decode_log_metadata( $raw ): array {
        if ( empty( $raw ) ) {
            return array();
        }

        if ( is_array( $raw ) ) {
            return $raw;
        }

        if ( ! is_string( $raw ) ) {
            return array();
        }

        $unserialized = maybe_unserialize( $raw );
        if ( is_array( $unserialized ) ) {
            return $unserialized;
        }

        $decoded = json_decode( $raw, true );
        if ( is_array( $decoded ) ) {
            return $decoded;
        }

        return array();
    }

    /**
     * Log an activity directly to the database with caching.
     *
     * @param array $data Data to insert into the activity log.
     * @return int|false The ID of the inserted record, or false on failure.
     */
    public static function log_activity($data) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Check if table exists
        if (!self::check_table_exists($table_name)) {
            // Try to create the table if it doesn't exist
            self::create_missing_table();
            
            // Check again after creation attempt
            if (!self::check_table_exists($table_name)) {
                // Use WordPress logging function if debugging is enabled
                if (defined('WP_DEBUG') && WP_DEBUG) {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
                    error_log('ACTLOGPRO_Database::log_activity() - Table does not exist and could not be created: ' . $table_name);
                }
                return false;
            }
        }
        
        // Insert into database
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
        $result = $wpdb->insert($table_name, $data);
        // phpcs:enable
        
        if ($result) {
            // Bust any related caches
            self::invalidate_log_caches();
            $insert_id = $wpdb->insert_id;
            // Fire after a log row is successfully saved — premium channels hook here.
            do_action( 'actlogpro_after_log_saved', $data, $insert_id );
            return $insert_id;
        }
        
        return false;
    }
    
    /**
     * Create the missing database table (SECURITY-ENHANCED).
     * This method includes additional safety checks to prevent accidental data loss.
     *
     * @since    1.0.0
     * @access   private
     */
    private static function create_missing_table() {
        global $wpdb;
        
        // Get the main table name with proper prefix
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // CRITICAL SAFETY CHECK: Double-verify table doesn't exist before creation
        // This prevents accidental recreation of existing tables with data
        if (self::check_table_exists($table_name)) {
            // Table exists - log warning and return without touching it
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
                error_log('ACTLOGPRO_Database::create_missing_table() - WARNING: Attempted to create existing table: ' . $table_name);
            }
            return;
        }
        
        // Safe to create table - it doesn't exist
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            user_role varchar(100) NOT NULL,
            user_login varchar(60) NOT NULL,
            user_display_name varchar(250) NOT NULL,
            ip_address varchar(45) NOT NULL,
            event_type varchar(100) NOT NULL,
            object_type varchar(100) NOT NULL,
            object_id bigint(20) NOT NULL,
            object_name varchar(250) NOT NULL,
            action varchar(100) NOT NULL,
            severity varchar(20) NOT NULL DEFAULT 'info',
            description text NOT NULL,
            created_at datetime NOT NULL,
            metadata longtext,
            PRIMARY KEY  (id)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Clear the table existence cache after creation
        $cache_key = 'actlogpro_table_exists_' . md5($table_name);
        wp_cache_delete($cache_key, 'activity-log-pro');
    }
    
    /**
     * Safely check database version and create tables if needed (upgrade-safe).
     * This method prevents data loss during plugin upgrades by never recreating existing tables.
     *
     * @since    1.0.1
     * @return   bool True if database is ready, false on failure.
     */
    public static function check_and_initialize_database() {
        global $wpdb;
        
        // Get the main table name with proper prefix
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Check if table exists using our secure method
        if (self::check_table_exists($table_name)) {
            // Table exists - verify version and run any pending column migrations
            // We NEVER recreate existing tables to prevent data loss
            $current_version = get_option('actlogpro_db_version', '0.0.0');

            if (version_compare($current_version, ACTLOGPRO_VERSION, '<')) {
                // Run additive column migrations before bumping the stored version
                self::run_column_migrations();
                update_option('actlogpro_db_version', ACTLOGPRO_VERSION);
            }

            return true;
        }
        
        // Table doesn't exist - safe to create it
        self::create_missing_table();
        
        // Verify creation was successful
        if (self::check_table_exists($table_name)) {
            // Set version after successful creation
            update_option('actlogpro_db_version', ACTLOGPRO_VERSION);
            return true;
        }
        
        // Creation failed - log error if debugging enabled
        if (defined('WP_DEBUG') && WP_DEBUG) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Only used when debugging is enabled
            error_log('ACTLOGPRO_Database::check_and_initialize_database() - Failed to create table: ' . $table_name);
        }
        
        return false;
    }

    /**
     * Run additive column migrations for existing tables (upgrade-safe).
     * Only adds missing columns — never drops or modifies existing data.
     *
     * @since 1.0.7
     */
    private static function run_column_migrations(): void {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';

        // Add severity column if it does not exist yet
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration only runs once per version upgrade; caching is inappropriate for DDL checks
        $col = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s',
                DB_NAME,
                $table_name,
                'severity'
            )
        );

        if ( ! $col ) {
            $wpdb->query( "ALTER TABLE `" . esc_sql( $table_name ) . "` ADD COLUMN `severity` varchar(20) NOT NULL DEFAULT 'info' AFTER `action`" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is sanitised via esc_sql(); column definition is a fixed literal with no user input
        }
        // phpcs:enable
    }

    /**
     * Invalidate all log-related caches.
     */
    public static function invalidate_log_caches() {
        // Delete all cache keys that contain log data
        $cache_keys = array(
            'actlogpro_total_logs_count',
            'actlogpro_event_types_dropdown',
            'actlogpro_action_types_dropdown',
            'actlogpro_ip_addresses_dropdown',
            'actlogpro_activity_day_hour',
            'actlogpro_security_daily_stats_',
            'actlogpro_business_daily_stats_',
            'actlogpro_most_active_user_',
            'actlogpro_peak_activity_hour_',
        );
        
        foreach ($cache_keys as $key) {
            // For wildcard keys (ending with underscore), delete all variations
            if (substr($key, -1) === '_') {
                // For date-based cache keys, clear today's cache - use WordPress local time
                $today = current_time('Y-m-d');
                wp_cache_delete($key . $today, 'activity-log-pro');
                
                // Also clear yesterday's cache as it might affect trend calculations
                $yesterday = current_time('Y-m-d', strtotime('-1 day'));
                wp_cache_delete($key . $yesterday, 'activity-log-pro');
                
                // Clear tomorrow's cache in case of timezone issues
                $tomorrow = current_time('Y-m-d', strtotime('+1 day'));
                wp_cache_delete($key . $tomorrow, 'activity-log-pro');
            } else {
                wp_cache_delete($key, 'activity-log-pro');
            }
        }
        
        // Also flush group caches if WordPress supports it
        if (function_exists('wp_cache_flush_group')) {
            wp_cache_flush_group('activity-log-pro');
        }
        
        // Force clear any WordPress object cache if available
        if (function_exists('wp_cache_flush')) {
            // Only flush our specific group, not the entire cache
            global $wp_object_cache;
            if (isset($wp_object_cache->cache['activity-log-pro'])) {
                unset($wp_object_cache->cache['activity-log-pro']);
            }
        }
    }
    
    /**
     * Get transients by pattern with caching.
     *
     * @param string $pattern Pattern to match in transient names.
     * @return array List of transient objects.
     */
    public static function get_transients_by_pattern($pattern) {
        global $wpdb;
        
        // Create a cache key
        $cache_key = 'actlogpro_transients_' . md5($pattern);
        
        // Try to get from cache first
        $transients = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $transients) {
            // Get transients
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $transients = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT option_name, option_value FROM $wpdb->options WHERE option_name LIKE %s",
                    $pattern
                )
            );
            // phpcs:enable
            
            // Cache the result for a short time (5 minutes)
            wp_cache_set($cache_key, $transients, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $transients;
    }

    /**
     * Log user login activity.
     *
     * @param int    $user_id       User ID.
     * @param string $user_login    User login name.
     * @param string $display_name  User display name.
     * @param string $user_role     User role.
     * @param string $ip_address    IP address.
     * @return int|false The ID of the inserted record, or false on failure.
     */
    public static function log_user_login($user_id, $user_login, $display_name, $user_role, $ip_address) {
        // Prepare data for insertion
        $data = array(
            'user_id' => $user_id,
            'user_login' => $user_login,
            'user_display_name' => $display_name,
            'user_role' => $user_role,
            'ip_address' => $ip_address,
            'event_type' => 'user',
            'object_type' => 'user',
            'object_id' => $user_id,
            'object_name' => $user_login,
            'action' => 'login',
            /* translators: %s: User's display name */
            'description' => sprintf(__('<strong>User Logged In:</strong> %s', 'activity-log-pro'), $display_name),
            'metadata' => '',
            'created_at' => current_time('mysql')
        );
        
        return self::log_activity($data);
    }

    /**
     * Get feed logs with pagination and caching.
     *
     * @param int $limit  Maximum number of logs to retrieve.
     * @param int $offset Number of logs to skip.
     * @return array Array of logs in ARRAY_A format.
     */
    public static function get_feed_logs($limit = 50, $offset = 0) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_feed_logs_' . md5($limit . '_' . $offset);
        
        // Check if result is in cache
        $logs = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $logs) {
            // Not in cache, get from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $logs = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM %i ORDER BY created_at DESC LIMIT %d OFFSET %d",
                $table_name,
                $limit,
                $offset
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $logs, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $logs;
    }

    /**
     * Get WooCommerce attribute data by ID with caching.
     *
     * @param int $attribute_id The attribute ID to retrieve.
     * @return object|null The attribute data or null if not found.
     */
    public static function get_wc_attribute_by_id($attribute_id) {
        global $wpdb;
        
        // Create a cache key
        $cache_key = 'actlogpro_wc_attribute_' . $attribute_id;
        
        // Try to get from cache first
        $attribute = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $attribute) {
            // Not in cache, get from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $attribute = $wpdb->get_row($wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_id = %d",
                absint($attribute_id)
            ));
            // phpcs:enable
            
            // Cache the result for 1 hour (attributes don't change often)
            wp_cache_set($cache_key, $attribute, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $attribute;
    }

    /**
     * Get all attachment IDs whose post_parent matches the given post ID.
     *
     * @param int $post_id Parent post ID.
     * @return array Array of attachment IDs (integers).
     */
    public static function get_attachment_ids_by_parent( $post_id ) {
        global $wpdb;

        $cache_key = 'actlogpro_attachment_ids_parent_' . absint( $post_id );
        $attachment_ids = wp_cache_get( $cache_key, 'activity-log-pro' );

        if ( false === $attachment_ids ) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $attachment_ids = $wpdb->get_col(
                $wpdb->prepare(
                    "SELECT ID FROM {$wpdb->posts} WHERE post_parent = %d AND post_type = 'attachment'",
                    absint( $post_id )
                )
            );
            // phpcs:enable

            wp_cache_set( $cache_key, $attachment_ids, 'activity-log-pro', MINUTE_IN_SECONDS * 5 );
        }

        return array_map( 'intval', $attachment_ids );
    }

    /**
     * Apply a named quick-filter preset to the WHERE clause.
     *
     * @param string $preset Preset key.
     * @param array  $where  WHERE fragments (passed by reference).
     * @param array  $values Prepare values (passed by reference).
     */
    private static function actlogpro_apply_filter_preset( $preset, &$where, &$values ) {
        switch ( $preset ) {
            case 'failed_logins':
                $where[]  = 'event_type = %s';
                $values[] = 'security';
                $where[]  = 'action IN (%s, %s)';
                $values[] = 'login_failed';
                $values[] = 'login_failed_nonexistent';
                break;
            case 'critical_high':
                $where[]  = 'severity IN (%s, %s)';
                $values[] = 'critical';
                $values[] = 'high';
                break;
        }
    }

    /**
     * Get count of logs with complex filtering.
     *
     * @param array $args Query arguments including filtering options.
     * @return int Number of logs matching the filters.
     */
    public static function get_filtered_logs_count($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Build query
        $where = array('1=1');
        $values = array();

        if ( ! empty( $args['filter_preset'] ) ) {
            self::actlogpro_apply_filter_preset( sanitize_key( $args['filter_preset'] ), $where, $values );
        }
        
        // Filter by date range
        if (!empty($args['date_from'])) {
            $where[] = 'created_at >= %s';
            $values[] = gmdate('Y-m-d 00:00:00', strtotime($args['date_from']));
        }
        
        if (!empty($args['date_to'])) {
            $where[] = 'created_at <= %s';
            $values[] = gmdate('Y-m-d 23:59:59', strtotime($args['date_to']));
        }
        
        // Filter by user
        if (!empty($args['user'])) {
            $where[] = 'user_id = %d';
            $values[] = intval($args['user']);
        }
        
        // Filter by event type
        if (!empty($args['event_type'])) {
            $where[] = 'event_type = %s';
            $values[] = $args['event_type'];
        }
        
        // Filter by action
        if (!empty($args['action'])) {
            $where[] = 'action = %s';
            $values[] = $args['action'];
        }
        
        // Filter by IP address
        if (!empty($args['ip_address'])) {
            $where[] = 'ip_address = %s';
            $values[] = $args['ip_address'];
        }

        // Filter by severity
        if (!empty($args['severity'])) {
            $where[] = 'severity = %s';
            $values[] = $args['severity'];
        }
        
        // Search
        if (!empty($args['search'])) {
            $search_term = '%' . $wpdb->esc_like($args['search']) . '%';
            $where[] = '(user_login LIKE %s OR ip_address LIKE %s OR object_name LIKE %s OR description LIKE %s)';
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
        }
        
        // Build secure WHERE clause - uses only placeholders, no direct user input
        $actlogpro_filtered_where_clause = self::actlogpro_build_safe_where_clause($where, $values);
        
        // Create cache key
        $cache_key = 'actlogpro_logs_count_' . md5(serialize($args));
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Query with WHERE clause parameters - build secure SQL to avoid concatenation in prepare()
            $actlogpro_filtered_count_params = array_merge(array($table_name), $values);
            $actlogpro_filtered_count_sql = "SELECT COUNT(*) FROM %i WHERE " . $actlogpro_filtered_where_clause;
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is safely built with placeholders via actlogpro_build_safe_where_clause(). All user input is properly escaped via esc_like() and passed through prepare() with placeholders.
            $result = $wpdb->get_var($wpdb->prepare($actlogpro_filtered_count_sql, ...$actlogpro_filtered_count_params));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get logs with complex filtering, sorting, and pagination.
     *
     * @param array $args Query arguments.
     * @return array Array of log objects.
     */
    public static function get_filtered_logs($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Default arguments
        $defaults = array(
            'per_page' => 20,
            'page' => 1,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'user' => 0,
            'event_type' => '',
            'action' => '',
            'date_from' => '',
            'date_to' => '',
            'search' => '',
        );
        
        $args = wp_parse_args($args, $defaults);
        
        // Build query
        $where = array('1=1');
        $values = array();

        if ( ! empty( $args['filter_preset'] ) ) {
            self::actlogpro_apply_filter_preset( sanitize_key( $args['filter_preset'] ), $where, $values );
        }
        
        // Filter by date range
        if (!empty($args['date_from'])) {
            $where[] = 'created_at >= %s';
            $values[] = gmdate('Y-m-d 00:00:00', strtotime($args['date_from']));
        }
        
        if (!empty($args['date_to'])) {
            $where[] = 'created_at <= %s';
            $values[] = gmdate('Y-m-d 23:59:59', strtotime($args['date_to']));
        }
        
        // Filter by user
        if (!empty($args['user'])) {
            $where[] = 'user_id = %d';
            $values[] = intval($args['user']);
        }
        
        // Filter by event type
        if (!empty($args['event_type'])) {
            $where[] = 'event_type = %s';
            $values[] = $args['event_type'];
        }
        
        // Filter by action
        if (!empty($args['action'])) {
            $where[] = 'action = %s';
            $values[] = $args['action'];
        }
        
        // Filter by IP address
        if (!empty($args['ip_address'])) {
            $where[] = 'ip_address = %s';
            $values[] = $args['ip_address'];
        }

        // Filter by severity
        if (!empty($args['severity'])) {
            $where[] = 'severity = %s';
            $values[] = $args['severity'];
        }
        
        // Search
        if (!empty($args['search'])) {
            $search_term = '%' . $wpdb->esc_like($args['search']) . '%';
            $where[] = '(user_login LIKE %s OR ip_address LIKE %s OR object_name LIKE %s OR description LIKE %s)';
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
            $values[] = $search_term;
        }
        
        // Build secure WHERE clause - uses only placeholders, no direct user input
        $actlogpro_filtered_logs_where_clause = self::actlogpro_build_safe_where_clause($where, $values);
        
        // Order by
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        
        // Pagination
        $offset = ($args['page'] - 1) * $args['per_page'];
        
        // Create cache key
        $cache_key = 'actlogpro_logs_' . md5(serialize($args) . $orderby . $offset . $args['per_page']);
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get logs from database - WHERE clause contains only safe placeholders (%s, %d, %i)
            $actlogpro_filtered_logs_params = array_merge(array($table_name), $values, array($args['per_page'], $offset));
            $actlogpro_filtered_logs_sql = "SELECT * FROM %i WHERE " . $actlogpro_filtered_logs_where_clause . " ORDER BY " . $orderby . " LIMIT %d OFFSET %d";
            
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause is safely built with placeholders via actlogpro_build_safe_where_clause(). All user input is properly escaped via esc_like() and passed through prepare() with placeholders.
            $result = $wpdb->get_results($wpdb->prepare($actlogpro_filtered_logs_sql, ...$actlogpro_filtered_logs_params));
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Delete logs by IDs.
     *
     * @param array $log_ids Array of log IDs to delete.
     * @return int|false Number of rows deleted or false on error.
     */
    public static function delete_logs_by_ids($log_ids = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        if (empty($log_ids) || !is_array($log_ids)) {
            return false;
        }
        
        // Create placeholders for IN clause - we need to generate the correct number of placeholders (%d) 
        // based on the number of items, then prepare them properly
        
        // Create array of placeholders corresponding to the number of IDs
        $placeholders = array_fill(0, count($log_ids), '%d');
        
        // Join the placeholders with commas for the IN clause
        $placeholder_string = implode(',', $placeholders);
        
        // Execute the query with prepare - table name first, then all the IDs
        $actlogpro_delete_sql = "DELETE FROM %i WHERE id IN ($placeholder_string)";
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries with secure parameter binding
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholder string is safely built from array_fill with %d placeholders
        $result = $wpdb->query($wpdb->prepare($actlogpro_delete_sql, $table_name, ...$log_ids));
        // phpcs:enable
        
        if ($result !== false) {
            // Invalidate cache
            self::invalidate_log_caches();
            
            // For specific log entries
            foreach ($log_ids as $id) {
                wp_cache_delete('actlogpro_log_' . $id, 'activity-log-pro');
            }
        }
        
        return $result;
    }

    /**
     * Get IP addresses for display in dropdown.
     *
     * @return array Array of IP addresses.
     */
    public static function get_ip_addresses_for_dropdown() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create cache key
        $cache_key = 'actlogpro_ip_addresses_dropdown';
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // Get data from database
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $result = $wpdb->get_col($wpdb->prepare("SELECT DISTINCT ip_address FROM %i WHERE ip_address IS NOT NULL AND ip_address != '' ORDER BY ip_address ASC", $table_name));
            // phpcs:enable
            
            // Cache the result for 1 hour (since IP addresses don't change often in logs)
            wp_cache_set($cache_key, $result, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get security-focused daily stats for today's statistics panel.
     *
     * @param string $date Date in Y-m-d format.
     * @return array Array of security statistics.
     */
    public static function get_security_daily_stats($date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create date range
        $date_start = $date . ' 00:00:00';
        $date_end = $date . ' 23:59:59';
        
        // Create cache key
        $cache_key = 'actlogpro_security_daily_stats_' . $date;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- This is a database abstraction class that needs to make direct queries. Queries use proper prepared statements.
            // Get failed logins count (including both known and unknown usernames)
            $failed_logins = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'security' AND (action = 'login_failed' OR action = 'login_failed_nonexistent') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get successful logins count
            $successful_logins = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND action = 'login' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get new user registrations count
            $new_users = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND action = 'registered' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get password changes/resets count
            $password_changes = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND (action = 'password_reset' OR action = 'password_changed') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            // phpcs:enable
            
            $result = array(
                'failed_logins' => intval($failed_logins),
                'successful_logins' => intval($successful_logins),
                'new_users' => intval($new_users),
                'password_changes' => intval($password_changes)
            );
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get business-focused daily stats for today's statistics panel.
     *
     * @param string $date Date in Y-m-d format.
     * @return array Array of business statistics.
     */
    public static function get_business_daily_stats($date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create date range
        $date_start = $date . ' 00:00:00';
        $date_end = $date . ' 23:59:59';
        
        // Create cache key
        $cache_key = 'actlogpro_business_daily_stats_' . $date;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- This is a database abstraction class that needs to make direct queries. Queries use proper prepared statements.
            // Get content changes count (posts, pages created/updated/deleted/published)
            $content_changes = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'post' AND (action = 'created' OR action = 'updated' OR action = 'deleted' OR action = 'published' OR action = 'trashed' OR action = 'restored') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get plugin/theme activities count
            $plugin_theme_activities = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE (event_type = 'plugin' OR event_type = 'theme') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get admin setting changes count
            $admin_changes = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'option' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Get file modifications count (media uploads and deletions)
            $file_changes = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'media' AND (action = 'added' OR action = 'deleted')
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            // phpcs:enable
            
            $result = array(
                'content_changes' => intval($content_changes),
                'plugin_theme_activities' => intval($plugin_theme_activities),
                'admin_changes' => intval($admin_changes),
                'file_changes' => intval($file_changes)
            );
            
            // Debug logging if enabled
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Debug queries with proper prepared statements.
                // Get detailed breakdown for debugging
                $debug_content_actions = $wpdb->get_results($wpdb->prepare(
                    "SELECT action, COUNT(*) as count FROM %i 
                    WHERE event_type = 'post' AND created_at BETWEEN %s AND %s 
                    GROUP BY action",
                    $table_name, $date_start, $date_end
                ), ARRAY_A);
                
                $debug_option_count = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) as count FROM %i 
                    WHERE event_type = 'option' AND created_at BETWEEN %s AND %s",
                    $table_name, $date_start, $date_end
                ));
                
                $debug_media_actions = $wpdb->get_results($wpdb->prepare(
                    "SELECT action, COUNT(*) as count FROM %i 
                    WHERE event_type = 'media' AND created_at BETWEEN %s AND %s 
                    GROUP BY action",
                    $table_name, $date_start, $date_end
                ), ARRAY_A);
                // phpcs:enable

            }
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get most active user for the given date.
     *
     * @param string $date Date in Y-m-d format.
     * @return array|null Most active user data or null if none found.
     */
    public static function get_most_active_user_by_date($date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create date range
        $date_start = $date . ' 00:00:00';
        $date_end = $date . ' 23:59:59';
        
        // Create cache key
        $cache_key = 'actlogpro_most_active_user_' . $date;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- This is a database abstraction class that needs to make direct queries. Query uses proper prepared statements.
            // Get most active user
            $result = $wpdb->get_row($wpdb->prepare(
                "SELECT user_display_name, COUNT(*) as activity_count FROM %i 
                WHERE created_at BETWEEN %s AND %s AND user_display_name IS NOT NULL AND user_display_name != ''
                GROUP BY user_display_name 
                ORDER BY activity_count DESC 
                LIMIT 1",
                $table_name, $date_start, $date_end
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get peak activity hour for the given date.
     *
     * @param string $date Date in Y-m-d format.
     * @return array|null Peak activity hour data or null if none found.
     */
    public static function get_peak_activity_hour_by_date($date) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        // Create date range
        $date_start = $date . ' 00:00:00';
        $date_end = $date . ' 23:59:59';
        
        // Create cache key
        $cache_key = 'actlogpro_peak_activity_hour_' . $date;
        
        // Check if result is in cache
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- This is a database abstraction class that needs to make direct queries. Query uses proper prepared statements.
            // Get peak activity hour
            $result = $wpdb->get_row($wpdb->prepare(
                "SELECT HOUR(created_at) as hour, COUNT(*) as activity_count FROM %i 
                WHERE created_at BETWEEN %s AND %s 
                GROUP BY HOUR(created_at) 
                ORDER BY activity_count DESC 
                LIMIT 1",
                $table_name, $date_start, $date_end
            ), ARRAY_A);
            // phpcs:enable
            
            // Cache the result for 5 minutes
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get database engine information with caching.
     *
     * @param string $table_name Optional table name to check engine for. Defaults to posts table.
     * @return string Database engine name or 'MySQL/MariaDB' as fallback.
     */
    public static function get_database_engine($table_name = null) {
        global $wpdb;
        
        if (null === $table_name) {
            $table_name = $wpdb->posts;
        }
        
        // Create cache key
        $cache_key = 'actlogpro_database_engine_' . md5($table_name);
        
        // Check if result is in cache
        $engine = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $engine) {
            // Get database engine
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- This is a database abstraction class that needs to make direct queries
            $engine = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s LIMIT 1",
                    $table_name
                )
            );
            // phpcs:enable
            
            // Fallback if engine detection fails
            if (!$engine) {
                $engine = 'MySQL/MariaDB';
            }
            
            // Cache the result for 1 hour (database engine rarely changes)
            wp_cache_set($cache_key, $engine, 'activity-log-pro', HOUR_IN_SECONDS);
        }
        
        return $engine;
    }
    
    /**
     * Get security statistics for email digest period.
     * 
     * Provides comprehensive security activity metrics including
     * login attempts, user changes, and potential security threats.
     * 
     * @param int $date_from Start timestamp.
     * @param int $date_to End timestamp.
     * @return array Security statistics array.
     * @since 1.1.0
     */
    public static function get_security_stats_for_period($date_from, $date_to) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        $date_start = gmdate('Y-m-d H:i:s', $date_from);
        $date_end = gmdate('Y-m-d H:i:s', $date_to);
        
        $cache_key = 'actlogpro_security_stats_' . md5($date_start . $date_end);
        
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Database abstraction class needs direct queries with proper prepared statements
            
            // Failed login attempts (including all types)
            // Use prepared statement with LIKE pattern - wildcard is added to the value, not in SQL
            $login_failed_pattern = 'login_failed' . '%';
            $failed_logins = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'security' AND action LIKE %s 
                AND created_at BETWEEN %s AND %s",
                $table_name, $login_failed_pattern, $date_start, $date_end
            ));
            
            // Successful logins
            $successful_logins = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND action = 'login' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // New user registrations
            $new_users = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND action = 'registered' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Password changes
            $password_changes = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'user' AND action = 'password_changed' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Suspicious activities (multiple failed logins from same IP, etc.)
            $suspicious_activities = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT ip_address) FROM %i 
                WHERE event_type = 'security' AND action LIKE %s 
                AND created_at BETWEEN %s AND %s 
                GROUP BY ip_address 
                HAVING COUNT(*) >= 5",
                $table_name, $login_failed_pattern, $date_start, $date_end
            ));
            
            // Critical changes (plugin/theme changes, user role changes)
            $critical_changes = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE ((event_type = 'user' AND action IN ('role_changed', 'deleted')) 
                OR (event_type = 'plugin' AND action IN ('activated', 'deactivated')) 
                OR (event_type = 'theme' AND action IN ('activated', 'changed'))) 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // phpcs:enable
            
            $result = array(
                'failed_logins' => $failed_logins,
                'successful_logins' => $successful_logins,
                'new_users' => $new_users,
                'password_changes' => $password_changes,
                'suspicious_activities' => $suspicious_activities,
                'critical_changes' => $critical_changes
            );
            
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get business activity statistics for email digest period.
     * 
     * Provides content management and administrative activity metrics
     * for business intelligence and operational insights.
     * 
     * @param int $date_from Start timestamp.
     * @param int $date_to End timestamp.
     * @return array Business activity statistics array.
     * @since 1.1.0
     */
    public static function get_business_stats_for_period($date_from, $date_to) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        $date_start = gmdate('Y-m-d H:i:s', $date_from);
        $date_end = gmdate('Y-m-d H:i:s', $date_to);
        
        $cache_key = 'actlogpro_business_stats_' . md5($date_start . $date_end);
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Database abstraction class needs direct queries with proper prepared statements
            
            // Posts created
            $posts_created = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'post' AND action IN ('created', 'published') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Posts updated
            $posts_updated = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'post' AND action = 'updated' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Media uploads
            $media_uploads = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'media' AND action = 'added' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Plugin activities
            $plugin_activities = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'plugin' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Theme activities
            $theme_activities = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'theme' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Admin changes (options, settings)
            $admin_changes = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'option' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // WordPress updates
            $wordpress_updates = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'wordpress' AND action IN ('updated', 'core_updated') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // phpcs:enable
            
            $result = array(
                'posts_created' => $posts_created,
                'posts_updated' => $posts_updated,
                'media_uploads' => $media_uploads,
                'plugin_activities' => $plugin_activities,
                'theme_activities' => $theme_activities,
                'admin_changes' => $admin_changes,
                'wordpress_updates' => $wordpress_updates
            );
            
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Check if WooCommerce activity data is available.
     * 
     * Determines if the database contains WooCommerce-related
     * activity logs for premium email digest integration.
     * 
     * @return bool True if WooCommerce activity exists.
     * @since 1.1.0
     */
    public static function has_woocommerce_activity() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        $cache_key = 'actlogpro_has_woocommerce_activity';
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Database abstraction class needs direct queries with proper prepared statements
            $count = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'woocommerce' 
                LIMIT 1",
                $table_name
            ));
            // phpcs:enable
            
            $result = ($count > 0);
            wp_cache_set($cache_key, $result, 'activity-log-pro', 30 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }
    
    /**
     * Get WooCommerce activity statistics for email digest period.
     * 
     * Provides e-commerce specific activity metrics when premium
     * WooCommerce integration is available and active.
     * 
     * @param int $date_from Start timestamp.
     * @param int $date_to End timestamp.
     * @return array WooCommerce statistics array.
     * @since 1.1.0
     */
    public static function get_woocommerce_stats_for_period($date_from, $date_to) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';
        
        $date_start = gmdate('Y-m-d H:i:s', $date_from);
        $date_end = gmdate('Y-m-d H:i:s', $date_to);
        
        $cache_key = 'actlogpro_woocommerce_stats_' . md5($date_start . $date_end);
        $result = wp_cache_get($cache_key, 'activity-log-pro');
        
        if (false === $result) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Database abstraction class needs direct queries with proper prepared statements
            
            // Orders created
            $orders_created = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'woocommerce' AND action = 'order_created' 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Products modified
            $products_modified = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'woocommerce' AND action IN ('product_created', 'product_updated', 'product_deleted') 
                AND created_at BETWEEN %s AND %s",
                $table_name, $date_start, $date_end
            ));
            
            // Settings changes
            $setting_pattern = '%' . 'setting' . '%';
            $settings_changes = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i 
                WHERE event_type = 'woocommerce' AND action LIKE %s 
                AND created_at BETWEEN %s AND %s",
                $table_name, $setting_pattern, $date_start, $date_end
            ));
            
            // phpcs:enable
            
            $result = array(
                'orders_created' => $orders_created,
                'products_modified' => $products_modified,
                'settings_changes' => $settings_changes
            );
            
            wp_cache_set($cache_key, $result, 'activity-log-pro', 5 * MINUTE_IN_SECONDS);
        }
        
        return $result;
    }

    /**
     * Get basic stats for the user card popover (total events, last login, last activity).
     *
     * Cached for 60 seconds — short TTL so timestamps stay reasonably fresh without
     * hammering the DB on repeated popover opens.
     *
     * @since  1.2.0
     * @param  int $user_id  The target user ID.
     * @return array { total_events: int, last_login: string, last_activity: string }
     */
    public static function get_user_card_stats( int $user_id ): array {
        global $wpdb;
        $table_name = $wpdb->prefix . 'actlogpro_activity_log_pro_all_logs';

        $cache_key = 'actlogpro_user_card_stats_' . $user_id;
        $cached    = wp_cache_get( $cache_key, 'activity-log-pro' );

        if ( false !== $cached && is_array( $cached ) && array_key_exists( 'last_login', $cached ) ) {
            return $cached;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Database abstraction class; direct query required.
        $row = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT COUNT(*) AS total,
                    MAX(created_at) AS last_activity,
                    MAX(CASE WHEN event_type = %s AND action = %s THEN created_at END) AS last_login
                FROM %i WHERE user_id = %d',
                'user',
                'login',
                $table_name,
                $user_id
            )
        );
        // phpcs:enable

        $result = array(
            'total_events'  => $row ? (int) $row->total : 0,
            'last_login'    => ( $row && $row->last_login ) ? $row->last_login : '',
            'last_activity' => ( $row && $row->last_activity ) ? $row->last_activity : '',
        );

        wp_cache_set( $cache_key, $result, 'activity-log-pro', 60 );

        return $result;
    }

}