<?php
/**
 * Database Class
 *
 * Gère toutes les opérations de base de données.
 *
 * Depuis 1.3.2 le stockage est en trois tables :
 *  - ai_monitor_logs        : détail des visites de bots IA et des humains venant d'une IA uniquement
 *  - ai_monitor_daily       : compteurs journaliers (total / IA / humains / autres bots) pour les ratios
 *  - ai_monitor_user_agents : table de référence des user-agents (dédoublonnage)
 *
 * @package   almaweb_ai_visitor_analytics
 * @since     1.2.0
 * @author    Yohan Ziri (AlmaWeb)
 * @link      https://almaweb.fr
 */

if (!defined('ABSPATH')) {
    exit;
}

class Almaweb_Database {

    /**
     * Étapes du schéma (option autoload 'almaweb_schema_stage') :
     *  0 = schéma 1.3.1 (une seule table, toutes les visites stockées)
     *  1 = transition : nouvelles tables créées, colonne ua_id ajoutée, anciennes colonnes encore présentes
     *  2 = schéma 1.3.2 final
     */
    const STAGE_LEGACY     = 0;
    const STAGE_TRANSITION = 1;
    const STAGE_FINAL      = 2;

    private $table_name;
    private $daily_table;
    private $ua_table;

    public function __construct() {
        global $wpdb;
        $this->table_name  = $wpdb->prefix . 'ai_monitor_logs';
        $this->daily_table = $wpdb->prefix . 'ai_monitor_daily';
        $this->ua_table    = $wpdb->prefix . 'ai_monitor_user_agents';
    }

    public static function logs_table() {
        global $wpdb;
        return $wpdb->prefix . 'ai_monitor_logs';
    }

    public static function daily_table() {
        global $wpdb;
        return $wpdb->prefix . 'ai_monitor_daily';
    }

    public static function ua_table() {
        global $wpdb;
        return $wpdb->prefix . 'ai_monitor_user_agents';
    }

    public static function schema_stage() {
        return (int) get_option('almaweb_schema_stage', self::STAGE_LEGACY);
    }

    /* ------------------------------------------------------------------
     * ÉCRITURE
     * ---------------------------------------------------------------- */

    /**
     * Enregistre une visite.
     *
     * - Toutes les visites incrémentent le compteur du jour (ratios exacts).
     * - Seules les visites de bots IA et les humains venant d'une IA sont stockées en détail.
     *
     * @param array $data Données du visiteur (Almaweb_Bot_Detector::analyze_request + enrichissements)
     */
    public function log_visit($data) {
        $stage = self::schema_stage();

        if ($stage === self::STAGE_LEGACY) {
            $this->log_visit_legacy($data);
            return;
        }

        $is_ai   = !empty($data['is_ai_bot']);
        $from_ai = !empty($data['is_from_ai']);
        $type    = isset($data['bot_type']) ? (string) $data['bot_type'] : 'human';
        $is_bot  = !$is_ai && $type !== 'human' && $type !== '';

        $this->increment_daily_counter($is_ai, $is_bot);

        if (!$is_ai && !$from_ai) {
            return; // visite humaine ou bot non identifié : comptée, pas détaillée
        }

        $this->insert_log_row($data, $stage);
    }

    /**
     * Incrémente les compteurs du jour (une seule requête, pas de SELECT préalable).
     */
    private function increment_daily_counter($is_ai, $is_bot) {
        global $wpdb;
        $ai    = $is_ai ? 1 : 0;
        $bot   = (!$is_ai && $is_bot) ? 1 : 0;
        $human = (!$is_ai && !$is_bot) ? 1 : 0;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- table name from class property, counters are write-only
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$this->daily_table} (visit_date, total_visits, ai_visits, human_visits, bot_visits)
             VALUES (%s, 1, %d, %d, %d)
             ON DUPLICATE KEY UPDATE
                total_visits = total_visits + 1,
                ai_visits    = ai_visits + VALUES(ai_visits),
                human_visits = human_visits + VALUES(human_visits),
                bot_visits   = bot_visits + VALUES(bot_visits)",
            current_time('Y-m-d'),
            $ai,
            $human,
            $bot
        ));
    }

    /**
     * Renvoie l'identifiant d'un user-agent dans la table de référence (créé si nécessaire).
     * Une seule requête grâce à ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id).
     */
    public function get_user_agent_id($user_agent) {
        global $wpdb;
        $user_agent = substr((string) $user_agent, 0, 500);
        if ($user_agent === '') {
            return null;
        }
        $hash = md5($user_agent);

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- table name from class property
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$this->ua_table} (ua_hash, user_agent) VALUES (%s, %s)
             ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)",
            $hash,
            $user_agent
        ));

        $id = (int) $wpdb->insert_id;
        if ($id > 0) {
            return $id;
        }

        // Repli (cas improbable : insert_id non renseigné par le pilote)
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- table name from class property
        $id = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$this->ua_table} WHERE ua_hash = %s", $hash));
        return $id > 0 ? $id : null;
    }

    private function insert_log_row($data, $stage) {
        global $wpdb;

        $insert_data = [
            'ua_id'                => $this->get_user_agent_id($data['user_agent'] ?? ''),
            'ip_address'           => substr($data['ip_address'] ?? '0.0.0.0', 0, 45),
            'bot_name'             => isset($data['bot_name']) ? substr((string) $data['bot_name'], 0, 100) : null,
            'bot_type'             => isset($data['bot_type']) ? substr((string) $data['bot_type'], 0, 50) : null,
            'bot_family'           => isset($data['bot_family']) ? substr((string) $data['bot_family'], 0, 50) : null,
            'bot_intention'        => isset($data['bot_intention']) ? substr((string) $data['bot_intention'], 0, 50) : null,
            'is_ai_bot'            => !empty($data['is_ai_bot']) ? 1 : 0,
            'is_stealth'           => !empty($data['is_stealth']) ? 1 : 0,
            'detection_method'     => substr($data['detection_method'] ?? 'user_agent', 0, 20),
            'request_url'          => substr($data['request_url'] ?? '', 0, 1200),
            'referer'              => isset($data['referer']) ? substr($data['referer'], 0, 500) : null,
            'action_type'          => isset($data['action_type']) ? substr((string) $data['action_type'], 0, 50) : 'visit',
            'page_visited'         => isset($data['page_visited']) ? substr($data['page_visited'], 0, 500) : null,
            'http_accept_language' => isset($data['http_accept_language']) ? substr($data['http_accept_language'], 0, 100) : null,
            'visited_at'           => current_time('mysql'),
            'ai_referrer_source'   => isset($data['ai_referrer_source']) ? substr((string) $data['ai_referrer_source'], 0, 200) : null,
            'ai_referrer_type'     => isset($data['ai_referrer_type']) ? substr((string) $data['ai_referrer_type'], 0, 50) : null,
            'ai_referrer_family'   => isset($data['ai_referrer_family']) ? substr((string) $data['ai_referrer_family'], 0, 100) : null,
            'is_from_ai'           => !empty($data['is_from_ai']) ? 1 : 0,
            'is_blocked'           => !empty($data['is_blocked']) ? 1 : 0,
        ];

        // En transition, l'ancienne colonne user_agent (NOT NULL) existe encore : on la renseigne à vide.
        if ($stage === self::STAGE_TRANSITION) {
            $insert_data['user_agent'] = '';
        }

        $wpdb->insert($this->table_name, $insert_data);
    }

    /**
     * Insertion au format 1.3.1 (tant que la migration 1.3.2 n'a pas démarré).
     */
    private function log_visit_legacy($data) {
        global $wpdb;

        $insert_data = [
            'user_agent'           => substr($data['user_agent'] ?? 'N/A', 0, 300),
            'ip_address'           => substr($data['ip_address'] ?? '0.0.0.0', 0, 45),
            'bot_name'             => $data['bot_name'] ?? null,
            'bot_type'             => $data['bot_type'] ?? null,
            'bot_family'           => $data['bot_family'] ?? null,
            'bot_intention'        => $data['bot_intention'] ?? null,
            'is_ai_bot'            => !empty($data['is_ai_bot']) ? 1 : 0,
            'is_stealth'           => !empty($data['is_stealth']) ? 1 : 0,
            'detection_method'     => $data['detection_method'] ?? 'user_agent',
            'request_url'          => substr($data['request_url'] ?? '', 0, 1200),
            'referer'              => isset($data['referer']) ? substr($data['referer'], 0, 500) : null,
            'page_visited'         => isset($data['page_visited']) ? substr($data['page_visited'], 0, 500) : null,
            'http_accept'          => isset($data['http_accept']) ? substr($data['http_accept'], 0, 200) : null,
            'http_accept_language' => $data['http_accept_language'] ?? null,
            'visited_at'           => current_time('mysql'),
            'ai_referrer_source'   => $data['ai_referrer_source'] ?? null,
            'ai_referrer_type'     => $data['ai_referrer_type'] ?? null,
            'ai_referrer_family'   => $data['ai_referrer_family'] ?? null,
            'is_from_ai'           => !empty($data['is_from_ai']) ? 1 : 0,
            'is_blocked'           => !empty($data['is_blocked']) ? 1 : 0,
        ];

        $wpdb->insert($this->table_name, $insert_data);
    }

    /**
     * Return the current cache version suffix for versioned cache keys.
     */
    public static function get_cache_version() {
        return (int) get_option('almaweb_cache_version', 0);
    }

    /* ------------------------------------------------------------------
     * COMPTEURS JOURNALIERS
     * ---------------------------------------------------------------- */

    /**
     * Totaux (toutes visites / visites IA) sur les N derniers jours, depuis les compteurs.
     *
     * @return array{total_visits:int, ai_visits:int}
     */
    public function get_daily_totals($days = 30) {
        $end   = current_time('Y-m-d');
        $start = gmdate('Y-m-d', current_time('timestamp') - ((int) $days * DAY_IN_SECONDS));
        return $this->get_daily_totals_between($start, $end);
    }

    /**
     * Totaux entre deux dates locales (incluses).
     *
     * @return array{total_visits:int, ai_visits:int}
     */
    public function get_daily_totals_between($start_date, $end_date) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from class property
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT COALESCE(SUM(total_visits), 0) AS total_visits, COALESCE(SUM(ai_visits), 0) AS ai_visits
             FROM {$this->daily_table}
             WHERE visit_date BETWEEN %s AND %s",
            $start_date,
            $end_date
        ), ARRAY_A);

        return [
            'total_visits' => isset($row['total_visits']) ? (int) $row['total_visits'] : 0,
            'ai_visits'    => isset($row['ai_visits']) ? (int) $row['ai_visits'] : 0,
        ];
    }

    /* ------------------------------------------------------------------
     * LECTURE DES LOGS
     * ---------------------------------------------------------------- */

    /**
     * Colonnes à sélectionner pour la vue Logs (user_agent via jointure quand le schéma est migré).
     */
    private function logs_select_sql() {
        if (self::schema_stage() === self::STAGE_FINAL) {
            return "SELECT l.*, ua.user_agent FROM {$this->table_name} l LEFT JOIN {$this->ua_table} ua ON ua.id = l.ua_id";
        }
        return "SELECT l.* FROM {$this->table_name} l";
    }

    public function get_recent_logs($limit = 100, $offset = 0, $filters = []) {
        global $wpdb;
        $select = $this->logs_select_sql();

        // Build query based on filters to avoid dynamic WHERE clause construction
        if (!empty($filters['bot_intention']) && !empty($filters['bot_family'])) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- static SELECT built from class properties
            return $wpdb->get_results(
                $wpdb->prepare(
                    "{$select} WHERE l.bot_intention = %s AND l.bot_family = %s ORDER BY l.visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_intention'],
                    $filters['bot_family'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } elseif (!empty($filters['bot_intention'])) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- static SELECT built from class properties
            return $wpdb->get_results(
                $wpdb->prepare(
                    "{$select} WHERE l.bot_intention = %s ORDER BY l.visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_intention'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } elseif (!empty($filters['bot_family'])) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- static SELECT built from class properties
            return $wpdb->get_results(
                $wpdb->prepare(
                    "{$select} WHERE l.bot_family = %s ORDER BY l.visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_family'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- static SELECT built from class properties
            return $wpdb->get_results(
                $wpdb->prepare(
                    "{$select} ORDER BY l.visited_at DESC LIMIT %d OFFSET %d",
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        }
    }

    public function get_total_logs($filters = []) {
        global $wpdb;

        // Build query based on filters to avoid dynamic WHERE clause construction
        if (!empty($filters['bot_intention']) && !empty($filters['bot_family'])) {
            return $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE bot_intention = %s AND bot_family = %s",
                    $filters['bot_intention'],
                    $filters['bot_family']
                )
            );
        } elseif (!empty($filters['bot_intention'])) {
            return $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE bot_intention = %s",
                    $filters['bot_intention']
                )
            );
        } elseif (!empty($filters['bot_family'])) {
            return $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE bot_family = %s",
                    $filters['bot_family']
                )
            );
        } else {
            return $wpdb->get_var("SELECT COUNT(*) FROM {$this->table_name}");
        }
    }

    /**
     * @param int  $days      Period in days
     * @param bool $use_cache Set to false to bypass the transient cache (e.g. scheduled email report)
     */
    public function get_visits_by_family($days = 30, $use_cache = true) {
        $cache_key = 'almaweb_visits_by_family_' . $days . '_v' . self::get_cache_version();

        if ($use_cache) {
            $cached = get_transient($cache_key);
            if (false !== $cached && is_array($cached)) {
                return $cached;
            }
        }

        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
        // Static SQL CASE statement - safe from injection
        $family_case_sql = "CASE WHEN bot_family IS NULL OR bot_family = '' THEN 'Inconnue' ELSE bot_family END";

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $family_case_sql is a safe static CASE statement, table name from class property
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT {$family_case_sql} as bot_family, COUNT(*) as visit_count
            FROM {$this->table_name}
            WHERE is_ai_bot = 1 AND bot_type = 'ai_bot' AND visited_at >= %s
            GROUP BY bot_family ORDER BY visit_count DESC",
            $date_limit
        ), ARRAY_A);

        $results = is_array($results) ? $results : [];

        // Cache for 5 minutes
        set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);

        return $results;
    }

    public function get_distinct_families() {
        $cache_key = 'almaweb_distinct_families_v' . self::get_cache_version();
        $cached = get_transient($cache_key);

        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $results = $wpdb->get_col("SELECT DISTINCT bot_family FROM {$this->table_name} WHERE bot_family IS NOT NULL AND bot_family != '' ORDER BY bot_family ASC");

        // Cache for 1 hour
        set_transient($cache_key, $results, HOUR_IN_SECONDS);

        return $results;
    }

    /* ------------------------------------------------------------------
     * MAINTENANCE
     * ---------------------------------------------------------------- */

    /**
     * Supprime les logs, les compteurs et les user-agents orphelins au-delà de la rétention.
     *
     * @return int Nombre de lignes de log supprimées
     */
    public function cleanup_old_logs($retention_days) {
        global $wpdb;
        $retention_days = max(1, (int) $retention_days);
        $date_limit     = gmdate('Y-m-d H:i:s', strtotime("-{$retention_days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name safe, cleanup operation
        $deleted = (int) $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->table_name} WHERE visited_at < %s",
            $date_limit
        ));

        if (self::schema_stage() >= self::STAGE_TRANSITION) {
            $date_only = gmdate('Y-m-d', current_time('timestamp') - ($retention_days * DAY_IN_SECONDS));
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name safe, cleanup operation
            $wpdb->query($wpdb->prepare("DELETE FROM {$this->daily_table} WHERE visit_date < %s", $date_only));
            $this->delete_orphan_user_agents();
        }

        return $deleted;
    }

    /**
     * Supprime les user-agents qui ne sont plus référencés par aucun log.
     */
    public function delete_orphan_user_agents() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table names from class properties
        $wpdb->query(
            "DELETE ua FROM {$this->ua_table} ua
             LEFT JOIN {$this->table_name} l ON l.ua_id = ua.id
             WHERE l.id IS NULL"
        );
    }

    /**
     * Vide toutes les données (logs, compteurs, user-agents).
     */
    public function reset_all_data() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Table names from class properties, explicit user action
        $wpdb->query("TRUNCATE TABLE {$this->table_name}");
        if (self::schema_stage() >= self::STAGE_TRANSITION) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Table names from class properties, explicit user action
            $wpdb->query("TRUNCATE TABLE {$this->daily_table}");
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Table names from class properties, explicit user action
            $wpdb->query("TRUNCATE TABLE {$this->ua_table}");
        }
    }

    /**
     * Statistiques de stockage affichées dans les informations système.
     *
     * @return array{logs:int, days:int, user_agents:int, size_mb:float}
     */
    public function get_storage_stats() {
        global $wpdb;
        $stats = ['logs' => (int) $this->get_total_logs(), 'days' => 0, 'user_agents' => 0, 'size_mb' => 0.0];

        if (self::schema_stage() >= self::STAGE_TRANSITION) {
            $stats['days']        = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->daily_table}");
            $stats['user_agents'] = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->ua_table}");
        }

        // MySQL 8 met en cache les statistiques d'information_schema pendant 24 h : on demande des valeurs fraîches.
        // (variable inconnue sur MariaDB / MySQL 5.7 : l'erreur est ignorée)
        $suppress = $wpdb->suppress_errors(true);
        $wpdb->query("SET SESSION information_schema_stats_expiry = 0");
        $wpdb->suppress_errors($suppress);

        $size = $wpdb->get_var($wpdb->prepare(
            "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1)
             FROM information_schema.TABLES
             WHERE table_schema = %s AND table_name IN (%s, %s, %s)",
            DB_NAME,
            $this->table_name,
            $this->daily_table,
            $this->ua_table
        ));
        $stats['size_mb'] = $size !== null ? (float) $size : 0.0;

        return $stats;
    }

    /* ------------------------------------------------------------------
     * RÉFÉRENTS IA
     * ---------------------------------------------------------------- */

    /**
     * Get top AI referrer sources
     *
     * @param int $days Number of days to look back
     * @param int $limit Maximum number of results
     * @return array Top referrer sources
     */
    public function get_top_referrer_sources($days = 30, $limit = 10) {
        $cache_key = 'almaweb_top_referrers_' . $days . '_' . $limit . '_v' . self::get_cache_version();
        $cached = get_transient($cache_key);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT ai_referrer_source,
                    ai_referrer_family,
                    COUNT(*) as visit_count,
                    COUNT(DISTINCT ip_address) as unique_visitors,
                    COUNT(DISTINCT page_visited) as pages_reached
             FROM {$this->table_name}
             WHERE is_from_ai = 1
                   AND ai_referrer_source IS NOT NULL
                   AND visited_at >= %s
             GROUP BY ai_referrer_source, ai_referrer_family
             ORDER BY visit_count DESC
             LIMIT %d",
            $date_limit,
            $limit
        ), ARRAY_A);

        set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);
        return $results;
    }

    /**
     * Get referrer timeline data
     *
     * @param int $days Number of days to look back
     * @return array Timeline data by date and family
     */
    public function get_referrer_timeline($days = 30) {
        $cache_key = 'almaweb_referrer_timeline_' . $days . '_v' . self::get_cache_version();
        $cached = get_transient($cache_key);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(visited_at) as visit_date,
                    ai_referrer_family,
                    COUNT(*) as visit_count
             FROM {$this->table_name}
             WHERE is_from_ai = 1
                   AND visited_at >= %s
             GROUP BY visit_date, ai_referrer_family
             ORDER BY visit_date ASC",
            $date_limit
        ), ARRAY_A);

        set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);
        return $results;
    }

    /**
     * Get landing pages from AI referrers
     *
     * @param int $days Number of days to look back
     * @param int $limit Maximum number of results
     * @return array Landing pages data
     */
    public function get_ai_landing_pages($days = 30, $limit = 10) {
        $cache_key = 'almaweb_landing_pages_' . $days . '_' . $limit . '_v' . self::get_cache_version();
        $cached = get_transient($cache_key);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT page_visited,
                    COUNT(*) as visit_count,
                    COUNT(DISTINCT ai_referrer_source) as referrer_sources
             FROM {$this->table_name}
             WHERE is_from_ai = 1
                   AND page_visited IS NOT NULL
                   AND page_visited != ''
                   AND visited_at >= %s
             GROUP BY page_visited
             ORDER BY visit_count DESC
             LIMIT %d",
            $date_limit,
            $limit
        ), ARRAY_A);

        set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);
        return $results;
    }

    /**
     * Get total AI referrer visits
     *
     * @param int $days Number of days to look back
     * @return int Total visits from AI referrers
     */
    public function get_ai_referrer_total($days = 30) {
        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*)
             FROM {$this->table_name}
             WHERE is_from_ai = 1 AND visited_at >= %s",
            $date_limit
        ));
    }

    /**
     * Get distinct AI referrer families count
     *
     * @param int $days Number of days to look back
     * @return int Number of distinct AI referrer families
     */
    public function get_referrer_diversity($days = 30) {
        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT ai_referrer_source)
             FROM {$this->table_name}
             WHERE is_from_ai = 1
                   AND ai_referrer_source IS NOT NULL
                   AND visited_at >= %s",
            $date_limit
        ));
    }
}