<?php
/**
 * Database Class - CLEAN REWRITE
 *
 * Gère toutes les opérations de base de données
 *
 * @package   almaweb_ai_visitor_analytics
 * @since     1.2.0
 * @author    Yohan Ziri (AlmaWeb)
 * @link      https://almaweb.fr
 */

if (!defined('ABSPATH')) {
    exit;
}

class Almaweb_Database {

    private $table_name;

    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'ai_monitor_logs';
    }

    public function log_visit($data) {
        global $wpdb;

        // Assurer la robustesse en fournissant des valeurs par défaut pour toutes les clés
        $insert_data = [
            'user_agent'           => $data['user_agent'] ?? 'N/A',
            'ip_address'           => $data['ip_address'] ?? '0.0.0.0',
            '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'          => $data['request_url'] ?? '',
            'request_method'       => $data['request_method'] ?? 'GET',
            'referer'              => $data['referer'] ?? null,
            'page_visited'         => $data['page_visited'] ?? null,
            'http_accept'          => $data['http_accept'] ?? 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,
            'session_id'           => $data['session_id'] ?? null,
            'session_duration'     => $data['session_duration'] ?? 0,
            'pages_in_session'     => $data['pages_in_session'] ?? 1,
            'is_bounce'            => isset($data['is_bounce']) ? ($data['is_bounce'] ? 1 : 0) : 1,
            'is_return_visitor'    => !empty($data['is_return_visitor']) ? 1 : 0,
            'has_conversion'       => !empty($data['has_conversion']) ? 1 : 0,
            'conversion_type'      => $data['conversion_type'] ?? null,
            'conversion_value'     => $data['conversion_value'] ?? null,
        ];

        $wpdb->insert($this->table_name, $insert_data);

        // Clear relevant caches when new data is inserted
        $this->clear_cache();
    }

    /**
     * Clear all plugin caches
     */
    private function clear_cache() {
        delete_transient('almaweb_distinct_families');
        // Clear all visits_by_family caches (different days)
        for ($days = 1; $days <= 90; $days++) {
            delete_transient('almaweb_visits_by_family_' . $days);
        }
        // Clear referrer caches
        delete_transient('almaweb_top_referrers');
        delete_transient('almaweb_referrer_timeline');
        for ($days = 1; $days <= 90; $days++) {
            delete_transient('almaweb_referrer_insights_' . $days);
        }
    }

    public function get_recent_logs($limit = 100, $offset = 0, $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_results(
                $wpdb->prepare(
                    "SELECT * FROM {$this->table_name} WHERE bot_intention = %s AND bot_family = %s ORDER BY visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_intention'],
                    $filters['bot_family'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } elseif (!empty($filters['bot_intention'])) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$this->table_name} WHERE bot_intention = %s ORDER BY visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_intention'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } elseif (!empty($filters['bot_family'])) {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$this->table_name} WHERE bot_family = %s ORDER BY visited_at DESC LIMIT %d OFFSET %d",
                    $filters['bot_family'],
                    $limit,
                    $offset
                ),
                ARRAY_A
            );
        } else {
            return $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$this->table_name} ORDER BY 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}");
        }
    }

    public function get_visits_by_family($days = 30) {
        // Try to get cached result first
        $cache_key = 'almaweb_visits_by_family_' . $days;
        $cached = get_transient($cache_key);

        if (false !== $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);

        // Cache for 5 minutes
        set_transient($cache_key, $results, 5 * MINUTE_IN_SECONDS);

        return $results;
    }
    
    public function get_distinct_families() {
        // Try to get cached result first
        $cache_key = 'almaweb_distinct_families';
        $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;
    }

    public function cleanup_old_logs($retention_days) {
        global $wpdb;
        $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
        return $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->table_name} WHERE visited_at < %s",
            $date_limit
        ));
    }

    /**
     * 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) {
        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        return $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);
    }

    /**
     * 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) {
        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        return $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);
    }

    /**
     * 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) {
        global $wpdb;
        $date_limit = gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safe
        return $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);
    }

    /**
     * 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
        ));
    }

    /**
     * Get AI referrer session metrics for conversion probability
     *
     * @param int $days Number of days to look back
     * @return array Session metrics
     */
    public function get_ai_referrer_metrics($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
        $metrics = $wpdb->get_row($wpdb->prepare(
            "SELECT
                AVG(session_duration) as avg_duration,
                AVG(pages_in_session) as avg_pages,
                (SUM(CASE WHEN is_bounce = 0 THEN 1 ELSE 0 END) / COUNT(*)) * 100 as engagement_rate,
                (SUM(CASE WHEN is_return_visitor = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100 as return_rate,
                (SUM(CASE WHEN has_conversion = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100 as conversion_rate
             FROM {$this->table_name}
             WHERE is_from_ai = 1 AND visited_at >= %s",
            $date_limit
        ), ARRAY_A);

        // Ensure all values are numeric
        if ($metrics) {
            foreach ($metrics as $key => $value) {
                $metrics[$key] = floatval($value);
            }
        }

        return $metrics;
    }
}