<?php
namespace JVBase\managers;

use JVBase\JVB;
use JVBase\managers\CacheManager;
use wpdb;
use WP_Error;
use Exception;

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

/**
 * Handles integration with Umami.js analytics to collect and provide metrics
 */
class UmamiMetrics
{
    protected wpdb $wpdb;
    protected string $api_url = 'https://cloud.umami.is/api';
    protected string $website_id;
    protected string $events_table;
    protected string $metrics_table;
    protected CacheManager $cache;

    /**
     * Constructor
     */
    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->events_table = $wpdb->prefix . BASE . 'umami_events';
        $this->metrics_table = $wpdb->prefix . BASE . 'performance_metrics';

        // Get Umami website ID from options
        $this->website_id = get_option('jvb_umami_website_id', UMAMI_WEBSITE_ID);

        // Initialize cache manager
        $this->cache = CacheManager::for('umami_metrics', DAY_IN_SECONDS);

        // Register hooks
        add_action('jvb_daily_umami_collection', [$this, 'collectDailyData']);
    }

    /**
     * Get authentication token for Umami API
     *
     * @return string|WP_Error Token or error
     */
    protected function getAuthToken():string|WP_Error
    {
        // Check if we have a cached token
        $token = get_transient('jvb_umami_auth_token');
        if ($token) {
            return $token;
        }

        // Get decrypted credentials
        $credentials = $this->getUmamiCredentials();
        if (!$credentials) {
            JVB()->error()->log(
                'umami',
                'Missing or invalid Umami API credentials',
                [],
                'error'
            );
            return new WP_Error('missing_credentials', 'Umami API credentials not configured');
        }

        // Authenticate with Umami
        $response = wp_remote_post($this->api_url . '/auth/login', [
            'body' => json_encode([
                'username' => $credentials['username'],
                'password' => $credentials['password']
            ]),
            'headers' => [
                'Content-Type' => 'application/json'
            ]
        ]);

        if (is_wp_error($response)) {
            JVB()->error()->log(
                'umami',
                'Failed to authenticate with Umami API: ' . $response->get_error_message(),
                [],
                'error'
            );
            return $response;
        }

        $data = json_decode(wp_remote_retrieve_body($response), true);

        if (empty($data['token'])) {
            JVB()->error()->log(
                'umami',
                'Invalid response from Umami API',
                ['response' => wp_remote_retrieve_body($response)],
                'error'
            );
            return new WP_Error('auth_failed', 'Failed to get auth token from Umami API');
        }

        // Cache token for 23 hours (tokens usually expire after 24 hours)
        set_transient('jvb_umami_auth_token', $data['token'], 23 * HOUR_IN_SECONDS);

        return $data['token'];
    }

    /**
     * Save Umami API credentials
     *
     * @param string $username Umami username
     * @param string $password Umami password
     * @return bool Success or failure
     */
    public function saveUmamiCredentials(string $username, string $password):bool
    {
        // Encrypt sensitive data
        $encrypted_username = $this->encryptData($username);
        $encrypted_password = $this->encryptData($password);

        // Store encrypted data
        $success1 = update_option('jvb_umami_username_encrypted', $encrypted_username);
        $success2 = update_option('jvb_umami_password_encrypted', $encrypted_password);

        return $success1 && $success2;
    }

    /**
     * Get Umami API credentials
     *
     * @return array|false Credentials or false on failure
     */
    protected function getUmamiCredentials():array|false
    {
        // Get encrypted credentials
        $encrypted_username = get_option('jvb_umami_username_encrypted');
        $encrypted_password = get_option('jvb_umami_password_encrypted');

        if (!$encrypted_username || !$encrypted_password) {
            return false;
        }

        // Decrypt
        $username = $this->decryptData($encrypted_username);
        $password = $this->decryptData($encrypted_password);

        if (!$username || !$password) {
            return false;
        }

        return [
            'username' => $username,
            'password' => $password
        ];
    }

    /**
     * Securely encrypt sensitive data
     *
     * @param string $data Data to encrypt
     * @return string Encrypted data
     */
    public function encryptData(string $data):string
    {
        // Get or generate encryption key
        $encryption_key = $this->getEncryptionKey();

        // Generate a random initialization vector
        $iv = openssl_random_pseudo_bytes(openssl_cipher_iv_length('aes-256-cbc'));

        // Encrypt the data
        $encrypted = openssl_encrypt($data, 'aes-256-cbc', $encryption_key, 0, $iv);

        // Combine IV and encrypted data for storage
        return base64_encode($iv . $encrypted);
    }

    /**
     * Decrypt sensitive data
     *
     * @param string $encrypted_data Encrypted data
     * @return string|bool Decrypted data or false on failure
     */
    public function decryptData(string $encrypted_data):string|bool
    {
        // Get encryption key
        $encryption_key = $this->getEncryptionKey();

        // Decode from base64
        $data = base64_decode($encrypted_data);
        if (!$data) {
            return false;
        }

        // Extract IV and encrypted data
        $iv_length = openssl_cipher_iv_length('aes-256-cbc');
        $iv = substr($data, 0, $iv_length);
        $encrypted = substr($data, $iv_length);

        // Decrypt
        return openssl_decrypt($encrypted, 'aes-256-cbc', $encryption_key, 0, $iv);
    }

    /**
     * Get or generate the encryption key
     *
     * @return string Encryption key
     */
    protected function getEncryptionKey():string
    {
        // Try to get existing key
        $key = get_option('jvb_encryption_key');

        // If no key exists, generate one and store it
        if (!$key) {
            // Generate a strong 32-byte key
            $key = bin2hex(openssl_random_pseudo_bytes(32));

            // Store key in WordPress options
            update_option('jvb_encryption_key', $key, true);
        }

        return $key;
    }

    /**
     * Fetch data from Umami API
     *
     * @param string $endpoint API endpoint
     * @param array $params Query parameters
     * @return array|WP_Error Response data or error
     */
    protected function fetchFromApi(string $endpoint, array $params = []):array|WP_Error
    {
        $token = $this->getAuthToken();
        if (is_wp_error($token)) {
            return $token;
        }

        $url = $this->api_url . $endpoint;
        if (!empty($params)) {
            $url = add_query_arg($params, $url);
        }

        $response = wp_remote_get($url, [
            'headers' => [
                'Authorization' => 'Bearer ' . $token
            ]
        ]);

        if (is_wp_error($response)) {
            JVB()->error()->log(
                'umami',
                'Error fetching from Umami API: ' . $response->get_error_message(),
                ['endpoint' => $endpoint, 'params' => $params],
                'error'
            );
            return $response;
        }

        $data = json_decode(wp_remote_retrieve_body($response), true);

        if (empty($data)) {
            JVB()->error()->log(
                'umami',
                'Empty response from Umami API',
                ['endpoint' => $endpoint, 'response' => wp_remote_retrieve_body($response)],
                'warning'
            );
            return new WP_Error('empty_response', 'Empty response from Umami API');
        }

        return $data;
    }

    /**
     * Collect daily analytics data from Umami and store in database
     *
     * @param string|null $date Optional date to collect (defaults to yesterday)
     * @return array Collection results
     */
    public function collectDailyData(string|null $date = null):array
    {
        // Default to yesterday if no date provided
        if (empty($date)) {
            $date = date('Y-m-d', strtotime('-1 day'));
        }

        $start_time = microtime(true);
        $results = [
            'date' => $date,
            'events_collected' => 0,
            'metrics_updated' => 0,
            'errors' => []
        ];

        try {
            // Fetch custom events from Umami
            $events_data = $this->fetchCustomEvents($date);

            if (is_wp_error($events_data)) {
                $results['errors'][] = 'Failed to fetch custom events: ' . $events_data->get_error_message();
                return $results;
            }

            // Process and store events
            $events_stored = $this->storeEvents($events_data, $date);
            $results['events_collected'] = count($events_stored);

            // Generate aggregated metrics
            $metrics_created = $this->generateDailyMetrics($date);
            $results['metrics_updated'] = $metrics_created;

            // Log success
            $duration = round(microtime(true) - $start_time, 2);
            $results['duration'] = $duration;

            JVB()->error()->log(
                'umami',
                'Successfully collected Umami data',
                [
                    'date' => $date,
                    'events' => count($events_stored),
                    'metrics' => $metrics_created,
                    'duration' => $duration
                ],
                'info'
            );

            // Clear cache for the processed date
            $this->cache->invalidate();
        } catch (Exception $e) {
            $results['errors'][] = 'Exception during data collection: ' . $e->getMessage();

            JVB()->error()->log(
                'umami',
                'Exception during Umami data collection: ' . $e->getMessage(),
                ['date' => $date],
                'error'
            );
        }

        return $results;
    }

    /**
     * Fetch custom events from Umami
     *
     * @param string $date Date to fetch in YYYY-MM-DD format
     * @return array|WP_Error Events data or error
     */
    protected function fetchCustomEvents(string $date):array|WP_Error
    {
        // Calculate start and end timestamps for the specified date
        $start_time = strtotime($date . ' 00:00:00');
        $end_time = strtotime($date . ' 23:59:59');

        return $this->fetchFromApi('/websites/' . $this->website_id . '/events', [
            'startAt' => $start_time,
            'endAt' => $end_time,
            'unit' => 'day'
        ]);
    }

    /**
     * Store events data in the database
     *
     * @param array $events_data Events from Umami API
     * @param string $date Date of events
     * @return array Stored event IDs
     */
    protected function storeEvents(array $events_data, string $date):array
    {
        $stored_ids = [];

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

        try {
            foreach ($events_data as $event) {
                // Extract data from the event
                $event_name = $event['event_name'] ?? '';
                $event_type = '';
                $user_id = null;
                $content_id = null;
                $content_type = null;
                $source_id = null;
                $source_type = null;
                $owner_id = null;
                $owner_type = null;
                $referrer = null;
                $metadata = [];

                // Process event data properties
                foreach ($event['event_data'] ?? [] as $key => $value) {
                    switch ($key) {
                        case 'type':
                            $event_type = $value;
                            break;
                        case 'user-id':
                            $user_id = (int)$value;
                            break;
                        case 'id':
                            $content_id = (int)$value;
                            break;
                        case 'content-type':
                            $content_type = $value;
                            break;
                        case 'source-id':
                            $source_id = (int)$value;
                            break;
                        case 'source-type':
                            $source_type = $value;
                            break;
                        case 'owner-id':
                            $owner_id = (int)$value;
                            break;
                        case 'owner-type':
                            $owner_type = $value;
                            break;
                        case 'from':
                            $referrer = $value;
                            break;
                        default:
                            // Store any other data as metadata
                            if ( str_starts_with( $key, 'meta-' ) ) {
                                $meta_key = str_replace('meta-', '', $key);
                                $metadata[$meta_key] = $value;
                            }
                    }
                }

                // Insert event into database
                $result = $this->wpdb->insert(
                    $this->events_table,
                    [
                        'date' => $date,
                        'timestamp' => date('Y-m-d H:i:s', $event['created_at'] ?? time()),
                        'event' => $event_name,
                        'event_type' => $event_type,
                        'user_id' => $user_id,
                        'content_id' => $content_id,
                        'content_type' => $content_type,
                        'source_id' => $source_id,
                        'source_type' => $source_type,
                        'owner_id' => $owner_id,
                        'owner_type' => $owner_type,
                        'referrer' => $referrer,
                        'metadata' => !empty($metadata) ? json_encode($metadata) : null
                    ]
                );

                if ($result) {
                    $stored_ids[] = $this->wpdb->insert_id;
                }
            }

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

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

            JVB()->error()->log(
                'umami',
                'Error storing Umami events: ' . $e->getMessage(),
                ['date' => $date],
                'error'
            );

            throw $e;
        }
    }

    /**
     * Generate daily aggregated metrics for all users
     *
     * @param string $date Date to generate metrics for
     * @return int Number of metrics records created/updated
     */
    protected function generateDailyMetrics(string $date):int
    {
        // Start transaction
        $this->wpdb->query('START TRANSACTION');

        try {
            $count = 0;

            // First, get all users who had activity on this date
            $active_users = $this->wpdb->get_col($this->wpdb->prepare(
                "SELECT DISTINCT owner_id FROM {$this->events_table}
                 WHERE date = %s AND owner_id IS NOT NULL",
                $date
            ));

            // Process metrics for each user
            foreach ($active_users as $user_id) {
                if ($this->generateUserMetrics($user_id, $date)) {
                    $count++;
                }
            }

            // Also generate metrics for all shops
            $active_shops = $this->wpdb->get_col($this->wpdb->prepare(
                "SELECT DISTINCT content_id FROM {$this->events_table}
                 WHERE date = %s AND event = 'view_shop' AND content_id IS NOT NULL",
                $date
            ));

            foreach ($active_shops as $shop_id) {
                if ($this->generateShopMetrics($shop_id, $date)) {
                    $count++;
                }
            }

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

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

            JVB()->error()->log(
                'umami',
                'Error generating metrics: ' . $e->getMessage(),
                ['date' => $date],
                'error'
            );

            throw $e;
        }
    }

    /**
     * Generate metrics for a specific user
     *
     * @param int $user_id User ID
     * @param string $date Date to generate metrics for
     * @return bool Success or failure
     */
    protected function generateUserMetrics(int $user_id, string $date):bool
    {
        // Get the artist profile ID for this user
        $artist_id = get_user_meta($user_id, BASE . 'link', true);
        if (!$artist_id) {
            return false;
        }

        // Calculate metrics
        $metrics = [
            'profile_view_count' => $this->countEvents($date, 'view_profile', $artist_id, 'content_id'),
            'feed_view_count' => $this->countEvents($date, 'view_feed', $artist_id, 'owner_id'),
            'taxonomy_view_count' => $this->countEvents($date, 'view_taxonomy', $artist_id, 'owner_id'),
            'shop_view_count' => $this->countEvents($date, 'view_shop', $artist_id, 'owner_id'),
            'favourite_count' => $this->countEvents($date, 'toggle_favourite', $artist_id, 'content_id', [
                'metadata_condition' => "JSON_EXTRACT(metadata, '$.action') = 'add'"
            ]),
            'upvote_count' => $this->countEvents($date, 'vote', $artist_id, 'content_id', [
                'metadata_condition' => "JSON_EXTRACT(metadata, '$.vote') = 'up'"
            ]),
            'downvote_count' => $this->countEvents($date, 'vote', $artist_id, 'content_id', [
                'metadata_condition' => "JSON_EXTRACT(metadata, '$.vote') = 'down'"
            ])
        ];

        // Calculate total view count
        $metrics['total_view_count'] = $metrics['profile_view_count'] +
                                       $metrics['feed_view_count'] +
                                       $metrics['taxonomy_view_count'] +
                                       $metrics['shop_view_count'];

        // Calculate karma
        $metrics['karma'] = $metrics['upvote_count'] - $metrics['downvote_count'];

        // Get top content
        $metrics['top_content'] = $this->getTopContent($date, $user_id);

        // Get source breakdown
        $metrics['source_breakdown'] = $this->getSourceBreakdown($date, $artist_id);

        // Get top favourites
        $metrics['top_favourites'] = $this->getTopFavourites($date, $user_id);

        // Calculate conversion rates
        if ($metrics['total_view_count'] > 0) {
            $metrics['favourite_conversion_rate'] = round($metrics['favourite_count'] / $metrics['total_view_count'], 4);
        } else {
            $metrics['favourite_conversion_rate'] = 0;
        }

        // Check if record exists for this date and user
        $exists = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT id FROM {$this->metrics_table}
             WHERE date = %s AND user_id = %d",
            $date,
            $user_id
        ));

        if ($exists) {
            // Update existing record
            $update_data = [
                'profile_view_count' => $metrics['profile_view_count'],
                'feed_view_count' => $metrics['feed_view_count'],
                'taxonomy_view_count' => $metrics['taxonomy_view_count'],
                'shop_view_count' => $metrics['shop_view_count'],
                'total_view_count' => $metrics['total_view_count'],
                'favourite_count' => $metrics['favourite_count'],
                'upvote_count' => $metrics['upvote_count'],
                'downvote_count' => $metrics['downvote_count'],
                'karma' => $metrics['karma'],
                'favourite_conversion_rate' => $metrics['favourite_conversion_rate'],
                'top_content' => json_encode($metrics['top_content']),
                'source_breakdown' => json_encode($metrics['source_breakdown']),
                'top_favourites' => json_encode($metrics['top_favourites'])
            ];

            $result = $this->wpdb->update(
                $this->metrics_table,
                $update_data,
                [
                    'date' => $date,
                    'user_id' => $user_id
                ]
            );
        } else {
            // Insert new record
            $insert_data = [
                'date' => $date,
                'user_id' => $user_id,
                'profile_view_count' => $metrics['profile_view_count'],
                'feed_view_count' => $metrics['feed_view_count'],
                'taxonomy_view_count' => $metrics['taxonomy_view_count'],
                'shop_view_count' => $metrics['shop_view_count'],
                'total_view_count' => $metrics['total_view_count'],
                'favourite_count' => $metrics['favourite_count'],
                'upvote_count' => $metrics['upvote_count'],
                'downvote_count' => $metrics['downvote_count'],
                'karma' => $metrics['karma'],
                'favourite_conversion_rate' => $metrics['favourite_conversion_rate'],
                'top_content' => json_encode($metrics['top_content']),
                'source_breakdown' => json_encode($metrics['source_breakdown']),
                'top_favourites' => json_encode($metrics['top_favourites'])
            ];

            $result = $this->wpdb->insert($this->metrics_table, $insert_data);
        }

        return $result !== false;
    }

    /**
     * Generate metrics for a specific shop
     *
     * @param int $shop_id Shop ID (term_id)
     * @param string $date Date to generate metrics for
     * @return bool Success or failure
     */
    protected function generateShopMetrics(int $shop_id, string $date):bool
    {
        // For now, we're just counting total views
        $view_count = $this->countEvents($date, 'view_shop', $shop_id, 'content_id');

        // Store in a separate shop metrics table or however you prefer
        // This is a placeholder for future implementation

        return true;
    }

    /**
     * Count events matching specific criteria
     *
     * @param string $date Date to query
     * @param string $event Event name
     * @param int $id ID to match
     * @param string $id_field Field to match ID against
     * @param array $extra_conditions Additional conditions
     * @return int Count of matching events
     */
    protected function countEvents(string $date, string $event, int $id, string $id_field, array $extra_conditions = []):int
    {
        $query = $this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->events_table}
             WHERE date = %s AND event = %s AND $id_field = %d",
            $date,
            $event,
            $id
        );

        // Add additional conditions
        if (!empty($extra_conditions['metadata_condition'])) {
            $query .= " AND " . $extra_conditions['metadata_condition'];
        }

        return (int)$this->wpdb->get_var($query);
    }

    /**
     * Get top content for a user
     *
     * @param string $date Date to query
     * @param int $user_id User ID
     * @return array Top content by content type
     */
    protected function getTopContent(string $date, int $user_id):array
    {
        $top_content = [];

        // Get content types for this user
        $content_types = $this->wpdb->get_col($this->wpdb->prepare(
            "SELECT DISTINCT content_type FROM {$this->events_table}
             WHERE date = %s AND owner_id = %d AND content_type IS NOT NULL",
            $date,
            $user_id
        ));

        foreach ($content_types as $type) {
            // Get top content items for this type
            $items = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT content_id, COUNT(*) as view_count
                 FROM {$this->events_table}
                 WHERE date = %s AND owner_id = %d AND content_type = %s AND event IN ('view_content', 'view_feed')
                 GROUP BY content_id
                 ORDER BY view_count DESC
                 LIMIT 5",
                $date,
                $user_id,
                $type
            ));

            if (!empty($items)) {
                $top_content[$type] = array_map(function ($item) {
                    return [
                        'id' => $item->content_id,
                        'views' => $item->view_count
                    ];
                }, $items);
            }
        }

        return $top_content;
    }

    /**
     * Get source breakdown for profile views
     *
     * @param string $date Date to query
     * @param int $artist_id Artist profile ID
     * @return array Source breakdown
     */
    protected function getSourceBreakdown(string $date, int $artist_id):array
    {
        $sources = [
            'direct' => 0,
            'feed' => 0,
            'taxonomy' => 0,
            'shop' => 0,
            'search' => 0,
            'other' => 0
        ];

        // Query for referrer distribution
        $results = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT referrer, COUNT(*) as count
             FROM {$this->events_table}
             WHERE date = %s AND event = 'view_profile' AND content_id = %d
             GROUP BY referrer",
            $date,
            $artist_id
        ));

        foreach ($results as $row) {
            $referrer = $row->referrer ?: 'direct';

            switch ($referrer) {
                case 'direct':
                    $sources['direct'] = $row->count;
                    break;
                case 'feed':
                    $sources['feed'] = $row->count;
                    break;
                case 'taxonomy':
                    $sources['taxonomy'] = $row->count;
                    break;
                case 'shop':
                    $sources['shop'] = $row->count;
                    break;
                case 'search':
                    $sources['search'] = $row->count;
                    break;
                default:
                    $sources['other'] += $row->count;
            }
        }

        return $sources;
    }

    /**
     * Get top favourited content for a user
     *
     * @param string $date Date to query
     * @param int $user_id User ID
     * @return array Top favourited content
     */
    protected function getTopFavourites(string $date, int $user_id):array
    {
        $results = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT content_id, content_type, COUNT(*) as fav_count
             FROM {$this->events_table}
             WHERE date = %s AND owner_id = %d
             AND event = 'toggle_favourite'
             AND JSON_EXTRACT(metadata, '$.action') = 'add'
             GROUP BY content_id, content_type
             ORDER BY fav_count DESC
             LIMIT 10",
            $date,
            $user_id
        ));

        $favourites = [];
        foreach ($results as $row) {
            if (!isset($favourites[$row->content_type])) {
                $favourites[$row->content_type] = [];
            }

            $favourites[$row->content_type][] = [
                'id' => $row->content_id,
                'count' => $row->fav_count
            ];
        }

        return $favourites;
    }

    /**
     * Get metrics for a user over a period
     *
     * @param int $user_id User ID
     * @param string $start_date Start date (YYYY-MM-DD)
     * @param string $end_date End date (YYYY-MM-DD)
     * @return array Metrics for the period
     */
    public function getUserMetrics(int $user_id, string $start_date, string $end_date):array
    {
        // Try to get from cache first
        $cache_key = "user_metrics_{$user_id}_{$start_date}_{$end_date}";
        $cached = $this->cache->get($cache_key);
        if ($cached) {
            return $cached;
        }

        // Query metrics for the time period
        $metrics = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM {$this->metrics_table}
             WHERE user_id = %d AND date BETWEEN %s AND %s
             ORDER BY date",
            $user_id,
            $start_date,
            $end_date
        ));

        // Prepare aggregated data
        $aggregated = [
            'period' => [
                'start' => $start_date,
                'end' => $end_date
            ],
            'totals' => [
                'profile_views' => 0,
                'feed_views' => 0,
                'taxonomy_views' => 0,
                'shop_views' => 0,
                'total_views' => 0,
                'favourites' => 0,
                'upvotes' => 0,
                'downvotes' => 0,
                'karma' => 0
            ],
            'conversion_rates' => [
                'favourite_rate' => 0
            ],
            'daily' => [],
            'top_content' => $this->aggregateTopContent($metrics),
            'source_breakdown' => $this->aggregateSourceBreakdown($metrics),
            'top_favourites' => $this->aggregateTopFavourites($metrics)
        ];

        // Process each day
        foreach ($metrics as $day) {
            // Add to totals
            $aggregated['totals']['profile_views'] += $day->profile_view_count;
            $aggregated['totals']['feed_views'] += $day->feed_view_count;
            $aggregated['totals']['taxonomy_views'] += $day->taxonomy_view_count;
            $aggregated['totals']['shop_views'] += $day->shop_view_count;
            $aggregated['totals']['total_views'] += $day->total_view_count;
            $aggregated['totals']['favourites'] += $day->favourite_count;
            $aggregated['totals']['upvotes'] += $day->upvote_count;
            $aggregated['totals']['downvotes'] += $day->downvote_count;
            $aggregated['totals']['karma'] += $day->karma;

            // Add daily data
            $aggregated['daily'][$day->date] = [
                'profile_views' => $day->profile_view_count,
                'feed_views' => $day->feed_view_count,
                'taxonomy_views' => $day->taxonomy_view_count,
                'shop_views' => $day->shop_view_count,
                'total_views' => $day->total_view_count,
                'favourites' => $day->favourite_count,
                'upvotes' => $day->upvote_count,
                'downvotes' => $day->downvote_count,
                'karma' => $day->karma
            ];
        }

        // Calculate conversion rates for the period
        if ($aggregated['totals']['total_views'] > 0) {
            $aggregated['conversion_rates']['favourite_rate'] = round(
                $aggregated['totals']['favourites'] / $aggregated['totals']['total_views'],
                4
            );
        }

        // Add growth metrics
        $aggregated['growth'] = $this->calculateGrowthMetrics($user_id, $start_date, $end_date);

        // Cache the results
        $this->cache->set($cache_key, $aggregated);

        return $aggregated;
    }

    /**
     * Calculate growth metrics comparing to previous period
     *
     * @param int $user_id User ID
     * @param string $start_date Start date
     * @param string $end_date End date
     * @return array Growth metrics
     */
    protected function calculateGrowthMetrics(int $user_id, string $start_date, string $end_date)
    {
        // Calculate the date range length
        $current_range_days = (strtotime($end_date) - strtotime($start_date)) / DAY_IN_SECONDS + 1;

        // Calculate previous period with same length
        $prev_end_date = date('Y-m-d', strtotime($start_date . ' -1 day'));
        $prev_start_date = date('Y-m-d', strtotime($prev_end_date . " -{$current_range_days} days +1 day"));

        // Get metrics for previous period
        $prev_metrics = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT
                SUM(profile_view_count) as profile_views,
                SUM(feed_view_count) as feed_views,
                SUM(taxonomy_view_count) as taxonomy_views,
                SUM(shop_view_count) as shop_views,
                SUM(total_view_count) as total_views,
                SUM(favourite_count) as favourites,
                SUM(upvote_count) as upvotes,
                SUM(downvote_count) as downvotes,
                SUM(karma) as karma
             FROM {$this->metrics_table}
             WHERE user_id = %d AND date BETWEEN %s AND %s",
            $user_id,
            $prev_start_date,
            $prev_end_date
        ));

        // Get current period totals
        $current_metrics = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT
                SUM(profile_view_count) as profile_views,
                SUM(feed_view_count) as feed_views,
                SUM(taxonomy_view_count) as taxonomy_views,
                SUM(shop_view_count) as shop_views,
                SUM(total_view_count) as total_views,
                SUM(favourite_count) as favourites,
                SUM(upvote_count) as upvotes,
                SUM(downvote_count) as downvotes,
                SUM(karma) as karma
             FROM {$this->metrics_table}
             WHERE user_id = %d AND date BETWEEN %s AND %s",
            $user_id,
            $start_date,
            $end_date
        ));

        $prev = !empty($prev_metrics) ? $prev_metrics[0] : null;
        $curr = !empty($current_metrics) ? $current_metrics[0] : null;

        // If no previous data, return zeros
        if (!$prev || !$curr) {
            return [
                'profile_views' => 0,
                'feed_views' => 0,
                'taxonomy_views' => 0,
                'shop_views' => 0,
                'total_views' => 0,
                'favourites' => 0,
                'upvotes' => 0,
                'downvotes' => 0,
                'karma' => 0
            ];
        }

        // Calculate percentage changes
        $growth = [];
        $metrics = [
            'profile_views', 'feed_views', 'taxonomy_views', 'shop_views',
            'total_views', 'favourites', 'upvotes', 'downvotes', 'karma'
        ];

        foreach ($metrics as $metric) {
            if ($prev->$metric > 0) {
                $growth[$metric] = round((($curr->$metric - $prev->$metric) / $prev->$metric) * 100, 1);
            } else {
                $growth[$metric] = $curr->$metric > 0 ? 100 : 0; // If previous was 0, any value is 100% growth
            }
        }

        return $growth;
    }

    /**
     * Aggregate top content from multiple days
     *
     * @param array $metrics Array of metrics objects
     * @return array Aggregated top content
     */
    protected function aggregateTopContent(array $metrics):array
    {
        $all_content = [];

        // Collect content from all days
        foreach ($metrics as $day) {
            $daily_content = json_decode($day->top_content, true);
            if (!empty($daily_content)) {
                foreach ($daily_content as $type => $content_items) {
                    if (!isset($all_content[$type])) {
                        $all_content[$type] = [];
                    }

                    foreach ($content_items as $item) {
                        $id = $item['id'];
                        if (!isset($all_content[$type][$id])) {
                            $all_content[$type][$id] = [
                                'id' => $id,
                                'views' => 0,
                                'title' => $this->getContentTitle($id, $type)
                            ];
                        }

                        $all_content[$type][$id]['views'] += $item['views'];
                    }
                }
            }
        }

        // Sort and trim each content type
        $result = [];
        foreach ($all_content as $type => $items) {
            // Convert to array and sort by views
            $items_array = array_values($items);
            usort($items_array, function ($a, $b) {
                return $b['views'] - $a['views'];
            });

            // Take top 10
            $result[$type] = array_slice($items_array, 0, 10);
        }

        return $result;
    }

    /**
     * Aggregate source breakdown from multiple days
     *
     * @param array $metrics Array of metrics objects
     * @return array Aggregated source breakdown
     */
    protected function aggregateSourceBreakdown(array $metrics):array
    {
        $totals = [
            'direct' => 0,
            'feed' => 0,
            'taxonomy' => 0,
            'shop' => 0,
            'search' => 0,
            'other' => 0
        ];

        foreach ($metrics as $day) {
            $sources = json_decode($day->source_breakdown, true);
            if (!empty($sources)) {
                foreach ($sources as $source => $count) {
                    $totals[$source] += $count;
                }
            }
        }

        return $totals;
    }

    /**
     * Aggregate top favourites from multiple days
     *
     * @param array $metrics Array of metrics objects
     * @return array Aggregated top favourites
     */
    protected function aggregateTopFavourites(array $metrics):array
    {
        $all_favourites = [];

        // Collect favourites from all days
        foreach ($metrics as $day) {
            $daily_favs = json_decode($day->top_favourites, true);
            if (!empty($daily_favs)) {
                foreach ($daily_favs as $type => $fav_items) {
                    if (!isset($all_favourites[$type])) {
                        $all_favourites[$type] = [];
                    }

                    foreach ($fav_items as $item) {
                        $id = $item['id'];
                        if (!isset($all_favourites[$type][$id])) {
                            $all_favourites[$type][$id] = [
                                'id' => $id,
                                'count' => 0,
                                'title' => $this->getContentTitle($id, $type)
                            ];
                        }

                        $all_favourites[$type][$id]['count'] += $item['count'];
                    }
                }
            }
        }

        // Sort and trim each content type
        $result = [];
        foreach ($all_favourites as $type => $items) {
            // Convert to array and sort by count
            $items_array = array_values($items);
            usort($items_array, function ($a, $b) {
                return $b['count'] - $a['count'];
            });

            // Take top 10
            $result[$type] = array_slice($items_array, 0, 10);
        }

        return $result;
    }

    /**
     * Get content title by ID and type
     *
     * @param int $id Content ID
     * @param string $type Content type
     * @return string Content title or ID if not found
     */
    protected function getContentTitle(int $id, string $type):string
    {
        // Try to get from cache first
        $cache_key = "content_title_{$type}_{$id}";
        $cached = $this->cache->get($cache_key);
        if ($cached) {
            return $cached;
        }

        $title = '';

        // Get title based on content type
        if (strpos($type, 'post_') === 0 ||
            in_array($type, ['tattoo', 'artist', 'piercing', 'artwork', 'event', 'offer'])) {
            // It's a post
            $post_id = $id;
            $post = get_post($post_id);
            if ($post) {
                $title = $post->post_title;
            }
        } elseif (in_array($type, ['shop', 'style', 'theme', 'city'])) {
            // It's a taxonomy term
            $term = get_term($id);
            if (!is_wp_error($term)) {
                $title = $term->name;
            }
        }

        // If still empty, use ID as fallback
        if (empty($title)) {
            $title = "#$id";
        }

        // Cache the result
        $this->cache->set($cache_key, $title, MONTH_IN_SECONDS);

        return $title;
    }

    /**
     * Get metrics for a shop over a period
     *
     * @param int $shop_id Shop ID (term_id)
     * @param string $start_date Start date (YYYY-MM-DD)
     * @param string $end_date End date (YYYY-MM-DD)
     * @return array Metrics for the period
     */
    public function getShopMetrics(int $shop_id, string $start_date, string $end_date):array
    {
        // Try to get from cache first
        $cache_key = "shop_metrics_{$shop_id}_{$start_date}_{$end_date}";
        $cached = $this->cache->get($cache_key);
        if ($cached) {
            return $cached;
        }

        // Query raw events for this shop
        $shop_views = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->events_table}
             WHERE event = 'view_shop'
             AND content_id = %d
             AND date BETWEEN %s AND %s",
            $shop_id,
            $start_date,
            $end_date
        ));

        // Get all artists for this shop
        $shop_artists = get_objects_in_term($shop_id, BASE . 'shop');
        $artist_user_ids = [];

        foreach ($shop_artists as $artist_post_id) {
            $user_id = get_post_meta($artist_post_id, BASE . 'link', true);
            if ($user_id) {
                $artist_user_ids[] = $user_id;
            }
        }

        // If no artists, return basic data
        if (empty($artist_user_ids)) {
            $shop_data = [
                'shop_id' => $shop_id,
                'period' => [
                    'start' => $start_date,
                    'end' => $end_date
                ],
                'views' => $shop_views,
                'artists' => 0,
                'artist_metrics' => []
            ];

            $this->cache->set($cache_key, $shop_data);
            return $shop_data;
        }

        // Format artist IDs for SQL
        $artist_ids_sql = implode(',', array_map('intval', $artist_user_ids));

        // Get aggregated artist metrics
        $artist_metrics = $this->wpdb->get_results(
            "SELECT
                user_id,
                SUM(profile_view_count) as profile_views,
                SUM(feed_view_count) as feed_views,
                SUM(taxonomy_view_count) as taxonomy_views,
                SUM(shop_view_count) as shop_views,
                SUM(total_view_count) as total_views,
                SUM(favourite_count) as favourites,
                SUM(upvote_count) as upvotes,
                SUM(downvote_count) as downvotes,
                SUM(karma) as karma
             FROM {$this->metrics_table}
             WHERE user_id IN ({$artist_ids_sql}) AND date BETWEEN '{$start_date}' AND '{$end_date}'
             GROUP BY user_id
             ORDER BY total_views DESC"
        );

        // Process artist metrics
        $formatted_artists = [];
        $totals = [
            'profile_views' => 0,
            'feed_views' => 0,
            'taxonomy_views' => 0,
            'shop_views' => 0,
            'total_views' => 0,
            'favourites' => 0,
            'upvotes' => 0,
            'downvotes' => 0,
            'karma' => 0
        ];

        foreach ($artist_metrics as $artist) {
            // Get artist name
            $artist_name = '';
            $artist_post_id = get_user_meta($artist->user_id, BASE . 'link', true);
            if ($artist_post_id) {
                $artist_name = get_post_field('post_title', $artist_post_id);
            }

            $formatted_artists[] = [
                'user_id' => $artist->user_id,
                'name' => $artist_name ?: 'Artist #' . $artist->user_id,
                'metrics' => [
                    'profile_views' => (int)$artist->profile_views,
                    'feed_views' => (int)$artist->feed_views,
                    'taxonomy_views' => (int)$artist->taxonomy_views,
                    'shop_views' => (int)$artist->shop_views,
                    'total_views' => (int)$artist->total_views,
                    'favourites' => (int)$artist->favourites,
                    'upvotes' => (int)$artist->upvotes,
                    'downvotes' => (int)$artist->downvotes,
                    'karma' => (int)$artist->karma
                ]
            ];

            // Add to totals
            $totals['profile_views'] += $artist->profile_views;
            $totals['feed_views'] += $artist->feed_views;
            $totals['taxonomy_views'] += $artist->taxonomy_views;
            $totals['shop_views'] += $artist->shop_views;
            $totals['total_views'] += $artist->total_views;
            $totals['favourites'] += $artist->favourites;
            $totals['upvotes'] += $artist->upvotes;
            $totals['downvotes'] += $artist->downvotes;
            $totals['karma'] += $artist->karma;
        }

        // Get daily view counts
        $daily_views = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT date, COUNT(*) as views
             FROM {$this->events_table}
             WHERE event = 'view_shop'
             AND content_id = %d
             AND date BETWEEN %s AND %s
             GROUP BY date
             ORDER BY date",
            $shop_id,
            $start_date,
            $end_date
        ));

        $views_by_day = [];
        foreach ($daily_views as $day) {
            $views_by_day[$day->date] = $day->views;
        }

        // Build final shop data
        $shop_data = [
            'shop_id' => $shop_id,
            'shop_name' => get_term_field('name', $shop_id, BASE . 'shop'),
            'period' => [
                'start' => $start_date,
                'end' => $end_date
            ],
            'views' => $shop_views,
            'views_by_day' => $views_by_day,
            'artists' => count($formatted_artists),
            'artist_metrics' => $formatted_artists,
            'totals' => $totals
        ];

        // Calculate growth compared to previous period
        $shop_data['growth'] = $this->calculateShopGrowth($shop_id, $shop_artists, $start_date, $end_date);

        // Cache the result
        $this->cache->set($cache_key, $shop_data);

        return $shop_data;
    }

    /**
     * Calculate shop growth metrics
     *
     * @param int $shop_id Shop ID
     * @param array $shop_artists Array of artist post IDs
     * @param string $start_date Start date
     * @param string $end_date End date
     * @return array Growth metrics
     */
    protected function calculateShopGrowth(int $shop_id, array $shop_artists, string $start_date, string $end_date):array
    {
        // Calculate the date range length
        $current_range_days = (strtotime($end_date) - strtotime($start_date)) / DAY_IN_SECONDS + 1;

        // Calculate previous period with same length
        $prev_end_date = date('Y-m-d', strtotime($start_date . ' -1 day'));
        $prev_start_date = date('Y-m-d', strtotime($prev_end_date . " -{$current_range_days} days +1 day"));

        // Get previous shop views
        $prev_shop_views = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->events_table}
             WHERE event = 'view_shop'
             AND content_id = %d
             AND date BETWEEN %s AND %s",
            $shop_id,
            $prev_start_date,
            $prev_end_date
        ));

        // Get current shop views
        $current_shop_views = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->events_table}
             WHERE event = 'view_shop'
             AND content_id = %d
             AND date BETWEEN %s AND %s",
            $shop_id,
            $start_date,
            $end_date
        ));

        // Calculate growth percentage for shop views
        $shop_view_growth = 0;
        if ($prev_shop_views > 0) {
            $shop_view_growth = round((($current_shop_views - $prev_shop_views) / $prev_shop_views) * 100, 1);
        } elseif ($current_shop_views > 0) {
            $shop_view_growth = 100; // If previously 0, any value is 100% growth
        }

        // Get artist user IDs
        $artist_user_ids = [];
        foreach ($shop_artists as $artist_post_id) {
            $user_id = get_post_meta($artist_post_id, BASE . 'link', true);
            if ($user_id) {
                $artist_user_ids[] = $user_id;
            }
        }

        // If no artists, just return shop views growth
        if (empty($artist_user_ids)) {
            return [
                'shop_views' => $shop_view_growth
            ];
        }

        // Format artist IDs for SQL
        $artist_ids_sql = implode(',', array_map('intval', $artist_user_ids));

        // Get previous period metrics for artists
        $prev_metrics = $this->wpdb->get_row(
            "SELECT
                SUM(profile_view_count) as profile_views,
                SUM(feed_view_count) as feed_views,
                SUM(taxonomy_view_count) as taxonomy_views,
                SUM(shop_view_count) as shop_views,
                SUM(total_view_count) as total_views,
                SUM(favourite_count) as favourites,
                SUM(upvote_count) as upvotes,
                SUM(downvote_count) as downvotes,
                SUM(karma) as karma
             FROM {$this->metrics_table}
             WHERE user_id IN ({$artist_ids_sql}) AND date BETWEEN '{$prev_start_date}' AND '{$prev_end_date}'"
        );

        // Get current period metrics for artists
        $current_metrics = $this->wpdb->get_row(
            "SELECT
                SUM(profile_view_count) as profile_views,
                SUM(feed_view_count) as feed_views,
                SUM(taxonomy_view_count) as taxonomy_views,
                SUM(shop_view_count) as shop_views,
                SUM(total_view_count) as total_views,
                SUM(favourite_count) as favourites,
                SUM(upvote_count) as upvotes,
                SUM(downvote_count) as downvotes,
                SUM(karma) as karma
             FROM {$this->metrics_table}
             WHERE user_id IN ({$artist_ids_sql}) AND date BETWEEN '{$start_date}' AND '{$end_date}'"
        );

        // Calculate growth percentages
        $growth = [
            'shop_views' => $shop_view_growth
        ];

        if ($prev_metrics && $current_metrics) {
            $metrics = [
                'profile_views', 'feed_views', 'taxonomy_views', 'shop_views',
                'total_views', 'favourites', 'upvotes', 'downvotes', 'karma'
            ];

            foreach ($metrics as $metric) {
                if ($prev_metrics->$metric > 0) {
                    $growth[$metric] = round((($current_metrics->$metric - $prev_metrics->$metric) / $prev_metrics->$metric) * 100, 1);
                } else {
                    $growth[$metric] = $current_metrics->$metric > 0 ? 100 : 0;
                }
            }
        }

        return $growth;
    }

    /**
     * Get dashboard summary for a user
     *
     * @param int $user_id User ID
     * @return array Dashboard summary data
     */
    public function getDashboardSummary(int $user_id):array
    {
        // Get metrics for different time periods
        $today = date('Y-m-d');
        $yesterday = date('Y-m-d', strtotime('-1 day'));
        $week_start = date('Y-m-d', strtotime('monday this week'));
        $month_start = date('Y-m-d', strtotime('first day of this month'));

        // Quick summary for today and yesterday
        $today_data = $this->getUserMetrics($user_id, $today, $today);
        $yesterday_data = $this->getUserMetrics($user_id, $yesterday, $yesterday);
        $week_data = $this->getUserMetrics($user_id, $week_start, $today);
        $month_data = $this->getUserMetrics($user_id, $month_start, $today);

        // Build quick summary for dashboard
        $summary = [
            'today' => [
                'total_views' => $today_data['totals']['total_views'],
                'profile_views' => $today_data['totals']['profile_views'],
                'favourites' => $today_data['totals']['favourites'],
                'karma' => $today_data['totals']['karma']
            ],
            'yesterday' => [
                'total_views' => $yesterday_data['totals']['total_views'],
                'profile_views' => $yesterday_data['totals']['profile_views'],
                'favourites' => $yesterday_data['totals']['favourites'],
                'karma' => $yesterday_data['totals']['karma']
            ],
            'this_week' => [
                'total_views' => $week_data['totals']['total_views'],
                'profile_views' => $week_data['totals']['profile_views'],
                'favourites' => $week_data['totals']['favourites'],
                'karma' => $week_data['totals']['karma']
            ],
            'this_month' => [
                'total_views' => $month_data['totals']['total_views'],
                'profile_views' => $month_data['totals']['profile_views'],
                'favourites' => $month_data['totals']['favourites'],
                'karma' => $month_data['totals']['karma']
            ],
            'growth' => [
                'day_over_day' => [
                    'total_views' => $this->calculatePercentageChange(
                        $yesterday_data['totals']['total_views'],
                        $today_data['totals']['total_views']
                    ),
                    'profile_views' => $this->calculatePercentageChange(
                        $yesterday_data['totals']['profile_views'],
                        $today_data['totals']['profile_views']
                    ),
                    'favourites' => $this->calculatePercentageChange(
                        $yesterday_data['totals']['favourites'],
                        $today_data['totals']['favourites']
                    ),
                    'karma' => $this->calculateAbsoluteChange(
                        $yesterday_data['totals']['karma'],
                        $today_data['totals']['karma']
                    )
                ]
            ],
            'top_content' => $this->simplifyTopContent($week_data['top_content']),
            'sources' => $week_data['source_breakdown']
        ];

        return $summary;
    }

    /**
     * Calculate percentage change between two values
     *
     * @param int $old Previous value
     * @param int $new Current value
     * @return float Percentage change
     */
    protected function calculatePercentageChange(int $old, int $new):float
    {
        if ($old == 0) {
            return $new > 0 ? 100 : 0;
        }

        return round((($new - $old) / $old) * 100, 1);
    }

    /**
     * Calculate absolute change between two values
     *
     * @param int $old Previous value
     * @param int $new Current value
     * @return int Absolute change
     */
    protected function calculateAbsoluteChange(int $old, int $new):int
    {
        return $new - $old;
    }

    /**
     * Simplify top content for dashboard view
     *
     * @param array $top_content Full top content data
     * @return array Simplified top content
     */
    protected function simplifyTopContent(array $top_content):array
    {
        $simplified = [];

        foreach ($top_content as $type => $items) {
            // Take just top 3 items
            $simplified[$type] = array_slice($items, 0, 3);
        }

        return $simplified;
    }

    /**
     * Update table schema with needed columns
     */
    public static function createTableSchema()
    {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        // Events table
        $events_table = $wpdb->prefix . BASE . 'umami_events';
        $events_schema = "CREATE TABLE IF NOT EXISTS {$events_table} (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `date` date NOT NULL,
            `timestamp` datetime NOT NULL,
            `event` varchar(50) NOT NULL,
            `event_type` varchar(50) NOT NULL,
            `user_id` bigint(20) unsigned DEFAULT NULL,
            `content_id` bigint(20) unsigned DEFAULT NULL,
            `content_type` varchar(50) DEFAULT NULL,
            `source_id` bigint(20) unsigned DEFAULT NULL,
            `source_type` varchar(50) DEFAULT NULL,
            `owner_id` bigint(20) unsigned DEFAULT NULL,
            `owner_type` varchar(50) DEFAULT NULL,
            `referrer` varchar(100) DEFAULT NULL,
            `metadata` JSON DEFAULT NULL,
            PRIMARY KEY (`id`),
            KEY `date_idx` (`date`),
            KEY `event_idx` (`event`, `event_type`),
            KEY `content_idx` (`content_type`, `content_id`),
            KEY `user_idx` (`user_id`),
            KEY `owner_idx` (`owner_id`)
        ) {$charset_collate};";

        // Performance metrics table
        $metrics_table = $wpdb->prefix . BASE . 'performance_metrics';
        $metrics_schema = "CREATE TABLE IF NOT EXISTS {$metrics_table} (
            `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            `date` date NOT NULL,
            `user_id` bigint(20) unsigned DEFAULT NULL,
            `profile_view_count` bigint(20) unsigned DEFAULT 0,
            `feed_view_count` bigint(20) unsigned DEFAULT 0,
            `taxonomy_view_count` bigint(20) unsigned DEFAULT 0,
            `shop_view_count` bigint(20) unsigned DEFAULT 0,
            `total_view_count` bigint(20) unsigned DEFAULT 0,
            `favourite_count` bigint(20) unsigned DEFAULT 0,
            `top_content` json DEFAULT NULL,
            `source_breakdown` json DEFAULT NULL,
            `top_favourites` json DEFAULT NULL,
            `upvote_count` bigint(20) unsigned DEFAULT 0,
            `downvote_count` bigint(20) unsigned DEFAULT 0,
            `karma` bigint(20) unsigned DEFAULT 0,
            `favourite_conversion_rate` float DEFAULT 0,
            PRIMARY KEY (`id`),
            UNIQUE KEY `user_date_idx` (`user_id`, `date`),
            KEY `date_idx` (`date`)
        ) {$charset_collate};";

        // Execute schema creation
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($events_schema);
        dbDelta($metrics_schema);
    }

    /**
     * Handle database upgrades for schema changes
     */
    public static function upgradeDatabase()
    {
        self::createTableSchema();
    }

    /**
     * Render the Umami credentials settings form
     */
    public function renderCredentialsForm()
    {
        // Check capability
        if (!current_user_can('manage_options')) {
            wp_die(__('You do not have sufficient permissions to access this page.'));
        }

        // Handle form submission
        if (isset($_POST['jvb_umami_credentials_nonce']) && wp_verify_nonce($_POST['jvb_umami_credentials_nonce'], 'jvb_saveUmamiCredentials')) {
            if (isset($_POST['jvb_umami_username']) && isset($_POST['jvb_umami_password'])) {
                $username = sanitize_text_field($_POST['jvb_umami_username']);
                $password = $_POST['jvb_umami_password']; // Don't sanitize password as it might contain special chars

                if ($this->saveUmamiCredentials($username, $password)) {
                    // Clear token cache to force re-authentication
                    delete_transient('jvb_umami_auth_token');
                    echo '<div class="updated"><p>Credentials saved successfully!</p></div>';

                    // Test connection
                    $test_result = $this->testUmamiConnection();
                    if (is_wp_error($test_result)) {
                        echo '<div class="error"><p>Connection test failed: ' . esc_html($test_result->get_error_message()) . '</p></div>';
                    } else {
                        echo '<div class="updated"><p>Connection test successful!</p></div>';
                    }
                } else {
                    echo '<div class="error"><p>Failed to save credentials.</p></div>';
                }
            }
        }

        // Render form
        ?>
        <div class="wrap">
            <h2>Umami Analytics Credentials</h2>
            <form method="post" action="">
                <?php wp_nonce_field('jvb_saveUmamiCredentials', 'jvb_umami_credentials_nonce'); ?>
                <table class="form-table">
                    <tr>
                        <th scope="row"><label for="jvb_umami_username">Username</label></th>
                        <td><input type="text" id="jvb_umami_username" name="jvb_umami_username" class="regular-text"></td>
                    </tr>
                    <tr>
                        <th scope="row"><label for="jvb_umami_password">Password</label></th>
                        <td><input type="password" id="jvb_umami_password" name="jvb_umami_password" class="regular-text"></td>
                    </tr>
                    <tr>
                        <th scope="row"><label for="jvb_umami_website_id">Website ID</label></th>
                        <td>
                            <input type="text" id="jvb_umami_website_id" name="jvb_umami_website_id" class="regular-text" value="<?= esc_attr(get_option('jvb_umami_website_id', UMAMI_WEBSITE_ID)); ?>">
                            <p class="description">The Umami website ID for tracking.</p>
                        </td>
                    </tr>
                </table>
                <p class="submit">
                    <input type="submit" name="submit" id="submit" class="button button-primary" value="Save Credentials">
                </p>
            </form>
        </div>
        <?php
    }

    /**
     * Test connection to Umami API
     *
     * @return bool|WP_Error True on success, WP_Error on failure
     */
    public function testUmamiConnection()
    {
        $token = $this->getAuthToken();
        if (is_wp_error($token)) {
            return $token;
        }

        // Try a simple API call
        $test_response = wp_remote_get($this->api_url . '/me', [
            'headers' => [
                'Authorization' => 'Bearer ' . $token
            ]
        ]);

        if (is_wp_error($test_response)) {
            return $test_response;
        }

        $response_code = wp_remote_retrieve_response_code($test_response);
        if ($response_code !== 200) {
            return new WP_Error(
                'connection_failed',
                'API responded with code: ' . $response_code
            );
        }

        return true;
    }
}