<?php
namespace JVBase;

use wpdb;
use JVBase\managers\ErrorHandler;
use JVBase\managers\OperationQueue;
use JVBase\managers\NotificationManager;

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly
}
/**
 * Manages system-wide reporting for edmonton.ink
 * Generates comprehensive daily reports for admins and collects various metrics
 */
class SystemReport
{
    protected wpdb $wpdb;
    protected ErrorHandler $error;
    protected OperationQueue $queue;
    protected NotificationManager $notifications;
    protected string $report_interval = 'daily'; // 'daily', 'weekly'

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->error = JVB()->error();
        $this->queue = JVB()->queue();

        add_action('jvb_generate_daily_report', [$this, 'generateDailyReport']);
    }

    /**
     * Generate and send a daily report
     * @return void
     */
    public function generateDailyReport():void
    {
        // Collect all metrics
        $error_stats = $this->gatherErrorStats();
        $queue_stats = $this->gatherQueueStats();
        $user_metrics = $this->gatherUserMetrics();
        $content_metrics = $this->gatherContentMetrics();
        $system_health = $this->gatherSystemHealthMetrics();
        $notification_metrics = $this->gatherNotificationMetrics();
        $user_retention = $this->gatherUserRetentionMetrics();
        $favourite_metrics = $this->gatherFavouriteMetrics();

        // Format the email content
        $subject = "[edmonton.ink] Daily System Report - " . date('Y-m-d');
        $content = $this->formatDailyReport(
            $error_stats,
            $queue_stats,
            $user_metrics,
            $content_metrics,
            $system_health,
            $notification_metrics,
            $user_retention,
            $favourite_metrics
        );

        // Send email
        $this->sendAdminEmail($subject, $content);

        // Log successful report generation
        $this->error->log(
            'system_report',
            'Daily report generated successfully',
            [
                'date' => date('Y-m-d'),
                'metrics_collected' => [
                    'error_stats', 'queue_stats', 'user_metrics',
                    'content_metrics', 'system_health', 'notification_metrics',
                    'user_retention', 'favourite_metrics'
                ]
            ],
            'info'
        );
    }

    /**
     * Gather error-related statistics for the past 24 hours
     * @return array
     */
    protected function gatherErrorStats():array
    {
        $yesterday = date('Y-m-d H:i:s', strtotime('-24 hours'));
        $stats = [
            'total_errors' => 0,
            'by_severity' => [],
            'by_component' => [],
            'peak_hours' => [],
            'frequent' => [],
            'critical' => []
        ];

        // Get total count
        $stats['total_errors'] = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->wpdb->prefix}jvb_error_log
             WHERE created_at > %s",
            $yesterday
        ));

        // Get counts by severity
        $severity_counts = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT severity, COUNT(*) as count
             FROM {$this->wpdb->prefix}jvb_error_log
             WHERE created_at > %s
             GROUP BY severity
             ORDER BY count DESC",
            $yesterday
        ));

        foreach ($severity_counts as $row) {
            $stats['by_severity'][$row->severity] = $row->count;
        }

        // Get counts by component
        $component_counts = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT component, COUNT(*) as count
             FROM {$this->wpdb->prefix}jvb_error_log
             WHERE created_at > %s
             GROUP BY component
             ORDER BY count DESC
             LIMIT 10",
            $yesterday
        ));

        foreach ($component_counts as $row) {
            $stats['by_component'][$row->component] = $row->count;
        }

        // Get hourly distribution
        $hour_counts = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT HOUR(created_at) as hour, COUNT(*) as count
             FROM {$this->wpdb->prefix}jvb_error_log
             WHERE created_at > %s
             GROUP BY HOUR(created_at)
             ORDER BY count DESC
             LIMIT 5",
            $yesterday
        ));

        foreach ($hour_counts as $row) {
            $stats['peak_hours'][$row->hour] = $row->count;
        }

        // Get most frequent errors
        $stats['frequent'] = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT error_type, component, message, COUNT(*) as count
             FROM {$this->wpdb->prefix}jvb_error_log
             WHERE created_at > %s
             GROUP BY error_type, component, message
             ORDER BY count DESC
             LIMIT 10",
            $yesterday
        ));

        // Get most recent critical errors
        $stats['critical'] = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM {$this->wpdb->prefix}jvb_error_log
             WHERE severity = 'critical' AND created_at > %s
             ORDER BY created_at DESC
             LIMIT 5",
            $yesterday
        ));

        return $stats;
    }

    /**
     * Gather statistics from the bulk operation queue
     * @return array
     */
    protected function gatherQueueStats():array
    {
        $table = $this->wpdb->prefix . BASE . 'bulk_operation';
        $stats = [];

        // Get counts by status
        $status_counts = $this->wpdb->get_results("
            SELECT status, COUNT(*) as count, SUM(count) as count
            FROM $table
            GROUP BY status
        ");

        $stats['by_status'] = [];
        foreach ($status_counts as $row) {
            $stats['by_status'][$row->status] = [
                'count' => $row->count,
                'operations' => $row->count
            ];
        }

        // Get counts by operation type
        $type_counts = $this->wpdb->get_results("
            SELECT type, COUNT(*) as count
            FROM $table
            GROUP BY type
            ORDER BY count DESC
        ");

        $stats['by_type'] = [];
        foreach ($type_counts as $row) {
            $stats['by_type'][$row->type] = $row->count;
        }

        // Get counts by priority
        $priority_counts = $this->wpdb->get_results("
            SELECT priority, COUNT(*) as count
            FROM $table
            WHERE status = 'pending'
            GROUP BY priority
        ");

        $stats['pending_by_priority'] = [];
        foreach ($priority_counts as $row) {
            $stats['pending_by_priority'][$row->priority] = $row->count;
        }

        // Check if queue limit has been reached recently
        $yesterday = date('Y-m-d H:i:s', strtotime('-24 hours'));
        $limit_reached = $this->wpdb->get_var($this->wpdb->prepare("
            SELECT COUNT(*)
            FROM {$this->wpdb->prefix}jvb_error_log
            WHERE error_type = 'queue_limit_reached'
            AND created_at > %s
        ", $yesterday));

        $stats['limit_reached_count'] = $limit_reached;

        // Get oldest pending operation
        $oldest_pending = $this->wpdb->get_row("
            SELECT id, type, created_at, TIMESTAMPDIFF(HOUR, created_at, NOW()) as age_hours
            FROM $table
            WHERE status = 'pending'
            ORDER BY created_at ASC
            LIMIT 1
        ");

        if ($oldest_pending) {
            $stats['oldest_pending'] = [
                'id' => $oldest_pending->id,
                'type' => $oldest_pending->type,
                'created_at' => $oldest_pending->created_at,
                'age_hours' => $oldest_pending->age_hours
            ];
        }

        // Get failed operations in the last 24 hours
        $recent_failures = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT id, type, error_message
            FROM $table
            WHERE status = 'failed'
            AND completed_at > %s
            ORDER BY completed_at DESC
            LIMIT 5
        ", $yesterday));

        $stats['recent_failures'] = $recent_failures;

        // Get average processing time by operation type
        $avg_processing_times = $this->wpdb->get_results("
            SELECT
                type,
                AVG(TIMESTAMPDIFF(SECOND, started_at, completed_at)) as avg_seconds,
                COUNT(*) as sample_size
            FROM $table
            WHERE
                status = 'completed'
                AND started_at IS NOT NULL
                AND completed_at IS NOT NULL
                AND completed_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY type
            HAVING sample_size > 5
        ");

        $stats['avg_processing_times'] = [];
        foreach ($avg_processing_times as $row) {
            $stats['avg_processing_times'][$row->type] = [
                'seconds' => round($row->avg_seconds, 2),
                'samples' => $row->sample_size
            ];
        }

        return $stats;
    }

    /**
     * Gather user-related metrics for the past 24 hours
     * @return array
     */
    protected function gatherUserMetrics():array
    {
        $yesterday = date('Y-m-d H:i:s', strtotime('-24 hours'));

        $metrics = [
            'logins' => [
                'total' => 0,
                'by_role' => []
            ],
            'registrations' => [
                'total' => 0,
                'by_role' => []
            ],
            'approvals' => [
                'total' => 0,
                'by_role' => []
            ],
            'new_shops' => []
        ];

        // Get login counts from activity log
        $login_counts = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                COUNT(*) as count,
                MAX(CASE WHEN um.meta_value LIKE '%administrator%' THEN 1 ELSE 0 END) as admin,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_enthusiast%' THEN 1 ELSE 0 END) as enthusiast,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_artist%' THEN 1 ELSE 0 END) as artist,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_partner%' THEN 1 ELSE 0 END) as partner
            FROM
                {$this->wpdb->prefix}jvb_activity_log al
            JOIN
                {$this->wpdb->usermeta} um ON al.user_id = um.user_id AND um.meta_key = '{$this->wpdb->prefix}capabilities'
            WHERE
                al.type = 'login'
                AND al.created_at > %s
            GROUP BY
                al.user_id
        ", $yesterday));

        if (!empty($login_counts)) {
            foreach ($login_counts as $count) {
                $metrics['logins']['total'] += $count->count;
                if ($count->enthusiast) $metrics['logins']['by_role']['enthusiast'] = ($metrics['logins']['by_role']['enthusiast'] ?? 0) + $count->count;
                if ($count->artist) $metrics['logins']['by_role']['artist'] = ($metrics['logins']['by_role']['artist'] ?? 0) + $count->count;
                if ($count->partner) $metrics['logins']['by_role']['partner'] = ($metrics['logins']['by_role']['partner'] ?? 0) + $count->count;
                if ($count->admin) $metrics['logins']['by_role']['admin'] = ($metrics['logins']['by_role']['admin'] ?? 0) + $count->count;
            }
        }

        // Get new user registrations
        $new_users = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                u.ID,
                u.user_registered,
                um.meta_value as capabilities
            FROM
                {$this->wpdb->users} u
            JOIN
                {$this->wpdb->usermeta} um ON u.ID = um.user_id AND um.meta_key = '{$this->wpdb->prefix}capabilities'
            WHERE
                u.user_registered > %s
        ", $yesterday));

        $metrics['registrations']['total'] = count($new_users);

        foreach ($new_users as $user) {
            $capabilities = maybe_unserialize($user->capabilities);

            if (isset($capabilities['jvb_enthusiast'])) {
                $metrics['registrations']['by_role']['enthusiast'] = ($metrics['registrations']['by_role']['enthusiast'] ?? 0) + 1;
            }
            if (isset($capabilities['jvb_artist'])) {
                $metrics['registrations']['by_role']['artist'] = ($metrics['registrations']['by_role']['artist'] ?? 0) + 1;
            }
            if (isset($capabilities['jvb_partner'])) {
                $metrics['registrations']['by_role']['partner'] = ($metrics['registrations']['by_role']['partner'] ?? 0) + 1;
            }
        }

        // Get user approvals (role changes)
        $approvals = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                al.user_id,
                al.data,
                u.user_nicename
            FROM
                {$this->wpdb->prefix}jvb_activity_log al
            JOIN
                {$this->wpdb->users} u ON al.user_id = u.ID
            WHERE
                al.type = 'role_change'
                AND al.created_at > %s
        ", $yesterday));

        $metrics['approvals']['total'] = count($approvals);

        foreach ($approvals as $approval) {
            $data = json_decode($approval->data, true);
            if (isset($data['new_role'])) {
                if ($data['new_role'] === 'jvb_artist') {
                    $metrics['approvals']['by_role']['artist'] = ($metrics['approvals']['by_role']['artist'] ?? 0) + 1;
                    $metrics['approvals']['artists'][] = $approval->user_nicename;
                } elseif ($data['new_role'] === 'jvb_partner') {
                    $metrics['approvals']['by_role']['partner'] = ($metrics['approvals']['by_role']['partner'] ?? 0) + 1;
                    $metrics['approvals']['partners'][] = $approval->user_nicename;
                }
            }
        }

        // Get new shops
        $new_shops = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                t.term_id,
                t.name
            FROM
                {$this->wpdb->terms} t
            JOIN
                {$this->wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
            WHERE
                tt.taxonomy = 'jvb_shop'
                AND tt.description LIKE %s
        ", '%' . $this->wpdb->esc_like('"created_at":"' . date('Y-m-d', strtotime('-1 day'))) . '%'));

        foreach ($new_shops as $shop) {
            $metrics['new_shops'][] = [
                'id' => $shop->term_id,
                'name' => $shop->name
            ];
        }

        $metrics['new_shops_count'] = count($metrics['new_shops']);

        return $metrics;
    }

    /**
     * Gather content-related metrics for the past 24 hours
     * @return array
     */
    protected function gatherContentMetrics():array
    {
        $yesterday = date('Y-m-d H:i:s', strtotime('-24 hours'));

        $metrics = [
            'uploads' => [],
            'total_uploads' => 0,
            'by_user' => [],
            'taxonomy_growth' => []
        ];

        // Define content types to track
        $content_types = [
            'jvb_tattoo',
            'jvb_piercing',
            'jvb_artwork',
            'jvb_event',
            'jvb_offer'
        ];

        // Get counts for each content type
        foreach ($content_types as $type) {
            $count = $this->wpdb->get_var($this->wpdb->prepare("
                SELECT COUNT(*)
                FROM {$this->wpdb->posts}
                WHERE post_type = %s
                AND post_date > %s
            ", $type, $yesterday));

            $metrics['uploads'][str_replace(BASE, '', $type)] = (int)$count;
            $metrics['total_uploads'] += (int)$count;
        }

        // Get top uploaders
        $top_uploaders = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                post_author,
                COUNT(*) as count,
                (SELECT user_nicename FROM {$this->wpdb->users} WHERE ID = post_author) as username,
                post_type
            FROM
                {$this->wpdb->posts}
            WHERE
                post_type IN ('" . implode("','", $content_types) . "')
                AND post_date > %s
            GROUP BY
                post_author, post_type
            ORDER BY
                count DESC
            LIMIT 10
        ", $yesterday));

        foreach ($top_uploaders as $uploader) {
            if (!isset($metrics['by_user'][$uploader->post_author])) {
                $metrics['by_user'][$uploader->post_author] = [
                    'username' => $uploader->username,
                    'total' => 0,
                    'types' => []
                ];
            }

            $metrics['by_user'][$uploader->post_author]['types'][str_replace(BASE, '', $uploader->post_type)] = $uploader->count;
            $metrics['by_user'][$uploader->post_author]['total'] += $uploader->count;
        }

        // Sort by total uploads
        usort($metrics['by_user'], function ($a, $b) {
            return $b['total'] - $a['total'];
        });

        // Track taxonomy growth
        $taxonomies = [
            'jvb_style' => 'Tattoo Styles',
            'jvb_theme' => 'Tattoo Themes',
            'jvb_shop' => 'Shops',
            'jvb_city' => 'Cities',
            'jvb_artstyle' => 'Art Styles',
            'jvb_arttheme' => 'Art Themes',
            'jvb_pstyle' => 'Piercing Styles',
            'jvb_placement' => 'Placements',
        ];

        foreach ($taxonomies as $taxonomy => $label) {
            // Get new terms added in the last day
            $new_terms = $this->wpdb->get_results($this->wpdb->prepare("
                SELECT t.term_id, t.name
                FROM {$this->wpdb->terms} t
                JOIN {$this->wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
                WHERE tt.taxonomy = %s
                AND tt.description LIKE %s
            ", $taxonomy, '%' . $this->wpdb->esc_like('"created_at":"' . date('Y-m-d', strtotime('-1 day'))) . '%'));

            if (!empty($new_terms)) {
                $metrics['taxonomy_growth'][$taxonomy] = [
                    'label' => $label,
                    'count' => count($new_terms),
                    'terms' => array_map(function ($term) {
                        return [
                            'id' => $term->term_id,
                            'name' => $term->name
                        ];
                    }, $new_terms)
                ];
            } else {
                $metrics['taxonomy_growth'][$taxonomy] = [
                    'label' => $label,
                    'count' => 0,
                    'terms' => []
                ];
            }
        }

        return $metrics;
    }

    /**
     * Gather user retention metrics
     * @return array
     */
    protected function gatherUserRetentionMetrics():array
    {
        $metrics = [
            'active_periods' => [
                'daily' => 0,
                'weekly' => 0,
                'monthly' => 0
            ],
            'retention_by_role' => [],
            'inactive_counts' => [],
            'reactivated_users' => []
        ];

        // Get daily active users (logged in today)
        $daily_active = $this->wpdb->get_var("
            SELECT COUNT(DISTINCT user_id)
            FROM {$this->wpdb->prefix}jvb_activity_log
            WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
        ");
        $metrics['active_periods']['daily'] = (int)$daily_active;

        // Get weekly active users
        $weekly_active = $this->wpdb->get_var("
            SELECT COUNT(DISTINCT user_id)
            FROM {$this->wpdb->prefix}jvb_activity_log
            WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
        ");
        $metrics['active_periods']['weekly'] = (int)$weekly_active;

        // Get monthly active users
        $monthly_active = $this->wpdb->get_var("
            SELECT COUNT(DISTINCT user_id)
            FROM {$this->wpdb->prefix}jvb_activity_log
            WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
        ");
        $metrics['active_periods']['monthly'] = (int)$monthly_active;

        // Get active users by role type in the last week
        $active_by_role = $this->wpdb->get_results("
            SELECT
                MAX(CASE WHEN um.meta_value LIKE '%administrator%' THEN 1 ELSE 0 END) as admin,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_enthusiast%' THEN 1 ELSE 0 END) as enthusiast,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_artist%' THEN 1 ELSE 0 END) as artist,
                MAX(CASE WHEN um.meta_value LIKE '%jvb_partner%' THEN 1 ELSE 0 END) as partner,
                COUNT(DISTINCT al.user_id) as count
            FROM
                {$this->wpdb->prefix}jvb_activity_log al
            JOIN
                {$this->wpdb->usermeta} um ON al.user_id = um.user_id AND um.meta_key = '{$this->wpdb->prefix}capabilities'
            WHERE
                al.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
            GROUP BY
                CASE
                    WHEN um.meta_value LIKE '%administrator%' THEN 'admin'
                    WHEN um.meta_value LIKE '%jvb_artist%' THEN 'artist'
                    WHEN um.meta_value LIKE '%jvb_partner%' THEN 'partner'
                    WHEN um.meta_value LIKE '%jvb_enthusiast%' THEN 'enthusiast'
                    ELSE 'other'
                END
        ");

        foreach ($active_by_role as $role_data) {
            if ($role_data->admin) $metrics['retention_by_role']['admin'] = $role_data->count;
            if ($role_data->enthusiast) $metrics['retention_by_role']['enthusiast'] = $role_data->count;
            if ($role_data->artist) $metrics['retention_by_role']['artist'] = $role_data->count;
            if ($role_data->partner) $metrics['retention_by_role']['partner'] = $role_data->count;
        }

        // Count inactive users by duration
        $inactive_periods = [
            ['30 days', '3 months', 'inactive_1mo_3mo'],
            ['3 months', '6 months', 'inactive_3mo_6mo'],
            ['6 months', '1 year', 'inactive_6mo_1yr'],
            ['1 year', null, 'inactive_1yr_plus']
        ];

        foreach ($inactive_periods as $period) {
            $start_period = $period[0];
            $end_period = $period[1];
            $key = $period[2];

            $query = "
                SELECT COUNT(*)
                FROM {$this->wpdb->users} u
                WHERE NOT EXISTS (
                    SELECT 1
                    FROM {$this->wpdb->prefix}jvb_activity_log al
                    WHERE al.user_id = u.ID
                    AND al.created_at > DATE_SUB(NOW(), INTERVAL {$start_period})
                )
            ";

            if ($end_period) {
                $query .= " AND EXISTS (
                    SELECT 1
                    FROM {$this->wpdb->prefix}jvb_activity_log al
                    WHERE al.user_id = u.ID
                    AND al.created_at > DATE_SUB(NOW(), INTERVAL {$end_period})
                )";
            }

            $count = $this->wpdb->get_var($query);
            $metrics['inactive_counts'][$key] = (int)$count;
        }

        // Find reactivated users (inactive for 30+ days who logged in recently)
        $reactivated = $this->wpdb->get_results("
            SELECT
                al1.user_id,
                u.user_nicename,
                MAX(al1.created_at) as recent_login,
                DATEDIFF(
                    MAX(al1.created_at),
                    (
                        SELECT MAX(al2.created_at)
                        FROM {$this->wpdb->prefix}jvb_activity_log al2
                        WHERE al2.user_id = al1.user_id
                        AND al2.created_at < DATE_SUB(MAX(al1.created_at), INTERVAL 30 DAY)
                    )
                ) as days_inactive
            FROM
                {$this->wpdb->prefix}jvb_activity_log al1
            JOIN
                {$this->wpdb->users} u ON al1.user_id = u.ID
            WHERE
                al1.created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
            GROUP BY
                al1.user_id
            HAVING
                days_inactive > 30
            ORDER BY
                days_inactive DESC
            LIMIT 10
        ");

        foreach ($reactivated as $user) {
            $metrics['reactivated_users'][] = [
                'user_id' => $user->user_id,
                'username' => $user->user_nicename,
                'days_inactive' => $user->days_inactive,
                'recent_login' => $user->recent_login
            ];
        }

        return $metrics;
    }

    /**
     * Gather favourite patterns metrics
     * @return array
     */
    protected function gatherFavouriteMetrics():array
    {
        $metrics = [
            'total_favourites' => 0,
            'favourites_last_24h' => 0,
            'by_content_type' => [],
            'top_favourited' => [],
            'top_artists' => []
        ];

        $table = $this->wpdb->prefix . BASE . 'favourites';

        // Get total favourites
        $metrics['total_favourites'] = $this->wpdb->get_var("
            SELECT COUNT(*) FROM {$table}
        ");

        // Get favourites added in the last 24 hours
        $metrics['favourites_last_24h'] = $this->wpdb->get_var($this->wpdb->prepare("
            SELECT COUNT(*)
            FROM {$table}
            WHERE date_added > %s
        ", date('Y-m-d H:i:s', strtotime('-24 hours'))));

        // Get favourites by content type
        $by_type = $this->wpdb->get_results("
            SELECT type, COUNT(*) as count
            FROM {$table}
            GROUP BY type
            ORDER BY count DESC
        ");

        foreach ($by_type as $type) {
            $metrics['by_content_type'][$type->type] = $type->count;
        }

        // Get top favourited content by type
        $content_types = ['tattoo', 'piercing', 'artwork', 'artist', 'shop'];

        foreach ($content_types as $type) {
            $top_items = $this->wpdb->get_results($this->wpdb->prepare("
                SELECT
                    f.target_id,
                    COUNT(*) as favourite_count,
                    CASE
                        WHEN %s = 'artist' THEN (
                            SELECT user_nicename FROM {$this->wpdb->users}
                            WHERE ID = (SELECT post_author FROM {$this->wpdb->posts} WHERE ID = f.target_id)
                        )
                        WHEN %s = 'shop' THEN (
                            SELECT name FROM {$this->wpdb->terms} WHERE term_id = f.target_id
                        )
                        ELSE (
                            SELECT post_title FROM {$this->wpdb->posts} WHERE ID = f.target_id
                        )
                    END as name
                FROM
                    {$table} f
                WHERE
                    f.type = %s
                GROUP BY
                    f.target_id
                ORDER BY
                    favourite_count DESC
                LIMIT 5
            ", $type, $type, $type));

            if (!empty($top_items)) {
                $metrics['top_favourited'][$type] = array_map(function ($item) {
                    return [
                        'id' => $item->target_id,
                        'name' => $item->name,
                        'count' => $item->favourite_count
                    ];
                }, $top_items);
            }
        }

        // Get top artists by favourites received
        $top_artists = $this->wpdb->get_results("
            SELECT
                p.post_author as user_id,
                u.user_nicename as artist_name,
                COUNT(f.id) as favourite_count
            FROM
                {$table} f
            JOIN
                {$this->wpdb->posts} p ON f.target_id = p.ID
            JOIN
                {$this->wpdb->users} u ON p.post_author = u.ID
            WHERE
                f.type IN ('tattoo', 'piercing', 'artwork')
            GROUP BY
                p.post_author
            ORDER BY
                favourite_count DESC
            LIMIT 10
        ");

        foreach ($top_artists as $artist) {
            $metrics['top_artists'][] = [
                'user_id' => $artist->user_id,
                'name' => $artist->artist_name,
                'favourite_count' => $artist->favourite_count
            ];
        }

        return $metrics;
    }

    /**
     * Gather system health metrics
     * @return array
     */
    protected function gatherSystemHealthMetrics():array
    {
        $metrics = [
            'database' => [
                'size' => 0,
                'tables' => [],
                'growth' => []
            ],
            'media' => [
                'total_size' => 0,
                'file_count' => 0,
                'by_type' => []
            ],
            'performance' => [
                'average_response_time' => 0,
                'slow_queries' => []
            ],
            'cache' => [
                'hit_rate' => 0,
                'size' => 0
            ]
        ];

        // Get database size
        $db_size = $this->wpdb->get_results("
            SELECT
                table_schema as 'database',
                SUM(data_length + index_length) / 1024 / 1024 as size_mb
            FROM
                information_schema.TABLES
            WHERE
                table_schema = DATABASE()
            GROUP BY
                table_schema
        ");

        if (!empty($db_size)) {
            $metrics['database']['size'] = round($db_size[0]->size_mb, 2);
        }

        // Get largest tables
        $tables = $this->wpdb->get_results("
            SELECT
                table_name,
                ROUND((data_length + index_length) / 1024 / 1024, 2) as size_mb,
                table_rows
            FROM
                information_schema.TABLES
            WHERE
                table_schema = DATABASE()
            ORDER BY
                (data_length + index_length) DESC
            LIMIT 10
        ");

        foreach ($tables as $table) {
            $metrics['database']['tables'][] = [
                'name' => $table->table_name,
                'size_mb' => $table->size_mb,
                'rows' => $table->table_rows
            ];
        }

        // Get database growth (using estimations from previous reports or calculating based on wp_options)
        $db_size_history = get_option('jvb_db_size_history', []);
        $current_size = $metrics['database']['size'];

        // Store current size to history
        $db_size_history[date('Y-m-d')] = $current_size;
        if (count($db_size_history) > 30) {
            // Keep only last 30 days
            $db_size_history = array_slice($db_size_history, -30, 30, true);
        }
        update_option('jvb_db_size_history', $db_size_history);

        // Calculate growth rates
        if (count($db_size_history) > 1) {
            $dates = array_keys($db_size_history);
            $sizes = array_values($db_size_history);

            // Daily growth (if we have yesterday's data)
            $yesterday_idx = array_search(date('Y-m-d', strtotime('-1 day')), $dates);
            if ($yesterday_idx !== false) {
                $yesterday_size = $sizes[$yesterday_idx];
                $daily_growth = $current_size - $yesterday_size;
                $metrics['database']['growth']['daily'] = round($daily_growth, 2);
                $metrics['database']['growth']['daily_percent'] = round(($daily_growth / $yesterday_size) * 100, 2);
            }

            // Weekly growth
            $week_ago_idx = array_search(date('Y-m-d', strtotime('-7 days')), $dates);
            if ($week_ago_idx !== false) {
                $week_ago_size = $sizes[$week_ago_idx];
                $weekly_growth = $current_size - $week_ago_size;
                $metrics['database']['growth']['weekly'] = round($weekly_growth, 2);
                $metrics['database']['growth']['weekly_percent'] = round(($weekly_growth / $week_ago_size) * 100, 2);
            }

            // Monthly growth (estimate based on available data)
            if (count($db_size_history) >= 7) {
                $oldest_date = min(array_keys($db_size_history));
                $oldest_size = $db_size_history[$oldest_date];
                $days_diff = (strtotime('now') - strtotime($oldest_date)) / 86400;

                if ($days_diff > 0) {
                    $growth_per_day = ($current_size - $oldest_size) / $days_diff;
                    $projected_monthly = $growth_per_day * 30;
                    $metrics['database']['growth']['monthly_projected'] = round($projected_monthly, 2);
                    $metrics['database']['growth']['monthly_percent_projected'] = round(($projected_monthly / $current_size) * 100, 2);
                }
            }
        }

        // Get media storage stats
        $upload_dir = wp_upload_dir();
        $upload_base = $upload_dir['basedir'];

        if (function_exists('exec') && !in_array('exec', explode(',', ini_get('disable_functions')))) {
            // Use system du command if available for more accurate results
            exec("du -sk {$upload_base}", $output);
            if (!empty($output)) {
                // Extract size in KB and convert to MB
                $size_parts = explode("\t", $output[0]);
                $media_size_kb = (int)$size_parts[0];
                $metrics['media']['total_size'] = round($media_size_kb / 1024, 2);
            }

            // Count files by type
            exec("find {$upload_base} -type f | grep -c '\\.jpg", $jpg_count);
            exec("find {$upload_base} -type f | grep -c '\\.png", $png_count);
            exec("find {$upload_base} -type f | grep -c '\\.webp", $webp_count);
            exec("find {$upload_base} -type f | grep -c '\\.gif", $gif_count);

            $metrics['media']['by_type']['jpg'] = !empty($jpg_count) ? (int)$jpg_count[0] : 0;
            $metrics['media']['by_type']['png'] = !empty($png_count) ? (int)$png_count[0] : 0;
            $metrics['media']['by_type']['webp'] = !empty($webp_count) ? (int)$webp_count[0] : 0;
            $metrics['media']['by_type']['gif'] = !empty($gif_count) ? (int)$gif_count[0] : 0;

            $metrics['media']['file_count'] = array_sum($metrics['media']['by_type']);
        } else {
            // Fallback to database count if exec is not available
            $attachment_count = $this->wpdb->get_var("
                SELECT COUNT(*)
                FROM {$this->wpdb->posts}
                WHERE post_type = 'attachment'
            ");

            $metrics['media']['file_count'] = (int)$attachment_count;

            // Estimate size based on average attachment size and count
            // This is just a rough estimate
            $metrics['media']['total_size'] = round($attachment_count * 0.5, 2); // Assume 0.5MB average per file
        }

        // Get cache statistics
        // This is specific to your caching solution - this is a simple example for WP Object Cache
        if (wp_using_ext_object_cache()) {
            global $wp_object_cache;

            if (is_object($wp_object_cache) && method_exists($wp_object_cache, 'getStats')) {
                $cache_stats = $wp_object_cache->getStats();
                if (!empty($cache_stats)) {
                    $hits = $cache_stats['get'] ?? 0;
                    $misses = $cache_stats['misses'] ?? 0;
                    $total = $hits + $misses;

                    if ($total > 0) {
                        $metrics['cache']['hit_rate'] = round(($hits / $total) * 100, 2);
                    }

                    $metrics['cache']['size'] = round(($cache_stats['bytes'] ?? 0) / 1024 / 1024, 2);
                }
            }
        }

        return $metrics;
    }

    /**
     * Gather notification metrics
     * @return array
     */
    protected function gatherNotificationMetrics():array
    {
        $metrics = [
            'total_sent' => 0,
            'by_type' => [],
            'read_rate' => 0,
            'action_rate' => 0,
            'most_active_users' => []
        ];

        $yesterday = date('Y-m-d H:i:s', strtotime('-24 hours'));
        $notification_table = $this->wpdb->prefix . BASE . 'notifications_archive';
        $notification_meta = $this->wpdb->prefix . BASE . 'notifications_archive_meta';

        // Get total sent in last 24 hours
        $metrics['total_sent'] = $this->wpdb->get_var($this->wpdb->prepare("
            SELECT COUNT(*)
            FROM {$notification_table}
            WHERE post_date > %s
        ", $yesterday));

        // Get counts by notification type
        $by_type = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                meta_value as notification_type,
                COUNT(*) as count
            FROM
                {$notification_meta} m
            JOIN
                {$notification_table} n ON m.notification_id = n.ID
            WHERE
                meta_key = 'notification_type'
                AND n.post_date > %s
            GROUP BY
                meta_value
            ORDER BY
                count DESC
        ", $yesterday));

        foreach ($by_type as $type) {
            $metrics['by_type'][$type->notification_type] = $type->count;
        }

        // Calculate read rates
        $read_stats = $this->wpdb->get_row($this->wpdb->prepare("
            SELECT
                COUNT(*) as total,
                SUM(CASE WHEN m.meta_value = '1' THEN 1 ELSE 0 END) as read_count
            FROM
                {$notification_meta} m
            JOIN
                {$notification_table} n ON m.notification_id = n.ID
            WHERE
                m.meta_key = 'is_read'
                AND n.post_date > %s
        ", date('Y-m-d H:i:s', strtotime('-7 days'))));

        if ($read_stats && $read_stats->total > 0) {
            $metrics['read_rate'] = round(($read_stats->read_count / $read_stats->total) * 100, 2);
        }

        // Calculate action rates (notifications that led to clicks/actions)
        $action_stats = $this->wpdb->get_row($this->wpdb->prepare("
            SELECT
                COUNT(*) as total,
                SUM(CASE WHEN m.meta_value = '1' THEN 1 ELSE 0 END) as action_count
            FROM
                {$notification_meta} m
            JOIN
                {$notification_table} n ON m.notification_id = n.ID
            WHERE
                m.meta_key = 'actioned'
                AND n.post_date > %s
        ", date('Y-m-d H:i:s', strtotime('-7 days'))));

        if ($action_stats && $action_stats->total > 0) {
            $metrics['action_rate'] = round(($action_stats->action_count / $action_stats->total) * 100, 2);
        }

        // Get users with most notifications
        $most_notified = $this->wpdb->get_results($this->wpdb->prepare("
            SELECT
                post_author as user_id,
                (SELECT user_nicename FROM {$this->wpdb->users} WHERE ID = post_author) as username,
                COUNT(*) as notification_count
            FROM
                {$notification_table}
            WHERE
                post_date > %s
            GROUP BY
                post_author
            ORDER BY
                notification_count DESC
            LIMIT 10
        ", date('Y-m-d H:i:s', strtotime('-7 days'))));

        foreach ($most_notified as $user) {
            $metrics['most_active_users'][] = [
                'user_id' => $user->user_id,
                'username' => $user->username,
                'notification_count' => $user->notification_count
            ];
        }

        return $metrics;
    }

    /**
     * Format the daily report into an HTML email
     * @param array $error_stats
     * @param array $queue_stats
     * @param array $user_metrics
     * @param array $content_metrics
     * @param array $system_health
     * @param array $notification_metrics
     * @param array $user_retention
     * @param array $favourite_metrics
     *
     * @return string
     */
    protected function formatDailyReport(
        array $error_stats,
        array $queue_stats,
        array $user_metrics,
        array $content_metrics,
        array $system_health,
        array $notification_metrics,
        array $user_retention,
        array $favourite_metrics
    ):string {
        $date = date('Y-m-d');
        $css = $this->getEmailCSS();

        $html = "<!DOCTYPE html><html><head><style>{$css}</style></head><body>";
        $html .= "<div class='container'>";
        $html .= "<header><h1>edmonton.ink System Report</h1><p class='date'>{$date}</p></header>";

        // Executive Summary
        $html .= "<section class='summary'>";
        $html .= "<h2>Executive Summary</h2>";
        $html .= "<div class='summary-grid'>";
        $html .= "<div class='summary-item'><span class='label'>New Users</span><span class='value'>{$user_metrics['registrations']['total']}</span></div>";
        $html .= "<div class='summary-item'><span class='label'>Active Users (DAU)</span><span class='value'>{$user_retention['active_periods']['daily']}</span></div>";
        $html .= "<div class='summary-item'><span class='label'>Content Uploads</span><span class='value'>{$content_metrics['total_uploads']}</span></div>";
        $html .= "<div class='summary-item'><span class='label'>New Favourites</span><span class='value'>{$favourite_metrics['favourites_last_24h']}</span></div>";
        $html .= "<div class='summary-item'><span class='label'>Notifications</span><span class='value'>{$notification_metrics['total_sent']}</span></div>";
        $html .= "<div class='summary-item'><span class='label'>Errors</span><span class='value'>{$error_stats['total_errors']}</span></div>";
        $html .= "</div>";
        $html .= "</section>";

        // User Activity Section
        $html .= "<section>";
        $html .= "<h2>User Activity (Last 24 Hours)</h2>";

        // Login statistics
        $html .= "<div class='card'>";
        $html .= "<h3>Login Activity</h3>";
        $html .= "<p>Total Logins: <strong>{$user_metrics['logins']['total']}</strong></p>";
        if (!empty($user_metrics['logins']['by_role'])) {
            $html .= "<ul>";
            foreach ($user_metrics['logins']['by_role'] as $role => $count) {
                $role_name = ucfirst($role);
                $html .= "<li>{$role_name}s: {$count}</li>";
            }
            $html .= "</ul>";
        }
        $html .= "</div>";

        // Retention metrics
        $html .= "<div class='card'>";
        $html .= "<h3>User Retention</h3>";
        $html .= "<div class='stat-group'>";
        $html .= "<div class='stat-item'><span class='label'>Daily Active</span><span class='value'>{$user_retention['active_periods']['daily']}</span></div>";
        $html .= "<div class='stat-item'><span class='label'>Weekly Active</span><span class='value'>{$user_retention['active_periods']['weekly']}</span></div>";
        $html .= "<div class='stat-item'><span class='label'>Monthly Active</span><span class='value'>{$user_retention['active_periods']['monthly']}</span></div>";
        $html .= "</div>";

        // Inactive user metrics
        $html .= "<h4>Inactive Users</h4>";
        $html .= "<table class='data-table'>";
        $html .= "<tr><th>Period</th><th>Count</th></tr>";
        foreach ($user_retention['inactive_counts'] as $key => $count) {
            $label = str_replace(['inactive_', 'mo', 'yr', '_'], ['', ' months', ' year', '-'], $key);
            $html .= "<tr><td>{$label}</td><td>{$count}</td></tr>";
        }
        $html .= "</table>";
        $html .= "</div>";

        // Registration statistics
        $html .= "<div class='card'>";
        $html .= "<h3>New Registrations</h3>";
        $html .= "<p>Total New Users: <strong>{$user_metrics['registrations']['total']}</strong></p>";
        if (!empty($user_metrics['registrations']['by_role'])) {
            $html .= "<ul>";
            foreach ($user_metrics['registrations']['by_role'] as $role => $count) {
                $role_name = ucfirst($role);
                $html .= "<li>{$role_name}s: {$count}</li>";
            }
            $html .= "</ul>";
        }
        $html .= "</div>";

        // Approvals
        if ($user_metrics['approvals']['total'] > 0) {
            $html .= "<div class='card'>";
            $html .= "<h3>User Approvals</h3>";
            $html .= "<p>Total Approvals: <strong>{$user_metrics['approvals']['total']}</strong></p>";
            $html .= "<ul>";
            if (!empty($user_metrics['approvals']['artists'])) {
                $html .= "<li>Artists: " . count($user_metrics['approvals']['artists']) . " (" . implode(', ', $user_metrics['approvals']['artists']) . ")</li>";
            }
            if (!empty($user_metrics['approvals']['partners'])) {
                $html .= "<li>Partners: " . count($user_metrics['approvals']['partners']) . " (" . implode(', ', $user_metrics['approvals']['partners']) . ")</li>";
            }
            $html .= "</ul>";
            $html .= "</div>";
        }

        // New Shops
        if (!empty($user_metrics['new_shops'])) {
            $html .= "<div class='card'>";
            $html .= "<h3>New Shops</h3>";
            $html .= "<p>Total New Shops: <strong>{$user_metrics['new_shops_count']}</strong></p>";
            $html .= "<ul>";
            foreach ($user_metrics['new_shops'] as $shop) {
                $html .= "<li>{$shop['name']} (ID: {$shop['id']})</li>";
            }
            $html .= "</ul>";
            $html .= "</div>";
        }

        $html .= "</section>";

        // Content Metrics Section
        $html .= "<section>";
        $html .= "<h2>Content Activity</h2>";

        // Content uploads
        $html .= "<div class='card'>";
        $html .= "<h3>Content Uploads</h3>";
        $html .= "<p>Total Uploads: <strong>{$content_metrics['total_uploads']}</strong></p>";

        if (!empty($content_metrics['uploads'])) {
            $html .= "<div class='chart-container'>";
            foreach ($content_metrics['uploads'] as $type => $count) {
                $type_name = ucfirst($type);
                $percentage = ($content_metrics['total_uploads'] > 0)
                    ? round(($count / $content_metrics['total_uploads']) * 100)
                    : 0;

                $html .= "<div class='chart-row'>";
                $html .= "<span class='chart-label'>{$type_name}s</span>";
                $html .= "<div class='chart-bar-container'>";
                $html .= "<div class='chart-bar' style='width: {$percentage}%'></div>";
                $html .= "<span class='chart-value'>{$count}</span>";
                $html .= "</div>";
                $html .= "</div>";
            }
            $html .= "</div>";
        }

        // Top uploaders
        if (!empty($content_metrics['by_user'])) {
            $html .= "<h4>Top Content Contributors</h4>";
            $html .= "<ol class='top-list'>";
            foreach (array_slice($content_metrics['by_user'], 0, 5) as $user_data) {
                $html .= "<li><strong>{$user_data['username']}</strong>: {$user_data['total']} items (";
                $types = [];
                foreach ($user_data['types'] as $type => $count) {
                    $types[] = ucfirst($type) . "s: {$count}";
                }
                $html .= implode(', ', $types) . ")</li>";
            }
            $html .= "</ol>";
        }
        $html .= "</div>";

        // Taxonomy growth
        if (!empty($content_metrics['taxonomy_growth'])) {
            $html .= "<div class='card'>";
            $html .= "<h3>Taxonomy Growth</h3>";

            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Taxonomy</th><th>New Terms</th><th>Examples</th></tr>";

            foreach ($content_metrics['taxonomy_growth'] as $taxonomy => $data) {
                $examples = array_slice(array_map(function ($term) {
                    return $term['name'];
                }, $data['terms']), 0, 3);

                $example_text = !empty($examples) ? implode(', ', $examples) : 'None';

                $html .= "<tr>";
                $html .= "<td>{$data['label']}</td>";
                $html .= "<td>{$data['count']}</td>";
                $html .= "<td>{$example_text}</td>";
                $html .= "</tr>";
            }

            $html .= "</table>";
            $html .= "</div>";
        }

        // Favourite metrics
        $html .= "<div class='card'>";
        $html .= "<h3>Favourite Activity</h3>";
        $html .= "<p>New Favourites (24h): <strong>{$favourite_metrics['favourites_last_24h']}</strong></p>";
        $html .= "<p>Total Favourites: <strong>{$favourite_metrics['total_favourites']}</strong></p>";

        // Favourites by content type
        if (!empty($favourite_metrics['by_content_type'])) {
            $html .= "<h4>Favourites by Content Type</h4>";
            $html .= "<div class='chart-container'>";

            foreach ($favourite_metrics['by_content_type'] as $type => $count) {
                $percentage = ($favourite_metrics['total_favourites'] > 0)
                    ? round(($count / $favourite_metrics['total_favourites']) * 100)
                    : 0;

                $html .= "<div class='chart-row'>";
                $html .= "<span class='chart-label'>" . ucfirst($type) . "s</span>";
                $html .= "<div class='chart-bar-container'>";
                $html .= "<div class='chart-bar' style='width: {$percentage}%'></div>";
                $html .= "<span class='chart-value'>{$count}</span>";
                $html .= "</div>";
                $html .= "</div>";
            }

            $html .= "</div>";
        }

        // Top artists by favourites
        if (!empty($favourite_metrics['top_artists'])) {
            $html .= "<h4>Top Artists by Favourites</h4>";
            $html .= "<ol class='top-list'>";
            foreach (array_slice($favourite_metrics['top_artists'], 0, 5) as $artist) {
                $html .= "<li><strong>{$artist['name']}</strong>: {$artist['favourite_count']} favourites</li>";
            }
            $html .= "</ol>";
        }

        $html .= "</div>";
        $html .= "</section>";

        // System Health Section
        $html .= "<section>";
        $html .= "<h2>System Health</h2>";

        // Database metrics
        $html .= "<div class='card'>";
        $html .= "<h3>Database</h3>";
        $html .= "<p>Total Size: <strong>{$system_health['database']['size']} MB</strong></p>";

        // Database growth
        if (!empty($system_health['database']['growth'])) {
            $html .= "<h4>Growth</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Period</th><th>Growth (MB)</th><th>Percentage</th></tr>";

            if (isset($system_health['database']['growth']['daily'])) {
                $html .= "<tr><td>Daily</td><td>{$system_health['database']['growth']['daily']} MB</td><td>{$system_health['database']['growth']['daily_percent']}%</td></tr>";
            }

            if (isset($system_health['database']['growth']['weekly'])) {
                $html .= "<tr><td>Weekly</td><td>{$system_health['database']['growth']['weekly']} MB</td><td>{$system_health['database']['growth']['weekly_percent']}%</td></tr>";
            }

            if (isset($system_health['database']['growth']['monthly_projected'])) {
                $html .= "<tr><td>Monthly (Est.)</td><td>{$system_health['database']['growth']['monthly_projected']} MB</td><td>{$system_health['database']['growth']['monthly_percent_projected']}%</td></tr>";
            }

            $html .= "</table>";
        }

        // Largest tables
        if (!empty($system_health['database']['tables'])) {
            $html .= "<h4>Largest Tables</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Table</th><th>Size (MB)</th><th>Rows</th></tr>";

            foreach (array_slice($system_health['database']['tables'], 0, 5) as $table) {
                $html .= "<tr><td>{$table['name']}</td><td>{$table['size_mb']}</td><td>{$table['rows']}</td></tr>";
            }

            $html .= "</table>";
        }
        $html .= "</div>";

        // Media storage
        $html .= "<div class='card'>";
        $html .= "<h3>Media Storage</h3>";
        $html .= "<p>Total Size: <strong>{$system_health['media']['total_size']} MB</strong></p>";
        $html .= "<p>Total Files: <strong>{$system_health['media']['file_count']}</strong></p>";

        // Files by type
        if (!empty($system_health['media']['by_type'])) {
            $html .= "<h4>Files by Type</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Type</th><th>Count</th></tr>";

            foreach ($system_health['media']['by_type'] as $type => $count) {
                $html .= "<tr><td>.{$type}</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }
        $html .= "</div>";

        // Cache metrics
        if ($system_health['cache']['hit_rate'] > 0) {
            $html .= "<div class='card'>";
            $html .= "<h3>Cache Performance</h3>";
            $html .= "<p>Hit Rate: <strong>{$system_health['cache']['hit_rate']}%</strong></p>";
            $html .= "<p>Cache Size: <strong>{$system_health['cache']['size']} MB</strong></p>";
            $html .= "</div>";
        }
        $html .= "</section>";

        // Notification Metrics Section
        $html .= "<section>";
        $html .= "<h2>Notification Activity</h2>";

        $html .= "<div class='card'>";
        $html .= "<h3>Notification Stats</h3>";
        $html .= "<p>Total Sent (24h): <strong>{$notification_metrics['total_sent']}</strong></p>";
        $html .= "<p>Read Rate: <strong>{$notification_metrics['read_rate']}%</strong></p>";
        $html .= "<p>Action Rate: <strong>{$notification_metrics['action_rate']}%</strong></p>";

        // Notifications by type
        if (!empty($notification_metrics['by_type'])) {
            $html .= "<h4>Notifications by Type</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Type</th><th>Count</th></tr>";

            foreach ($notification_metrics['by_type'] as $type => $count) {
                $type_display = str_replace('_', ' ', $type);
                $html .= "<tr><td>" . ucfirst($type_display) . "</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }
        $html .= "</div>";

        // Most active users
        if (!empty($notification_metrics['most_active_users'])) {
            $html .= "<div class='card'>";
            $html .= "<h3>Most Notified Users</h3>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>User</th><th>Notifications</th></tr>";

            foreach (array_slice($notification_metrics['most_active_users'], 0, 5) as $user) {
                $html .= "<tr><td>{$user['username']}</td><td>{$user['notification_count']}</td></tr>";
            }

            $html .= "</table>";
            $html .= "</div>";
        }
        $html .= "</section>";

        // Error Statistics Section
        $html .= "<section>";
        $html .= "<h2>Error Statistics</h2>";

        $html .= "<div class='card'>";
        $html .= "<h3>Error Overview</h3>";
        $html .= "<p>Total Errors (24h): <strong>{$error_stats['total_errors']}</strong></p>";

        // Errors by severity
        if (!empty($error_stats['by_severity'])) {
            $html .= "<h4>Errors by Severity</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Severity</th><th>Count</th></tr>";

            foreach ($error_stats['by_severity'] as $severity => $count) {
                $html .= "<tr><td>" . ucfirst($severity) . "</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }

        // Errors by component
        if (!empty($error_stats['by_component'])) {
            $html .= "<h4>Errors by Component</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Component</th><th>Count</th></tr>";

            foreach ($error_stats['by_component'] as $component => $count) {
                $html .= "<tr><td>" . ucfirst($component) . "</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }

        // Most frequent errors
        if (!empty($error_stats['frequent'])) {
            $html .= "<h4>Most Frequent Errors</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Error Type</th><th>Component</th><th>Count</th></tr>";

            foreach (array_slice($error_stats['frequent'], 0, 5) as $error) {
                $html .= "<tr><td>{$error->error_type}</td><td>{$error->component}</td><td>{$error->count}</td></tr>";
            }

            $html .= "</table>";
        }

        // Critical errors
        if (!empty($error_stats['critical'])) {
            $html .= "<h4>Recent Critical Errors</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Error Type</th><th>Component</th><th>Time</th></tr>";

            foreach ($error_stats['critical'] as $error) {
                $time = date('H:i:s', strtotime($error->created_at));
                $html .= "<tr><td>{$error->error_type}</td><td>{$error->component}</td><td>{$time}</td></tr>";
            }

            $html .= "</table>";
        }
        $html .= "</div>";
        $html .= "</section>";

        // Queue Statistics Section
        $html .= "<section>";
        $html .= "<h2>Queue Statistics</h2>";

        $html .= "<div class='card'>";
        $html .= "<h3>Queue Overview</h3>";

        // Queue by status
        if (!empty($queue_stats['by_status'])) {
            $html .= "<div class='stat-group'>";

            foreach ($queue_stats['by_status'] as $status => $data) {
                $html .= "<div class='stat-item'>";
                $html .= "<span class='label'>" . ucfirst($status) . "</span>";
                $html .= "<span class='value'>{$data['count']}</span>";
                $html .= "<span class='subtext'>{$data['operations']} operations</span>";
                $html .= "</div>";
            }

            $html .= "</div>";
        }

        // Queue by type
        if (!empty($queue_stats['by_type'])) {
            $html .= "<h4>Queue by Operation Type</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Operation Type</th><th>Count</th></tr>";

            foreach ($queue_stats['by_type'] as $type => $count) {
                $html .= "<tr><td>{$type}</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }

        // Pending by priority
        if (!empty($queue_stats['pending_by_priority'])) {
            $html .= "<h4>Pending Operations by Priority</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Priority</th><th>Count</th></tr>";

            foreach ($queue_stats['pending_by_priority'] as $priority => $count) {
                $html .= "<tr><td>" . ucfirst($priority) . "</td><td>{$count}</td></tr>";
            }

            $html .= "</table>";
        }

        // Oldest pending operation
        if (!empty($queue_stats['oldest_pending'])) {
            $html .= "<h4>Oldest Pending Operation</h4>";
            $html .= "<p><strong>ID:</strong> {$queue_stats['oldest_pending']['id']}</p>";
            $html .= "<p><strong>Type:</strong> {$queue_stats['oldest_pending']['type']}</p>";
            $html .= "<p><strong>Created:</strong> {$queue_stats['oldest_pending']['created_at']}</p>";
            $html .= "<p><strong>Age:</strong> {$queue_stats['oldest_pending']['age_hours']} hours</p>";
        }

        // Recent failures
        if (!empty($queue_stats['recent_failures'])) {
            $html .= "<h4>Recent Failed Operations</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>ID</th><th>Type</th><th>Error</th></tr>";

            foreach ($queue_stats['recent_failures'] as $failure) {
                $html .= "<tr><td>{$failure->id}</td><td>{$failure->type}</td><td>{$failure->error_message}</td></tr>";
            }

            $html .= "</table>";
        }

        // Average processing times
        if (!empty($queue_stats['avg_processing_times'])) {
            $html .= "<h4>Average Processing Times</h4>";
            $html .= "<table class='data-table'>";
            $html .= "<tr><th>Operation Type</th><th>Average Time</th><th>Sample Size</th></tr>";

            foreach ($queue_stats['avg_processing_times'] as $type => $data) {
                $formatted_time = $this->formatSeconds($data['seconds']);
                $html .= "<tr><td>{$type}</td><td>{$formatted_time}</td><td>{$data['samples']}</td></tr>";
            }

            $html .= "</table>";
        }

        $html .= "</div>";
        $html .= "</section>";

        // Footer
        $html .= "<footer>";
        $html .= "<p>This report was automatically generated by edmonton.ink System Reporting. </p>";
        $html .= "<p>Next report will be delivered tomorrow at this time.</p>";
        $html .= "</footer>";

        $html .= "</div></body></html>";

        return $html;
    }

    /**
     * Format seconds into a human-readable string
     * @param float $seconds
     *
     * @return string
     */
    protected function formatSeconds(float $seconds):string
    {
        if ($seconds < 1) {
            return round($seconds * 1000) . " ms";
        } elseif ($seconds < 60) {
            return round($seconds, 2) . " seconds";
        } elseif ($seconds < 3600) {
            $minutes = floor($seconds / 60);
            $remaining_seconds = $seconds % 60;
            return "{$minutes}m " . round($remaining_seconds) . "s";
        } else {
            $hours = floor($seconds / 3600);
            $minutes = floor(($seconds % 3600) / 60);
            return "{$hours}h {$minutes}m";
        }
    }

    /**
     * Send email to admin
     * @param string $subject
     * @param string $content
     *
     * @return void
     */
    protected function sendAdminEmail(string $subject, string $content):void
    {
        $admin_email = get_option('admin_email');

        JVB()->email()->sendEmail($admin_email, $subject, $content);

        // Also send to any additional configured recipients
        $additional_recipients = get_option('jvb_report_recipients', []);
        if (!empty($additional_recipients)) {
            foreach ($additional_recipients as $recipient) {
				JVB()->email()->sendEmail($recipient, $subject, $content);
            }
        }
    }

    /**
     * Get CSS styles for email
     * @return string
     */
    protected function getEmailCSS():string
    {
        return '
        body {
            font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
            line-height: 1.6;
            color: #333;
            background-color: #f5f5f5;
            margin: 0;
            padding: 0;
        }
        .container {
            max-width: 900px;
            margin: 0 auto;
            background: #fff;
            padding: 20px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.1);
        }
        header {
            text-align: center;
            margin-bottom: 30px;
            padding-bottom: 20px;
            border-bottom: 1px solid #eee;
        }
        header h1 {
            color: #FF0080;
            margin: 0;
            font-size: 28px;
        }
        header .date {
            font-size: 16px;
            color: #777;
        }
        h2 {
            color: #333;
            padding-bottom: 10px;
            border-bottom: 2px solid #FF0080;
            margin-top: 40px;
        }
        h3 {
            color: #444;
            margin-top: 25px;
            margin-bottom: 15px;
        }
        h4 {
            color: #555;
            margin-top: 20px;
            margin-bottom: 10px;
            font-weight: 500;
        }
        section {
            margin-bottom: 40px;
        }
        .card {
            background: #fff;
            border: 1px solid #ddd;
            padding: 15px;
            margin-bottom: 15px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.05);
        }
        .summary {
            background-color: #f8f8f8;
            padding: 15px;
            margin: 20px 0;
            border-left: 4px solid #FF0080;
        }
        .summary-grid {
            display: grid;
            grid-template-columns: repeat(auto-fill, minmax(150px, 1fr));
            gap: 15px;
            margin-top: 15px;
        }
        .summary-item {
            text-align: center;
            padding: 10px;
            background: white;
            border-radius: 4px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.05);
        }
        .summary-item .label {
            display: block;
            font-size: 14px;
            color: #666;
            margin-bottom: 5px;
        }
        .summary-item .value {
            display: block;
            font-size: 24px;
            font-weight: bold;
            color: #FF0080;
        }
        .data-table {
            width: 100%;
            border-collapse: collapse;
            margin: 15px 0;
            font-size: 14px;
        }
        .data-table th {
            background-color: #f5f5f5;
            text-align: left;
            padding: 8px;
            border-bottom: 2px solid #ddd;
        }
        .data-table td {
            padding: 8px;
            border-bottom: 1px solid #ddd;
        }
        .data-table tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        .chart-container {
            margin: 20px 0;
        }
        .chart-row {
            display: flex;
            align-items: center;
            margin-bottom: 8px;
        }
        .chart-label {
            width: 120px;
            text-align: right;
            padding-right: 10px;
            font-size: 14px;
        }
        .chart-bar-container {
            flex: 1;
            height: 25px;
            background-color: #eee;
            border-radius: 3px;
            position: relative;
            overflow: hidden;
        }
        .chart-bar {
            height: 100%;
            background-color: #FF0080;
            border-radius: 3px 0 0 3px;
        }
        .chart-value {
            position: absolute;
            right: 10px;
            top: 50%;
            transform: translateY(-50%);
            color: #333;
            font-weight: bold;
            font-size: 14px;
            text-shadow: 0 0 5px white;
        }
        .stat-group {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            margin: 15px 0;
        }
        .stat-item {
            background: #f8f8f8;
            flex: 1;
            min-width: 120px;
            padding: 10px;
            text-align: center;
            border-radius: 3px;
        }
        .stat-item .label {
            font-size: 14px;
            color: #666;
            display: block;
        }
        .stat-item .value {
            font-size: 20px;
            font-weight: bold;
            display: block;
            margin: 5px 0;
            color: #333;
        }
        .stat-item .subtext {
            font-size: 12px;
            color: #999;
            display: block;
        }
        .top-list {
            margin: 15px 0;
            padding-left: 20px;
        }
        .top-list li {
            margin-bottom: 8px;
        }
        footer {
            text-align: center;
            margin-top: 30px;
            padding-top: 15px;
            border-top: 1px solid #eee;
            color: #777;
            font-size: 14px;
        }
        ';
    }

    /**
     * Add recipient to the report email list
     * @param string $email
     *
     * @return bool
     */
    public function addReportRecipient(string $email):bool
    {
        if (!is_email($email)) {
            return false;
        }

        $current_recipients = get_option('jvb_report_recipients', []);
        if (!in_array($email, $current_recipients)) {
            $current_recipients[] = $email;
            update_option('jvb_report_recipients', $current_recipients);
            return true;
        }

        return false;
    }

    /**
     * Remove recipient from the report email list
     * @param string $email
     *
     * @return bool
     */
    public function removeReportRecipient(string $email):bool
    {
        $current_recipients = get_option('jvb_report_recipients', []);
        $key = array_search($email, $current_recipients);

        if ($key !== false) {
            unset($current_recipients[$key]);
            update_option('jvb_report_recipients', array_values($current_recipients));
            return true;
        }

        return false;
    }

    /**
     * Change report frequency
     * @param string $interval
     *
     * @return bool
     */
    public function setReportInterval(string $interval):bool
    {
        if (!in_array($interval, ['daily', 'weekly'])) {
            return false;
        }

        // Clear existing schedule
        wp_clear_scheduled_hook('jvb_generate_daily_report');

        // Set new interval
        $this->report_interval = $interval;

        // Schedule new report
        if ($interval === 'daily') {
            wp_schedule_event(strtotime('tomorrow 01:00:00'), 'daily', 'jvb_generate_daily_report');
        } else {
            wp_schedule_event(strtotime('next monday 01:00:00'), 'weekly', 'jvb_generate_daily_report');
        }

        return true;
    }

    /**
     * Generate an on-demand report
     * @return bool
     */
    public function generateOnDemandReport():bool
    {
        // Collect metrics like in the daily report
        $error_stats = $this->gatherErrorStats();
        $queue_stats = $this->gatherQueueStats();
        $user_metrics = $this->gatherUserMetrics();
        $content_metrics = $this->gatherContentMetrics();
        $system_health = $this->gatherSystemHealthMetrics();
        $notification_metrics = $this->gatherNotificationMetrics();
        $user_retention = $this->gatherUserRetentionMetrics();
        $favourite_metrics = $this->gatherFavouriteMetrics();

        // Format report
        $subject = "[edmonton.ink] On-Demand System Report - " . date('Y-m-d H:i');
        $content = $this->formatDailyReport(
            $error_stats,
            $queue_stats,
            $user_metrics,
            $content_metrics,
            $system_health,
            $notification_metrics,
            $user_retention,
            $favourite_metrics
        );

        // Send to admin only
        $admin_email = get_option('admin_email');
        return JVB()->email()->sendEmail($admin_email, $subject, $content);
    }

    //List report:
    /**
     * @return bool
     */
    public function rebuildAllListStats():bool
    {
        $items_table = $this->wpdb->prefix . BASE . 'list_items';
        $stats_table = $this->wpdb->prefix . BASE . 'list_stats';

        // Start transaction
        $this->wpdb->query('START TRANSACTION');

        try {
            // Get distinct item types and IDs
            $distinct_items = $this->wpdb->get_results("
            SELECT DISTINCT item_type, item_id
            FROM {$items_table}
        ");

            // Prepare items for batch update
            $items_to_update = [];
            foreach ($distinct_items as $item) {
                $items_to_update[] = [$item->item_type, $item->item_id];
            }

            // Process in reasonable batches (500 items per batch)
            $batches = array_chunk($items_to_update, 500);

            foreach ($batches as $batch) {
                $this->batchUpdateListStats($batch);
            }

            // Commit transaction
            $this->wpdb->query('COMMIT');

            // Log success
            JVB()->error()->log(
                'favourites',
                'Successfully rebuilt all list statistics',
                ['items_processed' => count($distinct_items)],
                'info'
            );

            return true;
        } catch (Exception $e) {
            // Rollback on error
            $this->wpdb->query('ROLLBACK');

            JVB()->error()->log(
                'favourites',
                'Error rebuilding list statistics: ' . $e->getMessage(),
                [],
                'error'
            );

            return false;
        }
    }

    /**
     * Update list statistics for multiple items at once
     *
     * @param array $items Array of [type, id] pairs
     */
    protected function batchUpdateListStats(array $items):void
    {
        if (empty($items)) {
            return;
        }

        $stats_table = $this->wpdb->prefix . BASE . 'list_stats';
        $items_table = $this->wpdb->prefix . BASE . 'list_items';

        // Get counts for all items at once
        $query_parts = [];
        $query_params = [];

        foreach ($items as $item_data) {
            list($type, $id) = $item_data;
            $query_parts[] = "(item_type = %s AND item_id = %d)";
            $query_params[] = $type;
            $query_params[] = $id;
        }

        $count_query = "SELECT item_type, item_id, COUNT(DISTINCT list_id) as list_count
                   FROM {$items_table}
                   WHERE " . implode(' OR ', $query_parts) . "
                   GROUP BY item_type, item_id";

        $results = $this->wpdb->get_results($this->wpdb->prepare($count_query, $query_params));

        if (empty($results)) {
            return;
        }

        $current_time = current_time('mysql');
        $existing_items = [];

        // Find existing records
        $existing_query_parts = [];
        $existing_query_params = [];

        foreach ($results as $result) {
            $existing_query_parts[] = "(item_type = %s AND item_id = %d)";
            $existing_query_params[] = $result->item_type;
            $existing_query_params[] = $result->item_id;
        }

        $existing_query = "SELECT item_type, item_id
                      FROM {$stats_table}
                      WHERE " . implode(' OR ', $existing_query_parts);

        $existing_results = $this->wpdb->get_results($this->wpdb->prepare($existing_query, $existing_query_params));

        foreach ($existing_results as $existing) {
            $existing_items["{$existing->item_type}-{$existing->item_id}"] = true;
        }

        // Prepare update and insert statement formats
        $update_query = "UPDATE {$stats_table}
                    SET list_count = %d,
                        last_added = %s
                    WHERE item_type = %s AND item_id = %d";

        $insert_query = "INSERT INTO {$stats_table}
                    (item_type, item_id, list_count, last_added)
                    VALUES (%s, %d, %d, %s)";

        // Prepare batched updates and inserts
        $updates = [];
        $inserts = [];

        foreach ($results as $result) {
            $key = "{$result->item_type}-{$result->item_id}";

            if (isset($existing_items[$key])) {
                $updates[] = $this->wpdb->prepare(
                    $update_query,
                    $result->list_count,
                    $current_time,
                    $result->item_type,
                    $result->item_id
                );
            } else {
                $inserts[] = $this->wpdb->prepare(
                    "(%s, %d, %d, %s)",
                    $result->item_type,
                    $result->item_id,
                    $result->list_count,
                    $current_time
                );
            }
        }

        // Execute updates in batches
        if (!empty($updates)) {
            foreach ($updates as $update_sql) {
                $this->wpdb->query($update_sql);
            }
        }

        // Execute inserts in one go if possible
        if (!empty($inserts)) {
            $multi_insert = "INSERT INTO {$stats_table}
                        (item_type, item_id, list_count, last_added)
                        VALUES " . implode(', ', $inserts);
            $this->wpdb->query($multi_insert);
        }
    }
}