<?php

/**
 * Link Click Tracker
 * 
 * Tracks clicks on internal links for analytics
 */

declare(strict_types=1);

namespace ProRank\SEO\Core;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}


/**
 * LinkClickTracker class
 */


class LinkClickTracker {
    
    /**
     * Database table name
     * 
     * @var string
     */
    private string $table_name;

    /**
     * Cached column map for link clicks table.
     *
     * @var array<string,bool>|null
     */
    private ?array $table_columns = null;

    /**
     * Cached click-tracking enablement state.
     *
     * @var bool|null
     */
    private ?bool $tracking_enabled = null;
    
    /**
     * Constructor
     */
    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'prorank_link_clicks';
    }
    
    /**
     * Initialize the tracker
     */
    public static function init(): void {
        $instance = new self();
        
        // Add hooks
        add_action('wp_enqueue_scripts', [$instance, 'enqueue_tracking_script']);
        add_action('wp_ajax_prorank_track_link_click', [$instance, 'track_click']);
        add_action('wp_ajax_nopriv_prorank_track_link_click', [$instance, 'track_click']);
        add_action('update_option_prorank_seo_enable_click_tracking', [$instance, 'handle_tracking_flag_update'], 10, 2);
        add_action('update_option_prorank_link_tracking_enabled', [$instance, 'handle_tracking_flag_update'], 10, 2);
        add_action('update_option_prorank_internal_linking_settings', [$instance, 'handle_internal_linking_settings_update'], 10, 2);
        add_action('update_option_prorank_seo_internal_linking', [$instance, 'handle_internal_linking_settings_update'], 10, 2);
        add_action('update_option_prorank_seo_internal_linking_settings', [$instance, 'handle_internal_linking_settings_update'], 10, 2);
        
        // Create table on activation
        register_activation_hook(PRORANK_PLUGIN_FILE, [$instance, 'create_table']);
    }

    /**
     * Handle direct click tracking flag updates.
     *
     * @param mixed $old_value Previous option value.
     * @param mixed $new_value New option value.
     * @return void
     */
    public function handle_tracking_flag_update($old_value, $new_value): void {
        $old_enabled = $this->normalize_bool($old_value);
        $new_enabled = $this->normalize_bool($new_value);

        if ($old_enabled === $new_enabled) {
            return;
        }

        $this->tracking_enabled = null;
        $this->purge_html_cache();
    }

    /**
     * Handle grouped internal-linking settings updates.
     *
     * @param mixed $old_value Previous settings array.
     * @param mixed $new_value New settings array.
     * @return void
     */
    public function handle_internal_linking_settings_update($old_value, $new_value): void {
        $old_settings = is_array($old_value) ? $old_value : [];
        $new_settings = is_array($new_value) ? $new_value : [];

        $old_enabled = array_key_exists('enable_click_tracking', $old_settings)
            ? $this->normalize_bool($old_settings['enable_click_tracking'])
            : null;
        $new_enabled = array_key_exists('enable_click_tracking', $new_settings)
            ? $this->normalize_bool($new_settings['enable_click_tracking'])
            : null;

        if ($old_enabled === $new_enabled) {
            return;
        }

        $this->tracking_enabled = null;
        $this->purge_html_cache();
    }

    /**
     * Purge ProRank HTML cache files so script toggles reflect immediately.
     *
     * @return void
     */
    private function purge_html_cache(): void {
        $upload_dir = wp_upload_dir();
        $base_dir = isset($upload_dir['basedir']) ? (string) $upload_dir['basedir'] : '';
        if ($base_dir === '') {
            return;
        }

        $cache_root = trailingslashit($base_dir) . 'prorank-cache/html';
        if (!is_dir($cache_root)) {
            return;
        }

        $iterator = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($cache_root, \FilesystemIterator::SKIP_DOTS),
            \RecursiveIteratorIterator::CHILD_FIRST
        );

        foreach ($iterator as $path_info) {
            $path = $path_info->getPathname();
            if ($path_info->isDir()) {
                \ProRank\SEO\Core\FilesystemHelper::delete_dir($path, false);
                continue;
            }
            \ProRank\SEO\Core\FilesystemHelper::delete_file($path);
        }
    }
    
    /**
     * Create database table for link clicks
     */
    public function create_table(): void {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            source_id bigint(20) unsigned NOT NULL,
            target_id bigint(20) unsigned DEFAULT NULL,
            target_url text NOT NULL,
            click_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            user_id bigint(20) unsigned DEFAULT NULL,
            session_id varchar(32) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY source_id (source_id),
            KEY target_id (target_id),
            KEY click_date (click_date)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
    
    /**
     * Enqueue tracking script
     */
    public function enqueue_tracking_script(): void {
        if (!is_singular()) {
            return;
        }

        if (!$this->is_tracking_enabled()) {
            return;
        }

        $script_relative_path = 'assets/js/link-tracker.js';
        $script_path = defined('PRORANK_PLUGIN_DIR') ? PRORANK_PLUGIN_DIR . $script_relative_path : '';
        $script_version = ($script_path !== '' && file_exists($script_path))
            ? (string) filemtime($script_path)
            : PRORANK_VERSION;
        
        wp_enqueue_script(
            'prorank-link-tracker',
            PRORANK_PLUGIN_URL . $script_relative_path,
            ['jquery'],
            $script_version,
            true
        );
        
        wp_localize_script('prorank-link-tracker', 'prorankLinkTracker', [
            'ajaxUrl' => admin_url('admin-ajax.php'),
            'nonce' => wp_create_nonce('prorank_link_tracker'),
            'postId' => get_the_ID(),
            'siteUrl' => get_site_url(),
        ]);
    }
    
    /**
     * Track link click via AJAX
     */
    public function track_click(): void {
        if (!$this->is_tracking_enabled()) {
            wp_send_json_error('Click tracking is disabled');
            return;
        }

        // Verify nonce
        $nonce = (string) prorank_get_input_value($_POST, 'nonce', '');
        if ($nonce === '' || !wp_verify_nonce($nonce, 'prorank_link_tracker')) {
            wp_die('Invalid nonce');
        }
        
        $source_id = intval(prorank_get_post_var('source_id'));
        $target_url = $this->normalize_target_url((string) prorank_get_input_value($_POST, 'target_url', ''));
        
        if (!$source_id || !$target_url) {
            wp_send_json_error('Invalid parameters');
            return;
        }
        $top_links = $this->normalize_top_links($top_links);
        
        // Get target post ID if internal link
        $target_id = url_to_postid($target_url);
        
        // Get user ID and session
        $user_id = get_current_user_id();
        $session_id = session_id() ?: wp_generate_password(32, false);

        $payload = [];
        $formats = [];

        // Support both legacy and new schemas.
        if ($this->has_column('source_id')) {
            $payload['source_id'] = $source_id;
            $formats[] = '%d';
        }
        if ($this->has_column('source_post_id')) {
            $payload['source_post_id'] = $source_id;
            $formats[] = '%d';
        }

        if (empty($payload)) {
            wp_send_json_error('Tracking table schema is missing source column');
            return;
        }

        if ($target_id) {
            if ($this->has_column('target_id')) {
                $payload['target_id'] = $target_id;
                $formats[] = '%d';
            }
            if ($this->has_column('target_post_id')) {
                $payload['target_post_id'] = $target_id;
                $formats[] = '%d';
            }
        }

        if ($this->has_column('target_url')) {
            $payload['target_url'] = $target_url;
            $formats[] = '%s';
        }
        if ($this->has_column('user_id') && $user_id > 0) {
            $payload['user_id'] = $user_id;
            $formats[] = '%d';
        }
        if ($this->has_column('session_id')) {
            $payload['session_id'] = $session_id;
            $formats[] = '%s';
        }
        if ($this->has_column('click_date')) {
            $payload['click_date'] = current_time('mysql');
            $formats[] = '%s';
        }
        if ($this->has_column('clicked_at')) {
            $payload['clicked_at'] = current_time('mysql');
            $formats[] = '%s';
        }

        // Insert click record
        global $wpdb;
        $result = $wpdb->insert($this->table_name, $payload, $formats);
        
        if ($result === false) {
            wp_send_json_error('Failed to track click');
            return;
        }
        
        // Update daily stats
        $this->update_daily_stats();
        
        wp_send_json_success('Click tracked');
    }
    
    /**
     * Update daily stats
     */
    private function update_daily_stats(): void {
        global $wpdb;
        $date_column = $this->get_date_column();
        if ($date_column === null) {
            return;
        }

        if ($date_column === 'clicked_at') {
            $today_clicks = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM %i WHERE DATE(clicked_at) = CURDATE()",
                    $this->table_name
                )
            );
            $yesterday_clicks = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM %i WHERE DATE(clicked_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)",
                    $this->table_name
                )
            );
        } else {
            $today_clicks = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM %i WHERE DATE(click_date) = CURDATE()",
                    $this->table_name
                )
            );
            $yesterday_clicks = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM %i WHERE DATE(click_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)",
                    $this->table_name
                )
            );
        }
        
        // Calculate change percentage
        $change = 0;
        if ($yesterday_clicks > 0) {
            $change = (($today_clicks - $yesterday_clicks) / $yesterday_clicks) * 100;
        }
        
        // Update options
        update_option('prorank_link_clicks', (int) $today_clicks);
        update_option('prorank_link_clicks_change', round($change));
    }

    /**
     * Resolve whether click tracking is enabled.
     *
     * Supports legacy and current option keys. Any explicit false disables tracking.
     *
     * @return bool
     */
    private function is_tracking_enabled(): bool {
        if ($this->tracking_enabled !== null) {
            return $this->tracking_enabled;
        }

        $this->maybe_normalize_legacy_tracking_defaults();

        $explicit_values = [];

        // Top-level options used by REST/UI flows.
        foreach (['prorank_seo_enable_click_tracking', 'prorank_link_tracking_enabled'] as $option_key) {
            $raw = get_option($option_key, null);
            if ($raw !== null) {
                $explicit_values[] = $this->normalize_bool($raw);
            }
        }

        // Internal-linking settings stores (canonical + legacy aliases).
        foreach ([
            'prorank_internal_linking_settings',
            'prorank_seo_internal_linking',
            'prorank_seo_internal_linking_settings',
        ] as $settings_key) {
            $settings = get_option($settings_key, null);
            if (is_array($settings) && array_key_exists('enable_click_tracking', $settings)) {
                $explicit_values[] = $this->normalize_bool($settings['enable_click_tracking']);
            }
        }

        if (empty($explicit_values)) {
            // Click tracking is an optional analytics feature and should remain
            // off until a site explicitly enables it in settings.
            $this->tracking_enabled = false;
            return false;
        }

        // Any explicit false wins to avoid accidental data collection/perf overhead.
        foreach ($explicit_values as $value) {
            if ($value === false) {
                $this->tracking_enabled = false;
                return false;
            }
        }

        $this->tracking_enabled = true;
        return true;
    }

    /**
     * Legacy builds seeded top-level click-tracking flags as enabled before the
     * grouped internal-linking settings were saved. When no canonical grouped
     * setting exists yet, normalize those legacy mirror flags back to opt-in.
     *
     * @return void
     */
    private function maybe_normalize_legacy_tracking_defaults(): void {
        static $normalized = false;

        if ($normalized) {
            return;
        }
        $normalized = true;

        $grouped_settings_keys = [
            'prorank_internal_linking_settings',
            'prorank_seo_internal_linking',
            'prorank_seo_internal_linking_settings',
        ];

        foreach ($grouped_settings_keys as $settings_key) {
            $settings = get_option($settings_key, null);
            if (is_array($settings) && array_key_exists('enable_click_tracking', $settings)) {
                return;
            }
        }

        $top_level_values = [];
        foreach (['prorank_seo_enable_click_tracking', 'prorank_link_tracking_enabled'] as $option_key) {
            $raw = get_option($option_key, null);
            if ($raw !== null) {
                $top_level_values[$option_key] = $raw;
            }
        }

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

        foreach ($top_level_values as $raw) {
            if ($this->normalize_bool($raw) === false) {
                return;
            }
        }

        foreach (array_keys($top_level_values) as $option_key) {
            update_option($option_key, 0);
        }
    }

    /**
     * Normalize loose WordPress option values into bool.
     *
     * @param mixed $value Option value.
     * @return bool
     */
    private function normalize_bool($value): bool {
        if (is_bool($value)) {
            return $value;
        }

        if (is_numeric($value)) {
            return ((int) $value) === 1;
        }

        if (is_string($value)) {
            return in_array(strtolower(trim($value)), ['1', 'true', 'yes', 'on'], true);
        }

        return (bool) $value;
    }

    /**
     * Normalize a tracked URL into a canonical absolute form when possible.
     *
     * @param string $url Raw target URL.
     * @return string
     */
    private function normalize_target_url(string $url): string {
        $url = trim(html_entity_decode(wp_unslash($url)));
        if ($url === '' || str_starts_with($url, '#') || str_starts_with($url, 'javascript:')) {
            return '';
        }

        if (preg_match('~^[a-z0-9.-]+\.[a-z]{2,}([/?#].*)?$~i', $url) === 1) {
            $preferred_scheme = (string) wp_parse_url(home_url('/'), PHP_URL_SCHEME);
            $url = ($preferred_scheme !== '' ? $preferred_scheme : 'https') . '://' . ltrim($url, '/');
        } elseif (str_starts_with($url, '//')) {
            $preferred_scheme = (string) wp_parse_url(home_url('/'), PHP_URL_SCHEME);
            $url = ($preferred_scheme !== '' ? $preferred_scheme : 'https') . ':' . $url;
        } elseif (str_starts_with($url, '/')) {
            $url = home_url($url);
        }

        $parsed = wp_parse_url($url);
        if (!is_array($parsed)) {
            return esc_url_raw($url);
        }

        $home_scheme = (string) wp_parse_url(home_url('/'), PHP_URL_SCHEME);
        $home_host = (string) wp_parse_url(home_url('/'), PHP_URL_HOST);
        $target_host = (string) ($parsed['host'] ?? '');

        if ($home_scheme !== '' && $home_host !== '' && $target_host !== '' && $this->normalize_host($target_host) === $this->normalize_host($home_host)) {
            $canonical = $home_scheme . '://' . $home_host;
            $home_port = wp_parse_url(home_url('/'), PHP_URL_PORT);
            if ($home_port !== null && $home_port !== false && $home_port !== '') {
                $canonical .= ':' . $home_port;
            }

            $path = (string) ($parsed['path'] ?? '/');
            if (preg_match('~^//([^/]+)(/.*)?$~', $path, $matches) === 1 && $this->normalize_host((string) $matches[1]) === $this->normalize_host($home_host)) {
                $path = (string) ($matches[2] ?? '/');
            }

            $canonical .= $path;

            if (!empty($parsed['query'])) {
                $canonical .= '?' . $parsed['query'];
            }

            if (!empty($parsed['fragment'])) {
                $canonical .= '#' . $parsed['fragment'];
            }

            return esc_url_raw($canonical);
        }

        return esc_url_raw($url);
    }

    /**
     * Normalize tracked top-link rows and merge malformed duplicates.
     *
     * @param array<int,array<string,mixed>> $rows Raw top-link rows.
     * @return array<int,array<string,mixed>>
     */
    private function normalize_top_links(array $rows): array {
        $merged = [];

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $normalized_url = $this->normalize_target_url((string) ($row['target_url'] ?? ''));
            $target_id = isset($row['target_id']) && $row['target_id'] !== null ? (int) $row['target_id'] : null;
            $post_title = isset($row['post_title']) && $row['post_title'] !== null ? (string) $row['post_title'] : null;
            $click_count = (int) ($row['click_count'] ?? 0);
            $key = $normalized_url;

            if (!isset($merged[$key])) {
                $merged[$key] = [
                    'target_url' => $normalized_url,
                    'target_id' => $target_id,
                    'click_count' => $click_count,
                    'post_title' => $post_title,
                ];
                continue;
            }

            $merged[$key]['click_count'] += $click_count;
            if (empty($merged[$key]['post_title']) && !empty($post_title)) {
                $merged[$key]['post_title'] = $post_title;
            }
            if (empty($merged[$key]['target_id']) && $target_id !== null) {
                $merged[$key]['target_id'] = $target_id;
            }
        }

        $merged = array_values($merged);
        usort($merged, static function (array $a, array $b): int {
            return (int) ($b['click_count'] ?? 0) <=> (int) ($a['click_count'] ?? 0);
        });

        return array_slice($merged, 0, 10);
    }

    /**
     * Normalize hostnames for same-site comparisons.
     *
     * @param string $host Hostname.
     * @return string
     */
    private function normalize_host(string $host): string {
        $host = strtolower(trim($host));
        if (str_starts_with($host, 'www.')) {
            $host = substr($host, 4);
        }

        return $host;
    }
    
    /**
     * Get click stats for a specific period
     * 
     * @param int $days Number of days to look back
     * @return array Click statistics
     */
    public function get_click_stats(int $days = 30): array {
        global $wpdb;
        $date_column = $this->get_date_column();
        $target_column = $this->get_target_column();

        if ($date_column === null) {
            return [
                'total_clicks' => 0,
                'daily_clicks' => [],
                'top_links' => [],
                'trend' => 0.0,
                'period_days' => $days,
            ];
        }

        $half_days = max(1, (int) floor($days / 2));
        if ($date_column === 'clicked_at') {
            $total_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $days
            ));
            $daily_clicks = $wpdb->get_results($wpdb->prepare(
                "SELECT DATE(clicked_at) as date, COUNT(*) as clicks
                 FROM %i
                 WHERE clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 GROUP BY DATE(clicked_at)
                 ORDER BY date DESC",
                $this->table_name,
                $days
            ), ARRAY_A);
            $first_half = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) 
                 FROM %i
                 WHERE clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 AND clicked_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $days,
                $half_days
            ));
            $second_half = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) 
                 FROM %i
                 WHERE clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $half_days
            ));
        } else {
            $total_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $days
            ));
            $daily_clicks = $wpdb->get_results($wpdb->prepare(
                "SELECT DATE(click_date) as date, COUNT(*) as clicks
                 FROM %i
                 WHERE click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 GROUP BY DATE(click_date)
                 ORDER BY date DESC",
                $this->table_name,
                $days
            ), ARRAY_A);
            $first_half = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) 
                 FROM %i
                 WHERE click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 AND click_date < DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $days,
                $half_days
            ));
            $second_half = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) 
                 FROM %i
                 WHERE click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)",
                $this->table_name,
                $half_days
            ));
        }

        // Most clicked links
        if ($target_column === 'target_post_id') {
            if ($date_column === 'clicked_at') {
                $top_links = $wpdb->get_results($wpdb->prepare(
                    "SELECT 
                        c.target_url,
                        c.target_post_id AS target_id,
                        COUNT(*) as click_count,
                        p.post_title
                     FROM %i c
                     LEFT JOIN %i p ON c.target_post_id = p.ID
                     WHERE c.clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                     GROUP BY c.target_url, c.target_post_id
                     ORDER BY click_count DESC
                     LIMIT 10",
                    $this->table_name,
                    $wpdb->posts,
                    $days
                ), ARRAY_A);
            } else {
                $top_links = $wpdb->get_results($wpdb->prepare(
                    "SELECT 
                        c.target_url,
                        c.target_post_id AS target_id,
                        COUNT(*) as click_count,
                        p.post_title
                     FROM %i c
                     LEFT JOIN %i p ON c.target_post_id = p.ID
                     WHERE c.click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)
                     GROUP BY c.target_url, c.target_post_id
                     ORDER BY click_count DESC
                     LIMIT 10",
                    $this->table_name,
                    $wpdb->posts,
                    $days
                ), ARRAY_A);
            }
        } elseif ($target_column === 'target_id') {
            if ($date_column === 'clicked_at') {
                $top_links = $wpdb->get_results($wpdb->prepare(
                    "SELECT 
                        c.target_url,
                        c.target_id AS target_id,
                        COUNT(*) as click_count,
                        p.post_title
                     FROM %i c
                     LEFT JOIN %i p ON c.target_id = p.ID
                     WHERE c.clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                     GROUP BY c.target_url, c.target_id
                     ORDER BY click_count DESC
                     LIMIT 10",
                    $this->table_name,
                    $wpdb->posts,
                    $days
                ), ARRAY_A);
            } else {
                $top_links = $wpdb->get_results($wpdb->prepare(
                    "SELECT 
                        c.target_url,
                        c.target_id AS target_id,
                        COUNT(*) as click_count,
                        p.post_title
                     FROM %i c
                     LEFT JOIN %i p ON c.target_id = p.ID
                     WHERE c.click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)
                     GROUP BY c.target_url, c.target_id
                     ORDER BY click_count DESC
                     LIMIT 10",
                    $this->table_name,
                    $wpdb->posts,
                    $days
                ), ARRAY_A);
            }
        } elseif ($date_column === 'clicked_at') {
            $top_links = $wpdb->get_results($wpdb->prepare(
                "SELECT 
                    target_url,
                    NULL AS target_id,
                    COUNT(*) as click_count,
                    '' AS post_title
                 FROM %i
                 WHERE clicked_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 GROUP BY target_url
                 ORDER BY click_count DESC
                 LIMIT 10",
                $this->table_name,
                $days
            ), ARRAY_A);
        } else {
            $top_links = $wpdb->get_results($wpdb->prepare(
                "SELECT 
                    target_url,
                    NULL AS target_id,
                    COUNT(*) as click_count,
                    '' AS post_title
                 FROM %i
                 WHERE click_date >= DATE_SUB(NOW(), INTERVAL %d DAY)
                 GROUP BY target_url
                 ORDER BY click_count DESC
                 LIMIT 10",
                $this->table_name,
                $days
            ), ARRAY_A);
        }
        
        $trend = 0;
        if ($first_half > 0) {
            $trend = (($second_half - $first_half) / $first_half) * 100;
        }
        
        return [
            'total_clicks' => (int) $total_clicks,
            'daily_clicks' => $daily_clicks,
            'top_links' => $top_links,
            'trend' => round($trend, 1),
            'period_days' => $days,
        ];
    }
    
    /**
     * Get clicks for a specific post
     * 
     * @param int $post_id Post ID
     * @return array Click data
     */
    public function get_post_clicks(int $post_id): array {
        global $wpdb;
        $source_column = $this->get_source_column();
        $target_column = $this->get_target_column();

        if ($source_column === null || $target_column === null) {
            return [
                'outbound' => 0,
                'inbound' => 0,
                'total' => 0,
            ];
        }
        if ($source_column === 'source_post_id') {
            $outbound_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE source_post_id = %d",
                $this->table_name,
                $post_id
            ));
        } else {
            $outbound_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE source_id = %d",
                $this->table_name,
                $post_id
            ));
        }

        if ($target_column === 'target_post_id') {
            $inbound_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE target_post_id = %d",
                $this->table_name,
                $post_id
            ));
        } else {
            $inbound_clicks = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE target_id = %d",
                $this->table_name,
                $post_id
            ));
        }
        
        return [
            'outbound' => (int) $outbound_clicks,
            'inbound' => (int) $inbound_clicks,
            'total' => (int) ($outbound_clicks + $inbound_clicks),
        ];
    }

    /**
     * Get table columns map.
     *
     * @return array<string,bool>
     */
    private function get_table_columns(): array {
        if ($this->table_columns !== null) {
            return $this->table_columns;
        }

        global $wpdb;
        $columns = [];
        $results = $wpdb->get_results("SHOW COLUMNS FROM {$this->table_name}");
        if (is_array($results)) {
            foreach ($results as $row) {
                if (isset($row->Field) && is_string($row->Field) && $row->Field !== '') {
                    $columns[$row->Field] = true;
                }
            }
        }

        $this->table_columns = $columns;
        return $this->table_columns;
    }

    /**
     * Check if a column exists in click table.
     */
    private function has_column(string $column): bool {
        $columns = $this->get_table_columns();
        return !empty($columns[$column]);
    }

    /**
     * Resolve source column name for current schema.
     */
    private function get_source_column(): ?string {
        if ($this->has_column('source_post_id')) {
            return 'source_post_id';
        }
        if ($this->has_column('source_id')) {
            return 'source_id';
        }
        return null;
    }

    /**
     * Resolve target column name for current schema.
     */
    private function get_target_column(): ?string {
        if ($this->has_column('target_post_id')) {
            return 'target_post_id';
        }
        if ($this->has_column('target_id')) {
            return 'target_id';
        }
        return null;
    }

    /**
     * Resolve date column name for current schema.
     */
    private function get_date_column(): ?string {
        if ($this->has_column('clicked_at')) {
            return 'clicked_at';
        }
        if ($this->has_column('click_date')) {
            return 'click_date';
        }
        return null;
    }
}

// Initialize the tracker
add_action('init', [LinkClickTracker::class, 'init']);
