table_name = $wpdb->prefix . 'aitm_statistics'; } /** * Log a generation attempt. * * @param int $attachment_id Attachment ID. * @param int|null $post_id Associated post ID. * @param string|null $post_type Post type. * @param string $generation_method Generation method (ai, regular). * @param string $ai_service AI service used. * @param bool $success Whether generation was successful. * @param string $error_message Error message if failed. * @return bool|WP_Error */ public function log_generation($attachment_id, $post_id, $post_type, $generation_method, $ai_service, $success, $error_message = '') { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table insert $result = $wpdb->insert( $this->table_name, array( 'attachment_id' => $attachment_id, 'post_id' => $post_id, 'post_type' => $post_type, 'generation_method' => $generation_method, 'ai_service' => $ai_service, 'success' => $success ? 1 : 0, 'error_message' => $error_message, 'created_at' => current_time('mysql'), ), array( '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s', ) ); if ($result === false) { return new WP_Error( 'aitm_log_generation_failed', __('Failed to log generation statistics.', 'ai-thumbnails-maker') ); } // Invalidate relevant caches wp_cache_delete('aitm_stats_total_*', 'aitm_statistics'); wp_cache_delete('aitm_stats_results_*', 'aitm_statistics'); wp_cache_delete('aitm_stats_summary_*', 'aitm_statistics'); wp_cache_delete('aitm_ai_service_usage_*', 'aitm_statistics'); wp_cache_delete('aitm_post_type_usage_*', 'aitm_statistics'); wp_cache_delete('aitm_error_stats_*', 'aitm_statistics'); wp_cache_delete('aitm_failed_attachment_ids', 'aitm_statistics'); return $wpdb->insert_id; } /** * Get statistics data. * * @param int $page Page number. * @param int $per_page Items per page. * @return array */ public function get_statistics($page = 1, $per_page = 100) { global $wpdb; $offset = ($page - 1) * $per_page; // Get total count (only Auto Featured Image operations) $cache_key = 'aitm_stats_total_' . $page . '_' . $per_page; $total = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $total) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $total = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE generation_method != 'regular'", $this->table_name ) ); wp_cache_set($cache_key, $total, 'aitm_statistics', 300); // Cache for 5 minutes } // Get statistics with pagination (only Auto Featured Image operations) $cache_key_results = 'aitm_stats_results_' . $page . '_' . $per_page; $results = wp_cache_get($cache_key_results, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT s.*, a.post_title as attachment_title, p.post_title as post_title, p.post_type as post_type FROM %i s LEFT JOIN {$wpdb->posts} a ON s.attachment_id = a.ID LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE s.generation_method != 'regular' ORDER BY s.created_at DESC LIMIT %d OFFSET %d", $this->table_name, $per_page, $offset ) ); wp_cache_set($cache_key_results, $results, 'aitm_statistics', 300); // Cache for 5 minutes } // Format results $formatted_results = array(); foreach ($results as $result) { $formatted_results[] = $this->format_statistics_entry($result); } // Get summary statistics $summary = $this->get_statistics_summary(30); return array( 'data' => $formatted_results, 'summary' => $summary, 'total' => (int) $total, 'page' => $page, 'per_page' => $per_page, 'total_pages' => ceil($total / $per_page), ); } /** * Get recent statistics (last 100 entries). * * @return array */ public function get_recent_statistics() { return $this->get_statistics(1, 100); } /** * Get statistics summary. * * @param int $days Number of days to look back. * @return array */ public function get_statistics_summary($days = 30) { global $wpdb; $start_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days")); $cache_key = 'aitm_stats_summary_' . $days; $summary = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $summary) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $summary = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) as total_generations, SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as successful_generations, SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as failed_generations, SUM(CASE WHEN generation_method = 'ai' AND success = 1 THEN 1 ELSE 0 END) as ai_successful_generations, SUM(CASE WHEN generation_method = 'first_image' AND success = 1 THEN 1 ELSE 0 END) as first_image_successful_generations FROM %i WHERE created_at >= %s AND generation_method != 'regular'", $this->table_name, $start_date ) ); wp_cache_set($cache_key, $summary, 'aitm_statistics', 300); // Cache for 5 minutes } $success_rate = $summary->total_generations > 0 ? ($summary->successful_generations / $summary->total_generations) * 100 : 0; $ai_usage_rate = $summary->total_generations > 0 ? ($summary->ai_successful_generations / $summary->total_generations) * 100 : 0; return array( 'total_generations' => (int) $summary->total_generations, 'successful_generations' => (int) $summary->successful_generations, 'failed_generations' => (int) $summary->failed_generations, 'ai_successful_generations' => (int) $summary->ai_successful_generations, 'regular_successful_generations' => (int) $summary->regular_successful_generations, 'success_rate' => round($success_rate, 2), 'ai_usage_rate' => round($ai_usage_rate, 2), 'period_days' => $days, ); } /** * Get AI service usage statistics. * * @param int $days Number of days to look back. * @return array */ public function get_ai_service_usage($days = 30) { global $wpdb; $start_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days")); $cache_key = 'aitm_ai_service_usage_' . $days; $results = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT ai_service, COUNT(*) as total_requests, SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as successful_requests, SUM(CASE WHEN success = 0 THEN 1 ELSE 0 END) as failed_requests FROM %i WHERE generation_method = 'ai' AND created_at >= %s GROUP BY ai_service ORDER BY total_requests DESC", $this->table_name, $start_date ) ); wp_cache_set($cache_key, $results, 'aitm_statistics', 300); // Cache for 5 minutes } $formatted_results = array(); foreach ($results as $result) { $success_rate = $result->total_requests > 0 ? ($result->successful_requests / $result->total_requests) * 100 : 0; $formatted_results[] = array( 'service' => $result->ai_service, 'total_requests' => (int) $result->total_requests, 'successful_requests' => (int) $result->successful_requests, 'failed_requests' => (int) $result->failed_requests, 'success_rate' => round($success_rate, 2), ); } return $formatted_results; } /** * Get post type usage statistics. * * @param int $days Number of days to look back. * @return array */ public function get_post_type_usage($days = 30) { global $wpdb; $start_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days")); $cache_key = 'aitm_post_type_usage_' . $days; $results = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT post_type, COUNT(*) as total_generations, SUM(CASE WHEN success = 1 THEN 1 ELSE 0 END) as successful_generations FROM %i WHERE post_type IS NOT NULL AND created_at >= %s GROUP BY post_type ORDER BY total_generations DESC", $this->table_name, $start_date ) ); wp_cache_set($cache_key, $results, 'aitm_statistics', 300); // Cache for 5 minutes } $formatted_results = array(); foreach ($results as $result) { $success_rate = $result->total_generations > 0 ? ($result->successful_generations / $result->total_generations) * 100 : 0; $formatted_results[] = array( 'post_type' => $result->post_type, 'total_generations' => (int) $result->total_generations, 'successful_generations' => (int) $result->successful_generations, 'success_rate' => round($success_rate, 2), ); } return $formatted_results; } /** * Get error statistics. * * @param int $days Number of days to look back. * @return array */ public function get_error_statistics($days = 30) { global $wpdb; $start_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days")); $cache_key = 'aitm_error_stats_' . $days; $results = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT error_message, COUNT(*) as error_count FROM %i WHERE success = 0 AND error_message IS NOT NULL AND error_message != '' AND created_at >= %s GROUP BY error_message ORDER BY error_count DESC LIMIT 20", $this->table_name, $start_date ) ); wp_cache_set($cache_key, $results, 'aitm_statistics', 300); // Cache for 5 minutes } $formatted_results = array(); foreach ($results as $result) { $formatted_results[] = array( 'error_message' => $result->error_message, 'error_count' => (int) $result->error_count, ); } return $formatted_results; } /** * Clean up old statistics. * * @param int $days Number of days to keep. * @return bool|WP_Error */ public function cleanup_old_statistics($days = 90) { global $wpdb; $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-{$days} days")); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Delete operation, caching not applicable $result = $wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE created_at < %s", $this->table_name, $cutoff_date ) ); if ($result === false) { return new WP_Error( 'aitm_cleanup_failed', __('Failed to cleanup old statistics.', 'ai-thumbnails-maker') ); } return true; } /** * Format a statistics entry for display. * * @param object $entry Statistics entry. * @return array */ private function format_statistics_entry($entry) { $attachment_url = wp_get_attachment_url($entry->attachment_id); $attachment_thumb = wp_get_attachment_image_url($entry->attachment_id, 'thumbnail'); $post_title = ''; $post_url = ''; if ($entry->post_id && $entry->post_title) { $post_title = $entry->post_title; $post_url = get_edit_post_link($entry->post_id); } return array( 'id' => $entry->id, 'attachment_id' => $entry->attachment_id, // translators: %d is the attachment ID number 'attachment_title' => $entry->attachment_title ?: sprintf(__('Attachment %d', 'ai-thumbnails-maker'), $entry->attachment_id), 'attachment_url' => $attachment_url, 'attachment_thumb' => $attachment_thumb, 'post_id' => $entry->post_id, 'post_title' => $post_title, 'post_type' => $entry->post_type, 'post_url' => $post_url, 'generation_method' => $entry->generation_method, 'ai_service' => $entry->ai_service, 'success' => (bool) $entry->success, 'error_message' => $entry->error_message, 'created_at' => $entry->created_at, 'created_at_formatted' => date_i18n(get_option('date_format') . ' ' . get_option('time_format'), strtotime($entry->created_at)), ); } /** * Get statistics for a specific attachment. * * @param int $attachment_id Attachment ID. * @return array */ public function get_attachment_statistics($attachment_id) { global $wpdb; $cache_key = 'aitm_attachment_stats_' . $attachment_id; $results = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE attachment_id = %d ORDER BY created_at DESC", $this->table_name, $attachment_id ) ); wp_cache_set($cache_key, $results, 'aitm_statistics', 300); // Cache for 5 minutes } $formatted_results = array(); foreach ($results as $result) { $formatted_results[] = $this->format_statistics_entry($result); } return $formatted_results; } /** * Get failed generations for retry. * * @param int $limit Maximum number of failed generations to return. * @return array */ public function get_failed_generations($limit = 50) { global $wpdb; $cache_key = 'aitm_failed_generations_' . $limit; $results = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $results) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE success = 0 AND generation_method != 'regular' ORDER BY created_at DESC LIMIT %d", $this->table_name, $limit ) ); wp_cache_set($cache_key, $results, 'aitm_statistics', 300); // Cache for 5 minutes } $formatted_results = array(); foreach ($results as $result) { $formatted_results[] = $this->format_statistics_entry($result); } return $formatted_results; } /** * Get failed attachment IDs. * * @return array */ public function get_failed_attachment_ids() { global $wpdb; $cache_key = 'aitm_failed_attachment_ids'; $failed_ids = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $failed_ids) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $failed_ids = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT attachment_id FROM %i WHERE success = 0 AND generation_method != 'regular' ORDER BY created_at DESC", $this->table_name ) ); wp_cache_set($cache_key, $failed_ids, 'aitm_statistics', 300); // Cache for 5 minutes } return array_map('intval', $failed_ids); } /** * Get statistics entry by ID */ public function get_stat_by_id($stat_id) { global $wpdb; $cache_key = 'aitm_stat_by_id_' . $stat_id; $result = wp_cache_get($cache_key, 'aitm_statistics'); if (false === $result) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table query with caching $result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $this->table_name, $stat_id ), ARRAY_A ); if ($result) { wp_cache_set($cache_key, $result, 'aitm_statistics', 300); // Cache for 5 minutes } } if (!$result) { return null; } return $this->format_statistics_entry((object) $result); } }