<?php
namespace Avision;

class Avision_Database {
    private $pdo;

    /** @var \wpdb|null Référence wpdb pour préfixe dynamique (ex. wor1515_). */
    private $wpdb = null;

    /** Préfixe des tables WordPress (ex. wor1515_). Vide si hors WordPress. */
    private $table_prefix = '';

    public function init() {
        $this->wpdb = ( isset( $GLOBALS['wpdb'] ) && $GLOBALS['wpdb'] instanceof \wpdb ) ? $GLOBALS['wpdb'] : null;
        $this->table_prefix = ( $this->wpdb && $this->wpdb->prefix !== '' ) ? (string) $this->wpdb->prefix : '';
        $this->connect();
        $this->create_tables();
        // Secours MySQL : colonne user_type (préfixe wpdb ex. wor1515_ inclus via table()).
        if ( $this->wpdb instanceof \wpdb ) {
            $tbl = $this->table( 'avi_saas_profiles' );
            $q   = 'ALTER TABLE `' . str_replace( '`', '``', $tbl ) . '` ADD COLUMN IF NOT EXISTS user_type VARCHAR(20) DEFAULT \'b2c\'';
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DDL, identifiant échappé.
            $this->wpdb->query( $q );
        }
    }

    /** Retourne le nom de table préfixé (ex. wor1515_avi_saas_profiles). */
    private function table( $name ) {
        if ( $this->wpdb instanceof \wpdb && $this->wpdb->prefix !== '' ) {
            return $this->wpdb->prefix . $name;
        }
        if ( $this->table_prefix !== '' ) {
            return $this->table_prefix . $name;
        }
        if ( isset( $GLOBALS['wpdb'] ) && $GLOBALS['wpdb'] instanceof \wpdb && $GLOBALS['wpdb']->prefix !== '' ) {
            return $GLOBALS['wpdb']->prefix . $name;
        }

        return $name;
    }

    private function connect() {
        $database_url = getenv('DATABASE_URL');
        
        if ($database_url) {
            $parsed = parse_url($database_url);
            $host = $parsed['host'] ?? 'localhost';
            $port = $parsed['port'] ?? 5432;
            $dbname = ltrim($parsed['path'] ?? '', '/');
            $user = $parsed['user'] ?? '';
            $password = $parsed['pass'] ?? '';
            
            try {
                $dsn = "pgsql:host={$host};port={$port};dbname={$dbname}";
                $this->pdo = new \PDO($dsn, $user, $password, [
                    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
                ]);
            } catch (\PDOException $e) {
                error_log('Database connection failed: ' . $e->getMessage());
            }
        }
    }

    private function create_tables() {
        if (!$this->pdo) return;

        $queries = [
            "CREATE TABLE IF NOT EXISTS avision_audits (
                id SERIAL PRIMARY KEY,
                business_name VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL,
                city VARCHAR(255) NOT NULL,
                category VARCHAR(255) NOT NULL,
                language VARCHAR(10) DEFAULT 'en',
                audit_link VARCHAR(500),
                score INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_user_progress (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                level_1_completed BOOLEAN DEFAULT FALSE,
                level_1_completed_at TIMESTAMP,
                level_2_unlocked_at TIMESTAMP,
                level_2_completed BOOLEAN DEFAULT FALSE,
                level_2_completed_at TIMESTAMP,
                level_3_purchased BOOLEAN DEFAULT FALSE,
                level_3_purchased_at TIMESTAMP,
                level_4_completed BOOLEAN DEFAULT FALSE,
                automation_subscribed BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_payments (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                stripe_payment_id VARCHAR(255),
                stripe_subscription_id VARCHAR(255),
                payment_type VARCHAR(50) NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                currency VARCHAR(10) NOT NULL,
                status VARCHAR(50) DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_chat_history (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                message TEXT NOT NULL,
                sender VARCHAR(20) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_guardian_settings (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id) UNIQUE,
                is_active BOOLEAN DEFAULT FALSE,
                auto_respond BOOLEAN DEFAULT TRUE,
                response_delay INTEGER DEFAULT 60,
                tone_of_voice VARCHAR(50) DEFAULT 'professional',
                include_seo BOOLEAN DEFAULT TRUE,
                notification_email BOOLEAN DEFAULT TRUE,
                languages TEXT DEFAULT 'fr,en',
                google_location_id VARCHAR(255),
                google_access_token TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_reviews (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                review_id VARCHAR(255) UNIQUE NOT NULL,
                reviewer_name VARCHAR(255) NOT NULL,
                rating INTEGER NOT NULL,
                review_text TEXT,
                review_language VARCHAR(10),
                review_created_at TIMESTAMP,
                received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                context_data JSONB,
                response_text TEXT,
                response_published BOOLEAN DEFAULT FALSE,
                response_published_at TIMESTAMP,
                response_time_seconds INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_guardian_posts (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                post_id VARCHAR(255),
                title VARCHAR(500),
                content TEXT,
                published BOOLEAN DEFAULT FALSE,
                published_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS avision_guardian_stats (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id),
                total_reviews INTEGER DEFAULT 0,
                total_responses INTEGER DEFAULT 0,
                avg_response_time_minutes DECIMAL(10,2) DEFAULT 0,
                reviews_5_stars INTEGER DEFAULT 0,
                reviews_4_stars INTEGER DEFAULT 0,
                reviews_3_stars INTEGER DEFAULT 0,
                reviews_2_stars INTEGER DEFAULT 0,
                reviews_1_star INTEGER DEFAULT 0,
                last_review_at TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        ];

        foreach ($queries as $query) {
            try {
                $this->pdo->exec($query);
            } catch (\PDOException $e) {
                error_log('Table creation failed: ' . $e->getMessage());
            }
        }
        
        $this->upgrade_tables();
    }
    
    /**
     * Migration : ajout des colonnes pour le système de notifications
     */
    private function upgrade_tables() {
        if (!$this->pdo) return;
        
        $alterations = [
            "ALTER TABLE avision_audits ADD COLUMN IF NOT EXISTS last_weekly_report_sent TIMESTAMP",
            "ALTER TABLE avision_audits ADD COLUMN IF NOT EXISTS last_monthly_report_sent TIMESTAMP",
            "ALTER TABLE avision_audits ADD COLUMN IF NOT EXISTS score_at_day_1 INTEGER DEFAULT 0",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS score INTEGER DEFAULT 0",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS google_access_token TEXT",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS google_refresh_token TEXT",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS google_token_expires_at TIMESTAMP",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS google_data_cache TEXT",
            // Radar de Croissance : offert = 20 slots, hebdo (7€) = 75, elite (15€) = 100. Aliases : weekly = hebdo, daily = elite.
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS radar_plan VARCHAR(20) DEFAULT 'offert'",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS max_keywords INTEGER DEFAULT 20",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS guardian_active SMALLINT DEFAULT 0",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS pilot_guardian_mode SMALLINT DEFAULT 0",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS is_sealed SMALLINT DEFAULT 0",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS guardian_expiry_date TIMESTAMP",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS pilot_reviews_mode VARCHAR(20) DEFAULT 'manual'",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS pilot_posts_mode VARCHAR(20) DEFAULT 'manual'",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS last_radar_scan TIMESTAMP",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS next_radar_scan TIMESTAMP",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS keywords_data TEXT",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS neighborhood VARCHAR(255) DEFAULT ''",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS google_location_name VARCHAR(512)",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS last_metrics_snapshot TEXT",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS is_first_sync SMALLINT DEFAULT 1",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS country VARCHAR(5) DEFAULT 'FR'",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS account_type VARCHAR(20) DEFAULT 'premium'",
            "ALTER TABLE " . $this->table( 'avi_saas_profiles' ) . " ADD COLUMN IF NOT EXISTS user_type VARCHAR(20) DEFAULT 'b2c'",
            "ALTER TABLE " . $this->table( 'avi_saas_competitors' ) . " ADD COLUMN IF NOT EXISTS rating DECIMAL(3,2)",
            "ALTER TABLE " . $this->table( 'avi_saas_competitors' ) . " ADD COLUMN IF NOT EXISTS reviews_count INTEGER",
            "ALTER TABLE " . $this->table( 'avi_saas_keywords' ) . " ADD COLUMN IF NOT EXISTS rank_init INTEGER",
            "ALTER TABLE " . $this->table( 'avi_saas_keywords' ) . " ADD COLUMN IF NOT EXISTS scan_status VARCHAR(20) DEFAULT 'pending'",
            "ALTER TABLE " . $this->table( 'avi_saas_keywords' ) . " ADD COLUMN IF NOT EXISTS last_scan_at TIMESTAMP"
        ];

        // Tables SaaS : utilisateurs et profils (liaison formulaire audit local-seo)
        $t_users = $this->table( 'avi_saas_users' );
        $t_profiles = $this->table( 'avi_saas_profiles' );
        $t_sessions = $this->table( 'avi_saas_sessions' );
        $create_saas_tables = [
            "CREATE TABLE IF NOT EXISTS {$t_users} (
                id SERIAL PRIMARY KEY,
                email VARCHAR(255) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS {$t_profiles} (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES {$t_users}(id) ON DELETE CASCADE,
                business_name VARCHAR(255) NOT NULL,
                city VARCHAR(255) NOT NULL,
                neighborhood VARCHAR(255) NOT NULL DEFAULT '',
                category VARCHAR(255) NOT NULL,
                current_level INTEGER NOT NULL DEFAULT 1,
                score INTEGER DEFAULT 0,
                promotion_expires_at TIMESTAMP,
                user_type VARCHAR(20) NOT NULL DEFAULT 'b2c',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE TABLE IF NOT EXISTS {$t_sessions} (
                id SERIAL PRIMARY KEY,
                token VARCHAR(64) UNIQUE NOT NULL,
                user_id INTEGER NOT NULL REFERENCES {$t_users}(id) ON DELETE CASCADE,
                profile_id INTEGER NOT NULL REFERENCES {$t_profiles}(id) ON DELETE CASCADE,
                expires_at TIMESTAMP NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        ];
        foreach ($create_saas_tables as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI SaaS tables: ' . $e->getMessage());
            }
        }

        // Table positions mots-clés (15 stratégiques par audit) + abonnement Top 3
        $create_keyword_tables = [
            "CREATE TABLE IF NOT EXISTS avision_keyword_ranks (
                id SERIAL PRIMARY KEY,
                audit_id INTEGER REFERENCES avision_audits(id) ON DELETE CASCADE,
                keyword_index SMALLINT NOT NULL CHECK (keyword_index >= 1 AND keyword_index <= 15),
                keyword VARCHAR(255) NOT NULL,
                position INTEGER,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(audit_id, keyword_index)
            )",
            "CREATE TABLE IF NOT EXISTS avision_keyword_top3_subscriptions (
                audit_id INTEGER REFERENCES avision_audits(id) ON DELETE CASCADE PRIMARY KEY,
                is_active BOOLEAN DEFAULT TRUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )"
        ];
        foreach ($create_keyword_tables as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI keyword tables: ' . $e->getMessage());
            }
        }

        // Table historique Radar (snapshots pour le graphique vert/bleu/jaune)
        $t_rank = $this->table( 'avi_saas_rank_history' );
        $create_rank_history = [
            "CREATE TABLE IF NOT EXISTS {$t_rank} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                date TIMESTAMP NOT NULL,
                avg_position DOUBLE PRECISION,
                top3_count INTEGER DEFAULT 0,
                top10_count INTEGER DEFAULT 0,
                net_worth_value DOUBLE PRECISION,
                score INTEGER
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_rank_history_profile ON {$t_rank} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_rank_history_profile_date ON {$t_rank} (profile_id, date DESC)"
        ];
        foreach ($create_rank_history as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI rank history table: ' . $e->getMessage());
            }
        }
        // Migration : ajout colonne score (installations existantes). PostgreSQL : IF NOT EXISTS ; MySQL : try/catch Duplicate.
        try {
            $driver = $this->pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
            $t_rank = $this->table( 'avi_saas_rank_history' );
            if (strtolower($driver) === 'pgsql') {
                $this->pdo->exec("ALTER TABLE {$t_rank} ADD COLUMN IF NOT EXISTS score INTEGER");
            } else {
                $this->pdo->exec("ALTER TABLE {$t_rank} ADD COLUMN score INT NULL");
            }
        } catch (\PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate column') === false && strpos($e->getMessage(), 'already exists') === false) {
                error_log('AVI rank history score column: ' . $e->getMessage());
            }
        }

        // Table actions Mode Gardien (Centre d'Intervention / Journal de bord)
        $t_actions = $this->table( 'avi_saas_actions' );
        $create_actions = [
            "CREATE TABLE IF NOT EXISTS {$t_actions} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                type VARCHAR(32) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'pending',
                priority INTEGER NOT NULL DEFAULT 2,
                content TEXT,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_actions_profile ON {$t_actions} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_actions_profile_status ON {$t_actions} (profile_id, status)",
            "CREATE INDEX IF NOT EXISTS idx_avi_actions_created ON {$t_actions} (created_at DESC)"
        ];
        foreach ($create_actions as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas actions table: ' . $e->getMessage());
            }
        }

        // Table Galerie Intelligente (medias pour Drip-Feed hebdo sur Google)
        $t_media = $this->table( 'avi_saas_media' );
        $create_media = [
            "CREATE TABLE IF NOT EXISTS {$t_media} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                file_path VARCHAR(1024) NOT NULL,
                media_type VARCHAR(32) NOT NULL DEFAULT 'photo',
                category VARCHAR(64),
                status VARCHAR(32) NOT NULL DEFAULT 'in_stock',
                scheduled_at TIMESTAMP,
                published_at TIMESTAMP,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_media_profile ON {$t_media} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_media_profile_status ON {$t_media} (profile_id, status)",
            "CREATE INDEX IF NOT EXISTS idx_avi_media_scheduled ON {$t_media} (scheduled_at)",
            "CREATE INDEX IF NOT EXISTS idx_avi_media_created ON {$t_media} (created_at DESC)"
        ];
        foreach ($create_media as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas media table: ' . $e->getMessage());
            }
        }

        // Table concurrents (Spy-Alert / Sentinel Top 3)
        $t_comp = $this->table( 'avi_saas_competitors' );
        $create_competitors = [
            "CREATE TABLE IF NOT EXISTS {$t_comp} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                competitor_name VARCHAR(255) NOT NULL,
                google_place_id VARCHAR(255),
                main_category VARCHAR(255),
                current_rank INTEGER,
                last_check TIMESTAMP,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_competitors_profile ON {$t_comp} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_competitors_profile_place ON {$t_comp} (profile_id, google_place_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_competitors_last_check ON {$t_comp} (last_check DESC)"
        ];
        foreach ($create_competitors as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas competitors table: ' . $e->getMessage());
            }
        }

        // Table mots-clés Radar granulaire (positions chirurgicales + Sentinel)
        $t_kw = $this->table( 'avi_saas_keywords' );
        $create_keywords = [
            "CREATE TABLE IF NOT EXISTS {$t_kw} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                keyword VARCHAR(255) NOT NULL,
                target_city VARCHAR(255),
                current_rank INTEGER,
                previous_rank INTEGER,
                best_rank INTEGER,
                is_strategic SMALLINT NOT NULL DEFAULT 0,
                updated_at TIMESTAMP,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_keywords_profile ON {$t_kw} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_keywords_profile_kw ON {$t_kw} (profile_id, keyword)",
            "CREATE INDEX IF NOT EXISTS idx_avi_keywords_strategic ON {$t_kw} (profile_id, is_strategic)",
            "CREATE INDEX IF NOT EXISTS idx_avi_keywords_updated ON {$t_kw} (updated_at DESC)"
        ];
        foreach ($create_keywords as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas keywords table: ' . $e->getMessage());
            }
        }

        // Table tâches API / Salle d'Attente Webhook (DataForSEO, etc.)
        $t_tasks = $this->table( 'avi_saas_api_tasks' );
        $create_api_tasks = [
            "CREATE TABLE IF NOT EXISTS {$t_tasks} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER NOT NULL,
                external_task_id VARCHAR(255),
                task_type VARCHAR(64) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                completed_at TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_api_tasks_profile ON {$t_tasks} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_api_tasks_external ON {$t_tasks} (external_task_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_api_tasks_status ON {$t_tasks} (status)",
            "CREATE INDEX IF NOT EXISTS idx_avi_api_tasks_created ON {$t_tasks} (created_at DESC)"
        ];
        foreach ($create_api_tasks as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas api_tasks table: ' . $e->getMessage());
            }
        }

        // Table logs / Boîte Noire (santé du système automatisé)
        $t_logs = $this->table( 'avi_saas_logs' );
        $create_logs = [
            "CREATE TABLE IF NOT EXISTS {$t_logs} (
                id SERIAL PRIMARY KEY,
                profile_id INTEGER,
                event_type VARCHAR(64) NOT NULL,
                severity VARCHAR(32) NOT NULL DEFAULT 'info',
                message TEXT,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
            )",
            "CREATE INDEX IF NOT EXISTS idx_avi_logs_profile ON {$t_logs} (profile_id)",
            "CREATE INDEX IF NOT EXISTS idx_avi_logs_event ON {$t_logs} (event_type)",
            "CREATE INDEX IF NOT EXISTS idx_avi_logs_severity ON {$t_logs} (severity)",
            "CREATE INDEX IF NOT EXISTS idx_avi_logs_created ON {$t_logs} (created_at DESC)"
        ];
        foreach ($create_logs as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI saas logs table: ' . $e->getMessage());
            }
        }
        
        foreach ($alterations as $sql) {
            try {
                $this->pdo->exec($sql);
            } catch (\PDOException $e) {
                error_log('AVI Notifications upgrade: ' . $e->getMessage());
            }
        }

        // WordPress/MySQL : correction chirurgicale du schéma — colonnes indispensables pour le CRON (MySQL n'a pas ADD COLUMN IF NOT EXISTS).
        global $wpdb;
        if ( isset( $wpdb ) && $wpdb ) {
            $t_kw       = $this->table( 'avi_saas_keywords' );
            $t_profiles = $this->table( 'avi_saas_profiles' );
            $schema = defined( 'DB_NAME' ) ? DB_NAME : '';
            if ( $schema !== '' ) {
                $keywords_columns = [
                    'rank_init'    => 'rank_init INT DEFAULT NULL',
                    'scan_status'  => "scan_status VARCHAR(20) DEFAULT 'pending'",
                    'last_scan_at' => 'last_scan_at DATETIME DEFAULT NULL',
                ];
                foreach ( $keywords_columns as $col_name => $col_def ) {
                    $col = $wpdb->get_results( $wpdb->prepare(
                        "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                        $schema,
                        $t_kw,
                        $col_name
                    ) );
                    if ( empty( $col ) ) {
                        $wpdb->query( "ALTER TABLE `{$t_kw}` ADD COLUMN {$col_def}" );
                    }
                }
                $profiles_columns = [
                    'radar_plan'       => "radar_plan VARCHAR(20) DEFAULT 'offert'",
                    'guardian_active'  => "guardian_active SMALLINT DEFAULT 0",
                ];
                foreach ( $profiles_columns as $col_name => $col_def ) {
                    $col = $wpdb->get_results( $wpdb->prepare(
                        "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                        $schema,
                        $t_profiles,
                        $col_name
                    ) );
                    if ( empty( $col ) ) {
                        $wpdb->query( "ALTER TABLE `{$t_profiles}` ADD COLUMN {$col_def}" );
                    }
                }

                // --- Migration one-shot : rattrapage des données existantes ---
                $migration_key = 'avi_migration_rank_init_backfill_done';
                if ( ! get_option( $migration_key ) ) {
                    // 1) Copier previous_rank → rank_init pour les mots-clés déjà scannés mais sans J0
                    $backfill_ri = $wpdb->query(
                        "UPDATE `{$t_kw}` SET rank_init = previous_rank WHERE (rank_init IS NULL OR rank_init = 0) AND previous_rank IS NOT NULL AND previous_rank > 0"
                    );
                    // 2) Copier current_rank → rank_init si previous_rank aussi est vide
                    $backfill_cr = $wpdb->query(
                        "UPDATE `{$t_kw}` SET rank_init = current_rank WHERE (rank_init IS NULL OR rank_init = 0) AND current_rank IS NOT NULL AND current_rank > 0"
                    );
                    // 3) Marquer scan_status = 'completed' pour les mots-clés déjà scannés (current_rank rempli)
                    $backfill_ss = $wpdb->query(
                        "UPDATE `{$t_kw}` SET scan_status = 'completed' WHERE (scan_status IS NULL OR scan_status = 'pending') AND current_rank IS NOT NULL AND current_rank > 0"
                    );
                    update_option( $migration_key, '1' );
                    if ( function_exists( 'error_log' ) ) {
                        error_log( '[AVI Migration] rank_init backfill: ' . (int) $backfill_ri . ' from previous_rank, ' . (int) $backfill_cr . ' from current_rank. scan_status fixed: ' . (int) $backfill_ss );
                    }
                }
            }
        }

        try {
            $this->pdo->exec("UPDATE avision_audits SET score_at_day_1 = score WHERE score_at_day_1 = 0 OR score_at_day_1 IS NULL");
        } catch (\PDOException $e) {
            // Colonne peut ne pas exister encore
        }
    }

    public function get_connection() {
        return $this->pdo;
    }

    /**
     * Récupère les positions des 15 mots-clés pour plusieurs audits en une seule requête.
     * Optimisation : une requête avec IN pour éviter N+1.
     *
     * @param int[] $audit_ids Liste des IDs d'audit
     * @return array [ audit_id => [ 1 => ['keyword' => '...', 'position' => int|null], ... ], ... ]
     */
    public function get_keyword_ranks_for_audits(array $audit_ids) {
        if (!$this->pdo || empty($audit_ids)) return [];

        $placeholders = implode(',', array_fill(0, count($audit_ids), '?'));
        $stmt = $this->pdo->prepare("
            SELECT kr.audit_id, kr.keyword_index, kr.keyword, kr.position, t3.is_active as has_top3
            FROM avision_keyword_ranks kr
            LEFT JOIN avision_keyword_top3_subscriptions t3 ON t3.audit_id = kr.audit_id AND t3.is_active = true
            WHERE kr.audit_id IN ({$placeholders})
            ORDER BY kr.audit_id, kr.keyword_index
        ");
        $stmt->execute(array_values($audit_ids));
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $result = [];
        foreach ($rows as $row) {
            $aid = (int) $row['audit_id'];
            if (!isset($result[$aid])) $result[$aid] = ['has_top3' => !empty($row['has_top3']), 'keywords' => []];
            $result[$aid]['keywords'][(int) $row['keyword_index']] = [
                'keyword' => $row['keyword'],
                'position' => $row['position'] !== null ? (int) $row['position'] : null
            ];
        }
        return $result;
    }

    /**
     * Liste des audit_ids ayant l'abonnement Top 3 actif.
     *
     * @param int[] $audit_ids
     * @return int[]
     */
    public function get_audit_ids_with_top3(array $audit_ids) {
        if (!$this->pdo || empty($audit_ids)) return [];

        $placeholders = implode(',', array_fill(0, count($audit_ids), '?'));
        $stmt = $this->pdo->prepare("
            SELECT audit_id FROM avision_keyword_top3_subscriptions
            WHERE audit_id IN ({$placeholders}) AND is_active = true
        ");
        $stmt->execute(array_values($audit_ids));
        return array_column($stmt->fetchAll(\PDO::FETCH_ASSOC), 'audit_id');
    }

    public function create_audit($data) {
        if (!$this->pdo) return null;

        $stmt = $this->pdo->prepare("
            INSERT INTO avision_audits (business_name, email, city, category, language, audit_link)
            VALUES (:business_name, :email, :city, :category, :language, :audit_link)
            RETURNING id
        ");

        $audit_link = $this->generate_audit_link();
        
        $stmt->execute([
            ':business_name' => $data['business_name'],
            ':email' => $data['email'],
            ':city' => $data['city'],
            ':category' => $data['category'],
            ':language' => $data['language'] ?? 'en',
            ':audit_link' => $audit_link
        ]);

        $result = $stmt->fetch();
        
        $this->pdo->prepare("
            INSERT INTO avision_user_progress (audit_id)
            VALUES (:audit_id)
        ")->execute([':audit_id' => $result['id']]);

        return [
            'id' => $result['id'],
            'audit_link' => $audit_link
        ];
    }

    private function generate_audit_link() {
        return bin2hex(random_bytes(16));
    }

    /**
     * SaaS : récupère un utilisateur par email ou le crée.
     *
     * @param string $email Email unique
     * @return int|null ID utilisateur ou null si erreur
     */
    public function get_or_create_saas_user($email) {
        if (!$this->pdo) return null;
        $email = trim($email);
        if ($email === '') return null;

        $stmt = $this->pdo->prepare("SELECT id FROM " . $this->table( 'avi_saas_users' ) . " WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $email]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($row) {
            return (int) $row['id'];
        }

        $stmt = $this->pdo->prepare("
            INSERT INTO " . $this->table( 'avi_saas_users' ) . " (email) VALUES (:email)
            ON CONFLICT (email) DO UPDATE SET updated_at = CURRENT_TIMESTAMP
            RETURNING id
        ");
        $stmt->execute([':email' => $email]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $result ? (int) $result['id'] : null;
    }

    /**
     * SaaS : crée un profil lié à l'utilisateur (current_level = 1, promotion 24h).
     *
     * @param int $user_id ID avi_saas_users
     * @param string $business_name Nom business
     * @param string $city Ville
     * @param string $category Métier / catégorie
     * @param string|null $promotion_expires_at ISO date (now + 24h si null)
     * @param int|null $score Score initial (optionnel)
     * @param string $neighborhood Quartier (optionnel, défaut '')
     * @param string $country Code pays FR, GB, US, ES (défaut 'FR')
     * @param string $phone     Téléphone (optionnel, ex. métadonnées Stripe — sanitize côté appelant ou ici)
     * @return array|null ['id' => int, 'current_level' => 1, 'created_at' => string, 'promotion_expires_at' => string, 'score' => int] ou null
     */
    public function create_saas_profile($user_id, $business_name, $city, $category, $promotion_expires_at = null, $score = null, $neighborhood = '', $country = 'FR', $phone = '') {
        if (!$this->pdo) return null;
        $user_id = (int) $user_id;
        if ($user_id <= 0) return null;

        if ($promotion_expires_at === null) {
            $promotion_expires_at = gmdate('Y-m-d H:i:s', strtotime('+24 hours'));
        }
        $score = $score !== null ? (int) $score : 0;
        $neighborhood = $neighborhood !== null ? trim((string) $neighborhood) : '';
        $country = in_array(strtoupper(trim((string) $country)), ['FR', 'GB', 'US', 'ES'], true) ? strtoupper(trim((string) $country)) : 'FR';
        $phone_clean = $phone !== null && (string) $phone !== ''
            ? ( function_exists( 'sanitize_text_field' ) ? \sanitize_text_field( trim( (string) $phone ) ) : trim( (string) $phone ) )
            : '';

        $stmt = $this->pdo->prepare("
            INSERT INTO " . $this->table( 'avi_saas_profiles' ) . " (user_id, business_name, city, neighborhood, category, current_level, promotion_expires_at, score, country, phone)
            VALUES (:user_id, :business_name, :city, :neighborhood, :category, 1, :promotion_expires_at, :score, :country, :phone)
            RETURNING id, current_level, created_at, promotion_expires_at, score
        ");
        $stmt->execute([
            ':user_id' => $user_id,
            ':business_name' => $business_name,
            ':city' => $city,
            ':neighborhood' => $neighborhood,
            ':category' => $category,
            ':promotion_expires_at' => $promotion_expires_at,
            ':score' => $score,
            ':country' => $country,
            ':phone' => $phone_clean,
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ? [
            'id' => (int) $row['id'],
            'current_level' => (int) $row['current_level'],
            'created_at' => $row['created_at'],
            'promotion_expires_at' => $row['promotion_expires_at'],
            'score' => isset($row['score']) ? (int) $row['score'] : 0,
        ] : null;
    }

    /**
     * SaaS : crée une session (token) pour user_id + profile_id. Expiration par défaut 7 jours.
     *
     * @param int $user_id
     * @param int $profile_id
     * @param int $ttl_seconds Durée de vie en secondes (défaut 604800 = 7 jours)
     * @return array|null ['token' => string, 'expires_at' => string] ou null
     */
    public function create_saas_session($user_id, $profile_id, $ttl_seconds = 604800) {
        if (!$this->pdo) return null;
        $user_id = (int) $user_id;
        $profile_id = (int) $profile_id;
        if ($user_id <= 0 || $profile_id <= 0) return null;
        $token = bin2hex(random_bytes(32));
        $expires_at = gmdate('Y-m-d H:i:s', time() + $ttl_seconds);
        $stmt = $this->pdo->prepare("
            INSERT INTO " . $this->table( 'avi_saas_sessions' ) . " (token, user_id, profile_id, expires_at)
            VALUES (:token, :user_id, :profile_id, :expires_at)
        ");
        $stmt->execute([
            ':token' => $token,
            ':user_id' => $user_id,
            ':profile_id' => $profile_id,
            ':expires_at' => $expires_at,
        ]);
        return ['token' => $token, 'expires_at' => $expires_at];
    }

    /**
     * SaaS : récupère une session par token si valide (non expirée).
     *
     * @param string $token
     * @return array|null ['user_id' => int, 'profile_id' => int] ou null
     */
    public function get_saas_session_by_token($token) {
        if (!$this->pdo || empty($token)) return null;
        $stmt = $this->pdo->prepare("
            SELECT user_id, profile_id FROM " . $this->table( 'avi_saas_sessions' ) . "
            WHERE token = :token AND expires_at > CURRENT_TIMESTAMP
            LIMIT 1
        ");
        $stmt->execute([':token' => $token]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ? ['user_id' => (int) $row['user_id'], 'profile_id' => (int) $row['profile_id']] : null;
    }

    /**
     * SaaS : récupère un profil par ID.
     *
     * @param int $profile_id
     * @return array|null Profil (business_name, city, category, current_level, score, ...) ou null
     */
    public function get_saas_profile_by_id($profile_id) {
        if (!$this->pdo || (int) $profile_id <= 0) return null;
        $stmt = $this->pdo->prepare("
            SELECT id, user_id, business_name, city, COALESCE(neighborhood, '') AS neighborhood, category,
                   COALESCE(NULLIF(TRIM(google_primary_category), ''), NULL) AS google_primary_category,
                   current_level,
                   COALESCE(score, 0) AS score, promotion_expires_at, created_at, updated_at,
                   COALESCE(radar_plan, 'offert') AS radar_plan, COALESCE(max_keywords, 20) AS max_keywords,
                   COALESCE(guardian_active, 0) AS guardian_active, guardian_expiry_date,
                   COALESCE(pilot_reviews_mode, 'manual') AS pilot_reviews_mode, COALESCE(pilot_posts_mode, 'manual') AS pilot_posts_mode,
                   last_radar_scan, next_radar_scan, keywords_data,
                   google_place_id
            FROM " . $this->table( 'avi_saas_profiles' ) . " WHERE id = :id LIMIT 1
        ");
        $stmt->execute([':id' => (int) $profile_id]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * SaaS : récupère un profil par user_id et business_name (ex. profil "ADMIN TEST STORE").
     *
     * @param int $user_id
     * @param string $business_name
     * @return array|null Profil (id, user_id, business_name, city, category, current_level, score, ...) ou null
     */
    public function get_saas_profile_by_user_and_business_name($user_id, $business_name) {
        if (!$this->pdo || (int) $user_id <= 0 || trim($business_name) === '') return null;
        $stmt = $this->pdo->prepare("
            SELECT id, user_id, business_name, city, COALESCE(neighborhood, '') AS neighborhood, category, current_level,
                   COALESCE(score, 0) AS score, promotion_expires_at, created_at, updated_at,
                   COALESCE(radar_plan, 'offert') AS radar_plan, COALESCE(max_keywords, 20) AS max_keywords,
                   COALESCE(guardian_active, 0) AS guardian_active, guardian_expiry_date,
                   COALESCE(pilot_reviews_mode, 'manual') AS pilot_reviews_mode, COALESCE(pilot_posts_mode, 'manual') AS pilot_posts_mode,
                   last_radar_scan, next_radar_scan, keywords_data
            FROM " . $this->table( 'avi_saas_profiles' ) . " WHERE user_id = :user_id AND business_name = :business_name LIMIT 1
        ");
        $stmt->execute([
            ':user_id' => (int) $user_id,
            ':business_name' => trim($business_name),
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * SaaS : récupère le premier profil associé à un utilisateur (avi_saas_users.id).
     *
     * @param int $user_id ID avi_saas_users
     * @return array|null Profil (id, user_id, business_name, ...) ou null
     */
    public function get_first_saas_profile_by_user_id($user_id) {
        if (!$this->pdo || (int) $user_id <= 0) return null;
        $stmt = $this->pdo->prepare("
            SELECT id, user_id, business_name, city, COALESCE(neighborhood, '') AS neighborhood, category,
                   COALESCE(NULLIF(TRIM(google_primary_category), ''), NULL) AS google_primary_category,
                   current_level,
                   COALESCE(score, 0) AS score, promotion_expires_at, created_at, updated_at,
                   COALESCE(radar_plan, 'offert') AS radar_plan, COALESCE(max_keywords, 20) AS max_keywords,
                   COALESCE(guardian_active, 0) AS guardian_active, guardian_expiry_date,
                   COALESCE(pilot_reviews_mode, 'manual') AS pilot_reviews_mode, COALESCE(pilot_posts_mode, 'manual') AS pilot_posts_mode,
                   last_radar_scan, next_radar_scan, keywords_data
            FROM " . $this->table( 'avi_saas_profiles' ) . " WHERE user_id = :user_id ORDER BY id ASC LIMIT 1
        ");
        $stmt->execute([':user_id' => (int) $user_id]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * SaaS : récupère tous les profils d'un utilisateur (avi_saas_users.id).
     *
     * @param int $user_id ID avi_saas_users
     * @return array Liste de profils (id, user_id, business_name, city, category, current_level, score, ...)
     */
    public function get_all_saas_profiles_by_user_id($user_id) {
        if (!$this->pdo || (int) $user_id <= 0) return array();
        $stmt = $this->pdo->prepare("
            SELECT id, user_id, business_name, city, COALESCE(neighborhood, '') AS neighborhood, category, current_level,
                   COALESCE(score, 0) AS score, promotion_expires_at, created_at, updated_at,
                   COALESCE(radar_plan, 'offert') AS radar_plan, COALESCE(max_keywords, 20) AS max_keywords,
                   COALESCE(guardian_active, 0) AS guardian_active, guardian_expiry_date,
                   COALESCE(pilot_reviews_mode, 'manual') AS pilot_reviews_mode, COALESCE(pilot_posts_mode, 'manual') AS pilot_posts_mode,
                   last_radar_scan, next_radar_scan, keywords_data
            FROM " . $this->table( 'avi_saas_profiles' ) . " WHERE user_id = :user_id ORDER BY id ASC
        ");
        $stmt->execute([':user_id' => (int) $user_id]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        return $rows ?: array();
    }

    /**
     * SaaS : met à jour le current_level d'un profil (sauvegarde progression des niveaux).
     *
     * @param int $profile_id
     * @param int $current_level 1–21
     * @return bool
     */
    public function update_saas_profile_current_level($profile_id, $current_level) {
        if (!$this->pdo || (int) $profile_id <= 0) return false;
        $current_level = max(1, min(21, (int) $current_level));
        $stmt = $this->pdo->prepare("
            UPDATE " . $this->table( 'avi_saas_profiles' ) . " SET current_level = :current_level, updated_at = CURRENT_TIMESTAMP WHERE id = :id
        ");
        $stmt->execute([':current_level' => $current_level, ':id' => (int) $profile_id]);
        return true;
    }

    /**
     * Délègue à insert_initial_keywords_from_profile_data pour garder une SEULE source de création des mots-clés
     * (avec "Best" pour Londres / villes anglophones). Après insertion, lance le scan DataForSEO (J0) et enregistre
     * la position réelle dans rank_init (J0 immuable) et current_rank. Utilise location_code et language_code du profil.
     *
     * @param int $profile_id ID du profil (avi_saas_profiles.id)
     * @return bool True si au moins un mot-clé a été inséré
     */
    public function insert_initial_keywords_for_profile($profile_id) {
        if ((int) $profile_id <= 0) return false;
        $profile_id = (int) $profile_id;
        $profile = $this->get_saas_profile_for_sync($profile_id);
        if (!$profile || !is_array($profile)) {
            return false;
        }
        $inserted = $this->insert_initial_keywords_from_profile_data($profile_id, $profile);
        if (!$inserted) {
            return false;
        }
        $this->run_j0_scan_for_profile_keywords($profile_id, $profile);
        return true;
    }

    /**
     * Scan DataForSEO juste après l'insertion des mots-clés initiaux : position réelle → rank_init (J0) + current_rank.
     * Utilise le country du profil pour location_code et language_code.
     *
     * @param int $profile_id
     * @param array $profile Doit contenir business_name, country, city
     */
    private function run_j0_scan_for_profile_keywords($profile_id, array $profile) {
        $keywords = $this->get_keywords_by_profile_id_wpdb($profile_id, 10);
        if (empty($keywords)) {
            return;
        }
        $business_name = isset($profile['business_name']) ? trim((string) $profile['business_name']) : '';
        $business_lower = $business_name !== '' ? mb_strtolower($business_name) : '';
        $country = isset($profile['country']) ? strtoupper(trim((string) $profile['country'])) : 'FR';
        $city = isset($profile['city']) ? trim((string) $profile['city']) : '';

        foreach ($keywords as $kw) {
            $kw_id = isset($kw['id']) ? (int) $kw['id'] : 0;
            $kw_keyword = isset($kw['keyword']) ? trim((string) $kw['keyword']) : '';
            $kw_city = isset($kw['target_city']) && trim((string) $kw['target_city']) !== '' ? trim($kw['target_city']) : $city;
            if ($kw_keyword === '' || $kw_id <= 0) {
                continue;
            }
            $maps_results = $this->fetch_dataforseo_maps_sector($kw_keyword, $kw_city, $country, $profile_id);
            if (is_wp_error($maps_results)) {
                $this->update_keyword_rank_j0($kw_id, 101);
                continue;
            }
            $client_rank = null;
            if (!empty($maps_results['items'])) {
                foreach ($maps_results['items'] as $idx => $item) {
                    $title = isset($item['title']) ? $item['title'] : (isset($item['original_title']) ? $item['original_title'] : '');
                    $title_lower = $title !== '' ? mb_strtolower($title) : '';
                    if ($business_lower !== '' && $title_lower !== '' && (mb_strpos($title_lower, $business_lower) !== false || mb_strpos($business_lower, $title_lower) !== false)) {
                        $client_rank = isset($item['rank_absolute']) ? (int) $item['rank_absolute'] : ($idx + 1);
                        break;
                    }
                }
            }
            $this->update_keyword_rank_j0($kw_id, $client_rank !== null && $client_rank <= 20 ? $client_rank : 101);
        }
    }

    /**
     * Insère les 3 mots-clés d'onboarding à partir des données du profil déjà chargé (ex. depuis $wpdb).
     * KW1 : [Category] [City], KW2 : [Category] [Neighborhood] (si neighborhood existe), KW3 : [Category] + "à proximité"/"near me"/"cerca de mi".
     * Utilise $wpdb pour rester aligné avec la même base que get_saas_profile_for_sync.
     *
     * @param int   $profile_id
     * @param array $profile Doit contenir au minimum category, city, et optionnellement neighborhood
     * @return bool True si au moins un mot-clé a été inséré
     */
    public function insert_initial_keywords_from_profile_data($profile_id, array $profile) {
        $profile_id = (int) $profile_id;
        if ($profile_id <= 0) {
            return false;
        }
        $existing = $this->get_keywords_by_profile_id_wpdb($profile_id, 1);
        if (!empty($existing)) {
            return false;
        }
        $category = isset($profile['category']) ? trim((string) $profile['category']) : '';
        $city = isset($profile['city']) ? trim((string) $profile['city']) : '';
        $neighborhood = isset($profile['neighborhood']) ? trim((string) $profile['neighborhood']) : '';
        $country = isset($profile['country']) ? strtoupper(trim((string) $profile['country'])) : 'FR';
        if ($category === '' || $city === '') {
            return false;
        }
        $is_english_country = ($country === 'GB' || $country === 'US');
        $is_spanish_country = ($country === 'ES');

        global $wpdb;
        $table = $this->table('avi_saas_keywords');
        if ($profile_id === 1 && $is_english_country) {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET keyword = REPLACE(keyword, 'Meilleur ', 'Best ') WHERE profile_id = %d AND keyword LIKE 'Meilleur %%'",
                $profile_id
            ));
        }
        $to_insert = [];
        $kw1 = trim($category . ' ' . $city);
        if ($kw1 !== '') {
            $to_insert[] = ['keyword' => $kw1, 'target_city' => $city];
        }
        if ($neighborhood !== '') {
            $kw2 = trim($category . ' ' . $neighborhood);
            if ($kw2 !== '') {
                $to_insert[] = ['keyword' => $kw2, 'target_city' => $neighborhood];
            }
        }
        if ($is_english_country) {
            $kw3 = trim($category . ' near me');
        } elseif ($is_spanish_country) {
            $kw3 = trim($category . ' cerca de mi');
        } else {
            $kw3 = trim($category . ' à proximité');
        }
        if ($kw3 !== '' && $kw3 !== $kw1) {
            $to_insert[] = ['keyword' => $kw3, 'target_city' => $city];
        }
        if (empty($to_insert)) {
            return false;
        }
        foreach ($to_insert as $row) {
            $wpdb->insert($table, [
                'profile_id'   => $profile_id,
                'keyword'     => substr($row['keyword'], 0, 255),
                'target_city' => $row['target_city'] !== '' ? substr($row['target_city'], 0, 255) : $city,
                'current_rank' => null,
                'is_strategic' => 1,
            ], ['%d', '%s', '%s', '%d', '%d']);
        }
        return true;
    }

    /**
     * Récupère les mots-clés du profil via $wpdb (même source que get_saas_profile_for_sync).
     * Utilisé après insert_initial_keywords_from_profile_data pour recharger la liste.
     *
     * @param int $profile_id
     * @param int $limit
     * @return array Liste de ['id' => int, 'keyword' => string, 'target_city' => string]
     */
    public function get_keywords_by_profile_id_wpdb($profile_id, $limit = 3) {
        $profile_id = (int) $profile_id;
        if ($profile_id <= 0) {
            return [];
        }
        global $wpdb;
        $table = $this->table('avi_saas_keywords');
        $limit = max(1, (int) $limit);
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT id, keyword, COALESCE(target_city, '') AS target_city FROM {$table} WHERE profile_id = %d ORDER BY is_strategic DESC, id ASC LIMIT %d",
            $profile_id,
            $limit
        ), ARRAY_A);
        return is_array($rows) ? $rows : [];
    }

    /**
     * Récupère les mots-clés Radar du profil (pour le scan First Sync).
     * Préfère $wpdb quand disponible pour retourner les mêmes données que le thème (ex. mots-clés "Best" après correction).
     * Sinon utilise PDO. Si vide, appelle insert_initial_keywords_for_profile puis relance la requête.
     *
     * @param int $profile_id
     * @param int $limit Max lignes (défaut 3)
     * @return array Liste de ['id' => int, 'keyword' => string, 'target_city' => string|null]
     */
    public function get_keywords_by_profile_id($profile_id, $limit = 3) {
        if ((int) $profile_id <= 0) return [];
        $profile_id = (int) $profile_id;
        $limit = max(1, (int) $limit);
        global $wpdb;
        if (isset($wpdb) && $wpdb) {
            $rows = $this->get_keywords_by_profile_id_wpdb($profile_id, $limit);
            if (!empty($rows)) {
                return $rows;
            }
        }
        if (!$this->pdo) return [];
        $stmt = $this->pdo->prepare("
            SELECT id, keyword, COALESCE(target_city, '') AS target_city
            FROM " . $this->table( 'avi_saas_keywords' ) . "
            WHERE profile_id = :pid
            ORDER BY is_strategic DESC, id ASC
            LIMIT " . $limit
        );
        $stmt->execute([':pid' => $profile_id]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $rows = is_array($rows) ? $rows : [];
        if (empty($rows)) {
            $this->insert_initial_keywords_for_profile($profile_id);
            if (isset($wpdb) && $wpdb) {
                $rows = $this->get_keywords_by_profile_id_wpdb($profile_id, $limit);
            } else {
                $stmt2 = $this->pdo->prepare("
                    SELECT id, keyword, COALESCE(target_city, '') AS target_city
                    FROM " . $this->table( 'avi_saas_keywords' ) . "
                    WHERE profile_id = :pid
                    ORDER BY is_strategic DESC, id ASC
                    LIMIT " . $limit
                );
                $stmt2->execute([':pid' => $profile_id]);
                $rows = $stmt2->fetchAll(\PDO::FETCH_ASSOC);
                $rows = is_array($rows) ? $rows : [];
            }
        }
        return $rows;
    }

    /**
     * Limite de mots-clés par plan Radar : offert 20, hebdo 75, elite 100.
     *
     * @param string $radar_plan Valeur du champ radar_plan (offert, hebdo, elite).
     * @return int Nombre max de mots-clés autorisés pour le plan.
     */
    public static function get_radar_plan_keyword_limit( $radar_plan ) {
        $plan = is_string( $radar_plan ) ? strtolower( trim( $radar_plan ) ) : 'offert';
        $limits = [ 'offert' => 20, 'hebdo' => 75, 'elite' => 100 ];
        return isset( $limits[ $plan ] ) ? $limits[ $plan ] : 20;
    }

    /**
     * Ajoute les mots-clés suggérés au profil en respectant la limite du plan (Queue Manager).
     * Vérifie radar_plan (offert: 20, hebdo: 75, elite: 100), n'insère que le nombre manquant,
     * et marque les nouveaux mots-clés en pending (scan_status = 'pending').
     *
     * @param int   $profile_id ID du profil (avi_saas_profiles.id).
     * @param array $keywords   Liste de chaînes (mots-clés choisis par l'utilisateur).
     * @return array { 'inserted' => int, 'limit' => int, 'current_total' => int } ou tableau vide en cas d'erreur.
     */
    public function add_suggested_keywords_to_profile( $profile_id, array $keywords ) {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 || empty( $keywords ) ) {
            return [ 'inserted' => 0, 'limit' => 20, 'current_total' => 0 ];
        }

        $profile = $this->get_saas_profile_by_id( $profile_id );
        $limit = 20;
        if ( $profile && ! empty( $profile['radar_plan'] ) ) {
            $limit = self::get_radar_plan_keyword_limit( $profile['radar_plan'] );
        }
        if ( $profile && isset( $profile['max_keywords'] ) && (int) $profile['max_keywords'] > 0 ) {
            $limit = min( $limit, (int) $profile['max_keywords'] );
        }

        global $wpdb;
        $table = $this->table( 'avi_saas_keywords' );
        $current_total = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM `{$table}` WHERE profile_id = %d",
            $profile_id
        ) );
        $slots_available = max( 0, $limit - $current_total );
        if ( $slots_available <= 0 ) {
            return [ 'inserted' => 0, 'limit' => $limit, 'current_total' => $current_total ];
        }

        $keywords = array_values( array_unique( array_map( 'trim', array_filter( array_map( 'strval', $keywords ) ) ) ) );
        $to_insert = array_slice( $keywords, 0, $slots_available );
        $inserted = 0;

        // Contrainte UNIQUE logique : ignorer les mots-clés déjà présents pour ce profil (insensible à la casse).
        $existing = $wpdb->get_col( $wpdb->prepare(
            "SELECT LOWER(keyword) FROM `{$table}` WHERE profile_id = %d AND keyword != ''",
            $profile_id
        ) );
        $existing = is_array( $existing ) ? $existing : [];
        $to_insert = array_values( array_filter( $to_insert, function ( $kw ) use ( $existing ) {
            return $kw !== '' && ! in_array( mb_strtolower( $kw ), $existing, true );
        } ) );
        $to_insert = array_slice( $to_insert, 0, $slots_available );

        $has_scan_status = $wpdb->get_var( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'scan_status'",
            DB_NAME,
            $table
        ) );
        $base_cols = [ 'profile_id' => $profile_id, 'keyword' => '', 'target_city' => null, 'current_rank' => null, 'is_strategic' => 0 ];
        $base_fmts = [ '%d', '%s', '%s', '%d', '%d' ];
        if ( $has_scan_status ) {
            $base_cols['scan_status'] = 'pending';
            $base_fmts[] = '%s';
        }

        foreach ( $to_insert as $kw ) {
            $kw = substr( $kw, 0, 255 );
            if ( $kw === '' ) {
                continue;
            }
            $base_cols['keyword'] = $kw;
            $result = $wpdb->insert( $table, $base_cols, $base_fmts );
            if ( $result ) {
                $inserted++;
            }
        }

        return [
            'inserted'       => $inserted,
            'limit'          => $limit,
            'current_total'  => $current_total + $inserted,
        ];
    }

    /**
     * Met à jour current_rank pour un mot-clé (scan Radar).
     *
     * @param int $keyword_id avi_saas_keywords.id
     * @param int|null $rank Position (null = Hors Radar)
     * @return bool
     */
    public function update_keyword_current_rank($keyword_id, $rank) {
        if (!$this->pdo || (int) $keyword_id <= 0) return false;
        $stmt = $this->pdo->prepare("UPDATE " . $this->table( 'avi_saas_keywords' ) . " SET current_rank = :rank, updated_at = CURRENT_TIMESTAMP WHERE id = :id");
        return $stmt->execute([':rank' => $rank !== null ? (int) $rank : null, ':id' => (int) $keyword_id]);
    }

    /**
     * Met à jour current_rank et rank_init (J0 immuable) pour un mot-clé après le premier scan.
     * Utilise $wpdb si disponible pour cohérence avec insert_initial_keywords_from_profile_data.
     *
     * @param int $keyword_id avi_saas_keywords.id
     * @param int|null $rank Position (101 = Hors Radar)
     * @return bool
     */
    public function update_keyword_rank_j0($keyword_id, $rank) {
        $keyword_id = (int) $keyword_id;
        if ($keyword_id <= 0) return false;
        $rank_val = $rank !== null ? (int) $rank : 101;
        global $wpdb;
        if (isset($wpdb) && $wpdb) {
            $table = $this->table('avi_saas_keywords');
            $data = [
                'current_rank' => $rank_val,
                'rank_init'    => $rank_val,
                'scan_status'  => 'completed',
                'updated_at'   => current_time('mysql'),
            ];
            $formats = ['%d', '%d', '%s', '%s'];
            $has_last_scan = $wpdb->get_var( "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" . DB_NAME . "' AND TABLE_NAME = '{$table}' AND COLUMN_NAME = 'last_scan_at'" );
            if ( $has_last_scan ) {
                $data['last_scan_at'] = current_time('mysql');
                $formats[] = '%s';
            }
            $updated = $wpdb->update($table, $data, ['id' => $keyword_id], $formats, ['%d']);
            return $updated !== false;
        }
        if (!$this->pdo) return false;
        $stmt = $this->pdo->prepare("UPDATE " . $this->table( 'avi_saas_keywords' ) . " SET current_rank = :rank, rank_init = :rank_init, scan_status = 'completed', last_scan_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = :id");
        return $stmt->execute([':rank' => $rank_val, ':rank_init' => $rank_val, ':id' => $keyword_id]);
    }

    /**
     * Récupère les N mots-clés les plus anciens en attente de scan (Queue Manager).
     * Statuts éligibles : pending, queued, NULL, chaîne vide — « scanning » est exclu (réservé au verrou S10-C).
     *
     * @param int $limit Nombre max de lignes (défaut 10).
     * @return array Liste de ['id' => int, 'keyword' => string, 'target_city' => string, 'profile_id' => int, 'rank_init' => int|null, ...]
     */
    public function get_pending_keywords_for_scan( $limit = 10 ) {
        $limit = max( 1, min( 50, (int) $limit ) );
        global $wpdb;
        $table = $this->table( 'avi_saas_keywords' );
        $has_scan = $wpdb->get_var( $wpdb->prepare(
            "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND LOWER(TABLE_NAME) = LOWER(%s) AND COLUMN_NAME = 'scan_status' LIMIT 1",
            defined( 'DB_NAME' ) ? DB_NAME : '',
            $table
        ) );
        if ( ! $has_scan ) {
            return [];
        }
        // id DESC : prioriser les derniers mots-clés ajoutés (sinon une longue file globale en id ASC
        // faisait attendre indéfiniment les nouveaux profils / PID).
        // Inclut '' et queued : certains inserts thème / migrations laissent scan_status vide ou non standard.
        $t_profiles = $wpdb->prefix . 'avi_saas_profiles';
        $has_strategic = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND LOWER(TABLE_NAME) = LOWER(%s) AND COLUMN_NAME = 'is_strategic' LIMIT 1",
                defined( 'DB_NAME' ) ? DB_NAME : '',
                $table
            )
        );
        if ( $has_strategic ) {
            // S9-C : appliquer le quota max_keywords PAR PROFIL (compte des mots-clés stratégiques activement trackés).
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT k.id, k.keyword, k.target_city, k.profile_id, k.rank_init, k.current_rank
                     FROM `{$table}` k
                     INNER JOIN `{$t_profiles}` p ON p.id = k.profile_id
                     LEFT JOIN (
                        SELECT profile_id, COUNT(*) AS tracked_kw
                        FROM `{$table}`
                        WHERE is_strategic = 1
                        GROUP BY profile_id
                     ) kwc ON kwc.profile_id = k.profile_id
                     WHERE (k.scan_status IN (%s, %s) OR k.scan_status IS NULL OR k.scan_status = '')
                       AND COALESCE(kwc.tracked_kw, 0) <= COALESCE(p.max_keywords, 20)
                     ORDER BY k.id DESC
                     LIMIT %d",
                    'pending',
                    'queued',
                    $limit
                ),
                ARRAY_A
            );
        } else {
            // Fallback sûr (schéma minimal) : conserver la file globale sans casser le scan.
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, keyword, target_city, profile_id, rank_init, current_rank FROM `{$table}` WHERE (scan_status IN (%s, %s) OR scan_status IS NULL OR scan_status = '') ORDER BY id DESC LIMIT %d",
                    'pending',
                    'queued',
                    $limit
                ),
                ARRAY_A
            );
        }
        $results = is_array( $rows ) ? $rows : [];
        if ( function_exists( 'error_log' ) ) {
            error_log( '[AVI DB] Found ' . count( $results ) . ' pending keywords' );
        }
        return $results;
    }

    /**
     * Mots-clés en attente pour un profil précis (bypass admin, relance manuelle).
     * Exclut les autres profils : ne dépend pas de la file globale ORDER BY id.
     *
     * @param int $profile_id ID avi_saas_profiles.
     * @param int $limit      Max lignes (défaut 10).
     * @return array<int, array<string, mixed>>
     */
    public function get_pending_keywords_for_profile_scan( $profile_id, $limit = 10 ) {
        $profile_id = (int) $profile_id;
        $limit      = max( 1, min( 50, (int) $limit ) );
        if ( $profile_id <= 0 ) {
            return [];
        }
        global $wpdb;
        $table = $this->table( 'avi_saas_keywords' );
        $has_scan = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND LOWER(TABLE_NAME) = LOWER(%s) AND COLUMN_NAME = 'scan_status' LIMIT 1",
                defined( 'DB_NAME' ) ? DB_NAME : '',
                $table
            )
        );
        if ( ! $has_scan ) {
            return [];
        }
        $order_extra = '';
        $has_strategic = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND LOWER(TABLE_NAME) = LOWER(%s) AND COLUMN_NAME = 'is_strategic' LIMIT 1",
                defined( 'DB_NAME' ) ? DB_NAME : '',
                $table
            )
        );
        if ( $has_strategic ) {
            $order_extra = 'is_strategic DESC, ';
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $order_extra est contrôlé (vide ou constante).
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, keyword, target_city, profile_id, rank_init, current_rank FROM `{$table}` WHERE profile_id = %d AND (scan_status IN (%s, %s) OR scan_status IS NULL OR scan_status = '') ORDER BY {$order_extra}id ASC LIMIT %d",
                $profile_id,
                'pending',
                'queued',
                $limit
            ),
            ARRAY_A
        );
        return is_array( $rows ) ? $rows : [];
    }

    /**
     * Met à jour un mot-clé après scan : current_rank et rank_init (J0) sont remplis dans la MÊME mise à jour lors du premier scan réussi.
     * Cette synchronisation débloque l'affichage J0 sur le dashboard dès le premier cycle CRON réussi.
     * J0 (rank_init) : fixé une seule fois au tout premier scan, ne doit JAMAIS être écrasé ensuite.
     *
     * @param int      $keyword_id
     * @param int|null $rank Position (101 = hors radar).
     * @return bool
     */
    public function update_keyword_scan_completed( $keyword_id, $rank, $is_error = false ) {
        $keyword_id = (int) $keyword_id;
        if ( $keyword_id <= 0 ) {
            return false;
        }
        $rank_val = $rank !== null ? (int) $rank : 101;
        $status = $is_error ? 'error' : 'completed';
        global $wpdb;
        if ( isset( $wpdb ) && $wpdb ) {
            $table = $this->table( 'avi_saas_keywords' );
            $row = $wpdb->get_row( $wpdb->prepare( "SELECT rank_init FROM `{$table}` WHERE id = %d", $keyword_id ), ARRAY_A );
            $current_init = isset( $row['rank_init'] ) ? $row['rank_init'] : null;
            // J0 (rank_init) : forcer l'écriture si NULL ou 0 (premier scan). Garantit que le TOUT PREMIER scan remplit J0 pour l'éternité.
            $set_rank_init = ( $row === null || $current_init === null || $current_init === '' || (int) $current_init === 0 );
            $data = [
                'current_rank' => $rank_val,
                'updated_at'   => current_time( 'mysql' ),
            ];
            $formats = [ '%d', '%s' ];
            if ( $set_rank_init ) {
                $data['rank_init'] = $rank_val;
                $formats[] = '%d';
            }
            $has_scan = $wpdb->get_var( $wpdb->prepare(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'scan_status'",
                DB_NAME,
                $table
            ) );
            if ( $has_scan ) {
                $data['scan_status']  = $status;
                $data['last_scan_at'] = current_time( 'mysql', true );
                $formats[] = '%s';
                $formats[] = '%s';
            }
            $updated = $wpdb->update( $table, $data, [ 'id' => $keyword_id ], $formats, [ '%d' ] );
            if ( function_exists( 'error_log' ) ) {
                error_log( '[AVI DB] update_keyword_scan_completed: id=' . $keyword_id . ' rank=' . $rank_val . ' status=' . $status . ' table=' . $table . ' has_scan=' . ( $has_scan ? 'yes' : 'NO' ) . ' updated=' . var_export( $updated, true ) . ' last_error=' . $wpdb->last_error );
            }
            return $updated !== false;
        }
        if ( ! $this->pdo ) {
            return false;
        }
        $t = $this->table( 'avi_saas_keywords' );
        $stmt = $this->pdo->prepare( "UPDATE {$t} SET current_rank = :rank, rank_init = COALESCE(rank_init, :rank_init), scan_status = :scan_status, last_scan_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP WHERE id = :id" );
        return $stmt->execute( [ ':rank' => $rank_val, ':rank_init' => $rank_val, ':scan_status' => $status, ':id' => $keyword_id ] );
    }

    /**
     * Verrou atomique : passe la ligne en « scanning » si encore pending/queued/NULL/vide (S10-C anti race).
     *
     * @param int $kw_id avi_saas_keywords.id
     * @return bool True si au moins une ligne a été mise à jour (ce worker détient le verrou).
     */
    private function try_claim_keyword_for_scan( int $kw_id ): bool {
        global $wpdb;
        if ( ! isset( $wpdb ) || ! $wpdb || $kw_id <= 0 ) {
            return false;
        }
        $table = $this->table( 'avi_saas_keywords' );
        $now   = current_time( 'mysql', true );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table via table().
        $affected = $wpdb->query(
            $wpdb->prepare(
                "UPDATE `{$table}`
                 SET scan_status = 'scanning', last_scan_at = %s, updated_at = %s
                 WHERE id = %d
                   AND (scan_status IN ('pending','queued')
                        OR scan_status IS NULL OR scan_status = '')",
                $now,
                $now,
                $kw_id
            )
        );
        return (int) $affected > 0;
    }

    /**
     * Scanne un mot-clé via DataForSEO Maps et met à jour la ligne (rank_init si vide, current_rank, scan_status completed).
     * Utilise les métadonnées du profil (city, country, business_name) pour l'appel API et le matching du rang.
     *
     * @param array $keyword_row Ligne avi_saas_keywords (id, keyword, target_city, profile_id).
     * @param array $profile    Profil (business_name, city, country).
     * @return bool True si le scan a été effectué et la ligne mise à jour.
     */
    public function run_single_keyword_scan( array $keyword_row, array $profile ) {
        $kw_id   = isset( $keyword_row['id'] ) ? (int) $keyword_row['id'] : 0;
        $profile_id = isset( $keyword_row['profile_id'] ) ? (int) $keyword_row['profile_id'] : 0;
        $keyword = isset( $keyword_row['keyword'] ) ? trim( (string) $keyword_row['keyword'] ) : '';
        $target_city = isset( $keyword_row['target_city'] ) && trim( (string) $keyword_row['target_city'] ) !== '' ? trim( $keyword_row['target_city'] ) : ( isset( $profile['city'] ) ? trim( (string) $profile['city'] ) : '' );
        $country = isset( $profile['country'] ) ? strtoupper( trim( (string) $profile['country'] ) ) : 'FR';
        $business_name = isset( $profile['business_name'] ) ? trim( (string) $profile['business_name'] ) : '';
        if ( $kw_id <= 0 || $keyword === '' ) {
            return false;
        }
        if ( ! $this->try_claim_keyword_for_scan( $kw_id ) ) {
            if ( function_exists( 'error_log' ) ) {
                error_log( '[AVI-S10C] Keyword id=' . (int) $kw_id . ' déjà pris ou statut non éligible — skip' );
            }
            return false;
        }
        $maps_results = $this->fetch_dataforseo_maps_sector( $keyword, $target_city, $country, $profile_id );
        if ( is_wp_error( $maps_results ) ) {
            $this->update_keyword_scan_completed( $kw_id, 101, true );
            if ( function_exists( 'error_log' ) ) {
                error_log( '[AVI-S10A] DataForSEO error kw_id=' . (int) $kw_id . ' msg=' . $maps_results->get_error_message() );
            }
            return true;
        }
        $business_lower = $business_name !== '' ? mb_strtolower( $business_name ) : '';
        $client_rank = null;
        if ( ! empty( $maps_results['items'] ) ) {
            foreach ( $maps_results['items'] as $idx => $item ) {
                $title = isset( $item['title'] ) ? $item['title'] : ( isset( $item['original_title'] ) ? $item['original_title'] : '' );
                $title_lower = $title !== '' ? mb_strtolower( $title ) : '';
                if ( $business_lower !== '' && $title_lower !== '' && ( mb_strpos( $title_lower, $business_lower ) !== false || mb_strpos( $business_lower, $title_lower ) !== false ) ) {
                    $client_rank = isset( $item['rank_absolute'] ) ? (int) $item['rank_absolute'] : ( $idx + 1 );
                    break;
                }
            }
        }
        $rank = ( $client_rank !== null && $client_rank <= 20 ) ? $client_rank : 101;
        $this->update_keyword_scan_completed( $kw_id, $rank );
        if ( function_exists( 'error_log' ) ) {
            error_log( sprintf(
                '[AVI-DATAFORSEO] kw_id=%d keyword="%s" rank=%d profile=%d status=%s',
                (int) $kw_id,
                (string) $keyword,
                (int) $rank,
                (int) $profile_id,
                ( (int) $rank <= 20 ? 'top20' : 'out' )
            ) );
        }
        if ( $profile_id > 0 && $this->is_primary_keyword_id_for_profile( $profile_id, $kw_id ) ) {
            $category = isset( $profile['category'] ) ? trim( (string) $profile['category'] ) : '';
            $leaders  = $this->extract_most_seen_neighbors_from_maps_items( $maps_results, $business_name, 3, $category );
            // ── fallback sans filtre si catégorie trop niche ──
            if ( empty( $leaders ) && $category !== '' ) {
                $leaders = $this->extract_most_seen_neighbors_from_maps_items( $maps_results, $business_name, 3, '' );
            }
            if ( ! empty( $leaders ) ) {
                $this->replace_profile_competitors( $profile_id, $leaders );
            }
        }
        return true;
    }

    // S10-A — Circuit breaker DataForSEO (transients)
    private const DATAFORSEO_CIRCUIT_ERRORS_MAX = 5;
    private const DATAFORSEO_CIRCUIT_TTL = 1800; // 30 min

    private function is_dataforseo_circuit_open(): bool {
        if ( ! function_exists( 'get_transient' ) ) {
            return false;
        }
        return (bool) get_transient( 'avi_dataforseo_circuit_open' );
    }

    private function record_dataforseo_error(): void {
        if ( ! function_exists( 'get_transient' ) || ! function_exists( 'set_transient' ) ) {
            return;
        }
        $count = (int) get_transient( 'avi_dataforseo_error_count' );
        $count++;
        set_transient( 'avi_dataforseo_error_count', $count, self::DATAFORSEO_CIRCUIT_TTL );
        if ( $count >= self::DATAFORSEO_CIRCUIT_ERRORS_MAX ) {
            set_transient( 'avi_dataforseo_circuit_open', 1, self::DATAFORSEO_CIRCUIT_TTL );
            if ( function_exists( 'error_log' ) ) {
                error_log( '[AVI-S10A] Circuit breaker OUVERT — pause 30min DataForSEO' );
            }
        }
    }

    private function reset_dataforseo_circuit(): void {
        if ( ! function_exists( 'delete_transient' ) ) {
            return;
        }
        delete_transient( 'avi_dataforseo_circuit_open' );
        delete_transient( 'avi_dataforseo_error_count' );
    }

    /**
     * Détermine si un mot-clé est le mot-clé principal d'un profil (priorité strategic puis id ASC).
     *
     * @param int $profile_id
     * @param int $keyword_id
     * @return bool
     */
    private function is_primary_keyword_id_for_profile( $profile_id, $keyword_id ) {
        $profile_id = (int) $profile_id;
        $keyword_id = (int) $keyword_id;
        if ( $profile_id <= 0 || $keyword_id <= 0 ) {
            return false;
        }
        global $wpdb;
        if ( ! isset( $wpdb ) || ! $wpdb ) {
            return false;
        }
        $table = $this->table( 'avi_saas_keywords' );
        $primary_id = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM `{$table}` WHERE profile_id = %d ORDER BY is_strategic DESC, id ASC LIMIT 1",
            $profile_id
        ) );
        return $primary_id > 0 && $primary_id === $keyword_id;
    }

    /**
     * Groupes métier GBP pour filtre concurrents Maps (FR + EN + ES).
     *
     * @return array<string, array<int, string>>
     */
    private function get_maps_category_groups() {
        $fr = array(
            'marketing' => array(
                'marketing', 'communication', 'publicité', 'pub', 'digital',
                'seo', 'web', 'média', 'agence', 'consultant', 'conseil',
                'conseiller', 'consulting', 'stratégie', 'strategy',
                'croissance', 'growth', 'branding', 'marque', 'commercial',
                'business development', 'développement commercial',
                'community management', 'community manager',
                'réseaux sociaux', 'social media', 'social',
                'création de site', 'concepteur', 'développeur web',
                'webmaster', 'webdesign', 'web design', 'studio web',
                'relations publiques', 'attaché de presse', 'publiciste',
                'inbound', 'content', 'rédaction web', 'copywriting',
                'emailing', 'email marketing', 'automation marketing',
                'référencement', 'sem', 'google ads', 'adwords', 'sea',
                'influence', 'influenceur', 'créateur de contenu',
                'agence créative', 'studio créatif', 'creative agency',
                'marketing digital', 'communication digitale',
                'print', 'imprimerie', 'graphiste', 'graphisme',
                'identité visuelle', 'logo', 'charte graphique',
            ),
            'consultant' => array(
                'management', 'organisation', 'rh', 'ressources humaines',
                'portage salarial', 'recrutement', 'formation professionnelle',
                'ingénierie conseil', 'it consulting', 'informatique conseil',
                'coach', 'coaching', 'accompagnement professionnel',
                'bilan de compétences', 'outplacement', 'transition pro',
                'cabinet conseil', 'advisory', 'audit organisationnel',
                'lean', 'agilité', 'agile', 'scrum', 'transformation',
                'change management', 'conduite du changement',
            ),
            'restaurant' => array(
                'restaurant', 'brasserie', 'bistrot', 'café', 'bar',
                'pizzeria', 'sushi', 'japonais', 'chinois', 'indien',
                'libanais', 'mexicain', 'thaï', 'vietnamien', 'italien',
                'traiteur', 'food truck', 'sandwicherie', 'burger',
                'crêperie', 'glacier', 'salon de thé', 'brunch',
                'kebab', 'tacos', 'fast food', 'restauration rapide',
                'gastronomique', 'boucherie-charcuterie', 'épicerie fine',
                'cave à vin', 'bar à vins',
            ),
            'boulangerie' => array(
                'boulangerie', 'pâtisserie', 'viennoiserie', 'boulanger',
                'pâtissier', 'confiserie', 'chocolatier', 'chocolat',
                'dessert', 'gâteau', 'wedding cake',
            ),
            'médical' => array(
                'médecin', 'docteur', 'clinique', 'cabinet médical',
                'dentiste', 'kiné', 'kinésithérapeute', 'ostéo',
                'ostéopathe', 'thérapeute', 'pharmacie', 'pharmacien',
                'infirmier', 'infirmière', 'sage-femme', 'maternité',
                'psychologue', 'psychiatre', 'psychothérapeute',
                'opticien', 'optique', 'lunettes', 'vétérinaire',
                'audioprothésiste', 'auditif', 'podologue',
                'orthophoniste', 'ergothérapeute', 'diététicien',
                'nutritionniste', 'radiologue', 'laboratoire',
                'centre médical', 'polyclinique', 'hopital',
                'acupuncteur', 'homéopathe', 'naturopathe',
                'hypnothérapeute', 'sophrologie', 'réflexologie',
            ),
            'juridique' => array(
                'avocat', 'notaire', 'huissier', 'juridique', 'droit',
                'cabinet juridique', 'cabinet d\'avocats',
                'commissaire de justice', 'mandataire judiciaire',
                'expert comptable', 'comptable', 'comptabilité',
                'commissaire aux comptes', 'fiduciaire', 'fiscaliste',
                'juriste', 'légal', 'legal',
            ),
            'immobilier' => array(
                'immobilier', 'agence immobilière', 'promoteur',
                'transaction', 'chasseur immobilier', 'gestionnaire locatif',
                'syndic', 'diagnostiqueur', 'diagnostics immobiliers',
                'estimation immobilière', 'location', 'vente immobilier',
                'lotisseur', 'constructeur maison', 'maître d\'œuvre',
                'architecte', 'architecture',
            ),
            'beauté' => array(
                'coiffeur', 'esthétique', 'salon', 'beauté', 'spa',
                'nail', 'onglerie', 'manucure', 'pédicure',
                'barbier', 'barber', 'tatouage', 'tattoo', 'piercing',
                'maquillage', 'makeup', 'microblading', 'sourcils',
                'épilation', 'laser', 'soins du visage', 'dermato',
                'centre esthétique', 'bien-être', 'détente',
                'massage', 'hammam', 'sauna', 'balnéo',
            ),
            'fitness' => array(
                'salle de sport', 'fitness', 'gym', 'coach sportif',
                'yoga', 'pilates', 'natation', 'piscine',
                'arts martiaux', 'judo', 'karaté', 'boxe', 'mma',
                'crossfit', 'musculation', 'cardio',
                'nutrition sportive', 'diététicien', 'préparateur physique',
                'danse', 'zumba', 'stretching', 'escalade', 'padel',
                'tennis', 'sport', 'stade',
            ),
            'artisan' => array(
                'plombier', 'électricien', 'maçon', 'peintre',
                'menuisier', 'charpentier', 'carreleur', 'couvreur',
                'plaquiste', 'serrurier', 'chauffagiste', 'climatisation',
                'domotique', 'vitrier', 'miroitier', 'isolation',
                'terrassement', 'paysagiste', 'jardinier', 'pisciniste',
                'déménageur', 'déménagement', 'nettoyage', 'entretien',
                'ramonage', 'fumiste', 'poêlier',
            ),
            'auto' => array(
                'garage', 'carrosserie', 'auto', 'mécanique',
                'concessionnaire', 'location voiture', 'contrôle technique',
                'pare-brise', 'pneus', 'pneumatique', 'moto',
                'scooter', 'vélo', 'véhicule', 'camion',
                'dépannage', 'remorquage', 'autoécole', 'conduite',
            ),
            'éducation' => array(
                'école', 'lycée', 'collège', 'université', 'formation',
                'cours', 'soutien scolaire', 'tutorat', 'tuteur',
                'autoécole', 'langue', 'anglais', 'espagnol',
                'musique', 'conservatoire', 'dessin', 'peinture',
                'école de commerce', 'école d\'ingénieur',
                'crèche', 'garderie', 'halte-garderie', 'baby-sitter',
                'centre de formation', 'cpf', 'apprentissage',
            ),
            'hôtellerie' => array(
                'hôtel', 'hotel', 'chambre d\'hôte', 'gîte',
                'camping', 'auberge', 'résidence', 'appart-hôtel',
                'airbnb', 'location saisonnière', 'villa', 'chalet',
                'hébergement', 'accueil', 'tourism', 'tourisme',
            ),
            'événementiel' => array(
                'événementiel', 'organisation événement', 'wedding planner',
                'mariage', 'traiteur événementiel', 'salle des fêtes',
                'salle de réception', 'séminaire', 'conférence',
                'photographe', 'photographie', 'vidéaste', 'vidéo',
                'dj', 'musicien', 'animation', 'animateur',
                'fleuriste', 'décoration', 'déco événement',
            ),
            'informatique' => array(
                'informatique', 'it', 'développeur', 'développement',
                'logiciel', 'software', 'erp', 'crm', 'saas',
                'cybersécurité', 'sécurité informatique',
                'réseau', 'infrastructure', 'cloud',
                'intelligence artificielle', 'ia', 'data',
                'réparation ordinateur', 'dépannage informatique',
                'téléphonie', 'telecom',
            ),
            'finance' => array(
                'banque', 'assurance', 'mutuelle', 'prévoyance',
                'conseiller financier', 'conseiller en gestion de patrimoine',
                'cgp', 'courtier', 'crédit', 'financement',
                'investissement', 'bourse', 'épargne', 'retraite',
                'leasing', 'affacturage', 'trésorerie',
            ),
            'retail' => array(
                'boutique', 'magasin', 'commerce', 'vente',
                'prêt-à-porter', 'vêtements', 'mode', 'accessoires',
                'chaussures', 'maroquinerie', 'bijoux', 'bijouterie',
                'librairie', 'livre', 'papeterie', 'jeux',
                'électronique', 'high-tech', 'téléphone', 'smartphone',
                'électroménager', 'mobilier', 'décoration maison',
                'jardinerie', 'animalerie', 'sport', 'outdoor',
            ),
            'transport' => array(
                'taxi', 'vtc', 'chauffeur', 'transport',
                'ambulance', 'vsl', 'déménagement',
                'livraison', 'coursier', 'logistique',
                'transitaire', 'douane',
            ),
        );
        $en = array(
            'marketing' => array(
                'advertising', 'media agency', 'digital marketing', 'web agency',
                'pr agency', 'public relations', 'copywriter', 'graphic designer',
                'ppc', 'paid search', 'content marketing', 'influencer marketing',
            ),
            'consultant' => array(
                'consulting firm', 'hr consulting', 'recruitment agency',
                'professional coaching', 'organizational development',
            ),
            'restaurant' => array(
                'diner', 'eatery', 'catering', 'bakery cafe', 'wine bar',
                'steakhouse', 'seafood', 'bbq', 'brunch spot',
            ),
            'boulangerie' => array(
                'bakery', 'pastry shop', 'patisserie', 'confectionery', 'cake shop',
            ),
            'médical' => array(
                'doctor', 'medical center', 'dental', 'physiotherapy', 'osteopathy',
                'pharmacy', 'psychology', 'psychiatry', 'optometry', 'veterinary',
                'hospital', 'clinic', 'midwife', 'dietitian', 'podiatry',
            ),
            'juridique' => array(
                'lawyer', 'law firm', 'notary', 'accounting', 'cpa', 'tax advisor',
                'bailiff', 'legal services',
            ),
            'immobilier' => array(
                'real estate', 'realtor', 'property management', 'property developer',
                'home builder', 'surveyor',
            ),
            'beauté' => array(
                'hair salon', 'beauty salon', 'nail salon', 'barbershop',
                'tattoo studio', 'wellness center', 'day spa',
            ),
            'fitness' => array(
                'gym', 'personal trainer', 'swimming pool', 'martial arts',
                'boxing gym', 'dance studio', 'sports club',
            ),
            'artisan' => array(
                'plumber', 'electrician', 'mason', 'painter', 'carpenter',
                'roofer', 'locksmith', 'hvac', 'landscaping', 'gardener',
                'moving company', 'cleaning service', 'chimney sweep',
            ),
            'auto' => array(
                'auto repair', 'body shop', 'car dealer', 'tire shop',
                'motorcycle', 'driving school', 'tow truck',
            ),
            'éducation' => array(
                'school', 'high school', 'college', 'university', 'training center',
                'tutoring', 'language school', 'daycare', 'preschool', 'driving school',
            ),
            'hôtellerie' => array(
                'lodging', 'bed and breakfast', 'hostel', 'resort', 'vacation rental',
                'guest house', 'motel',
            ),
            'événementiel' => array(
                'event planner', 'wedding planner', 'conference center', 'banquet hall',
                'photographer', 'videographer', 'florist', 'entertainment',
            ),
            'informatique' => array(
                'software development', 'it services', 'cybersecurity', 'managed services',
                'cloud computing', 'artificial intelligence', 'computer repair',
            ),
            'finance' => array(
                'bank', 'insurance', 'financial advisor', 'wealth management',
                'mortgage broker', 'investment', 'accounting firm',
            ),
            'retail' => array(
                'store', 'shop', 'clothing store', 'fashion', 'jewelry store',
                'bookstore', 'electronics store', 'furniture store', 'pet store',
            ),
            'transport' => array(
                'cab', 'rideshare', 'courier', 'freight', 'logistics', 'moving',
                'ambulance service',
            ),
        );
        $es = array(
            'marketing' => array(
                'publicidad', 'agencia de marketing', 'agencia digital', 'agencia web',
                'comunicación', 'redes sociales', 'diseño web', 'posicionamiento web',
                'community manager', 'marketing de contenidos',
            ),
            'consultant' => array(
                'consultoría', 'recursos humanos', 'reclutamiento', 'coaching',
                'transformación digital', 'gestión del cambio',
            ),
            'restaurant' => array(
                'restaurante', 'cafetería', 'bar', 'pizzería', 'comida rápida',
                'marisquería', 'asador', 'catering',
            ),
            'boulangerie' => array(
                'panadería', 'pastelería', 'repostería', 'confitería', 'pastel',
            ),
            'médical' => array(
                'médico', 'clínica', 'dentista', 'fisioterapia', 'osteopatía',
                'farmacia', 'psicólogo', 'psiquiatra', 'óptica', 'veterinario',
                'hospital', 'matrona', 'nutricionista',
            ),
            'juridique' => array(
                'abogado', 'despacho de abogados', 'notaría', 'contabilidad',
                'asesor fiscal', 'servicios legales',
            ),
            'immobilier' => array(
                'inmobiliaria', 'agente inmobiliario', 'promotora', 'arquitecto',
                'administración de fincas',
            ),
            'beauté' => array(
                'peluquería', 'salón de belleza', 'uñas', 'barbería',
                'tatuaje', 'spa', 'centro de bienestar',
            ),
            'fitness' => array(
                'gimnasio', 'entrenador personal', 'piscina', 'artes marciales',
                'boxeo', 'estudio de danza', 'club deportivo',
            ),
            'artisan' => array(
                'fontanero', 'electricista', 'albañil', 'pintor', 'carpintero',
                'cerrajero', 'climatización', 'jardinería', 'mudanzas', 'limpieza',
            ),
            'auto' => array(
                'taller mecánico', 'chapa y pintura', 'concesionario', 'neumáticos',
                'motocicleta', 'autoescuela', 'grúa',
            ),
            'éducation' => array(
                'escuela', 'instituto', 'universidad', 'formación', 'academia',
                'clases particulares', 'guardería', 'autoescuela', 'idiomas',
            ),
            'hôtellerie' => array(
                'hotel', 'hostal', 'alojamiento', 'casa rural', 'apartamento turístico',
                'camping', 'turismo',
            ),
            'événementiel' => array(
                'organizador de eventos', 'wedding planner', 'salón de eventos',
                'fotógrafo', 'videógrafo', 'floristería', 'animación',
            ),
            'informatique' => array(
                'informática', 'desarrollo de software', 'ciberseguridad',
                'servicios it', 'nube', 'inteligencia artificial', 'reparación pc',
            ),
            'finance' => array(
                'banco', 'seguros', 'asesor financiero', 'gestión patrimonial',
                'corredor de seguros', 'inversión', 'contabilidad',
            ),
            'retail' => array(
                'tienda', 'comercio', 'ropa', 'moda', 'joyería', 'librería',
                'electrónica', 'muebles', 'tienda de mascotas',
            ),
            'transport' => array(
                'taxi', 'vtc', 'mensajería', 'logística', 'mudanzas', 'ambulancia',
            ),
        );
        $merged = array();
        foreach ( $fr as $group_key => $words_fr ) {
            $words_en = isset( $en[ $group_key ] ) && is_array( $en[ $group_key ] ) ? $en[ $group_key ] : array();
            $words_es = isset( $es[ $group_key ] ) && is_array( $es[ $group_key ] ) ? $es[ $group_key ] : array();
            $merged[ $group_key ] = array_values( array_unique( array_merge( $words_fr, $words_en, $words_es ) ) );
        }
        return $merged;
    }

    /**
     * Détecte le groupe métier d'une catégorie GBP (sous-chaîne, insensible à la casse).
     *
     * @param string $cat_str
     * @param array|null $groups
     * @return string|null
     */
    private function find_maps_category_group( $cat_str, $groups = null ) {
        if ( $groups === null ) {
            $groups = $this->get_maps_category_groups();
        }
        $cat_str = mb_strtolower( trim( (string) $cat_str ) );
        if ( $cat_str === '' ) {
            return null;
        }
        foreach ( $groups as $group_key => $words ) {
            if ( ! is_array( $words ) ) {
                continue;
            }
            foreach ( $words as $word ) {
                $word = mb_strtolower( trim( (string) $word ) );
                if ( $word !== '' && mb_strpos( mb_strtolower( $cat_str ), mb_strtolower( $word ) ) !== false ) {
                    return $group_key;
                }
            }
        }
        return null;
    }

    /**
     * Keywords élargis DataForSEO pour secteurs niches (FR / EN / ES).
     *
     * @param string $group_key
     * @param string $city
     * @param string $lang fr|en|es
     * @return array<int, string>
     */
    private function get_maps_niche_expanded_keywords( $group_key, $city, $lang = 'fr' ) {
        $city = trim( (string) $city );
        if ( $city === '' || $group_key === '' ) {
            return array();
        }
        $lang = strtolower( trim( (string) $lang ) );
        if ( ! in_array( $lang, array( 'fr', 'en', 'es' ), true ) ) {
            $lang = 'fr';
        }
        $maps = array(
            'fr' => array(
                'marketing'    => array( 'agence marketing', 'agence communication', 'agence digitale', 'agence web' ),
                'consultant'   => array( 'cabinet conseil', 'consultant', 'coaching professionnel' ),
                'informatique' => array( 'agence informatique', 'développeur web', 'agence numérique' ),
                'finance'      => array( 'conseiller financier', 'cabinet comptable', 'courtier assurance' ),
                'éducation'    => array( 'centre de formation', 'cours particuliers', 'école privée' ),
                'événementiel' => array( 'organisateur événements', 'wedding planner', 'agence événementielle' ),
            ),
            'en' => array(
                'marketing'    => array( 'marketing agency', 'digital marketing agency', 'web agency', 'advertising agency' ),
                'consultant'   => array( 'consulting firm', 'business consultant', 'executive coaching' ),
                'informatique' => array( 'it agency', 'web developer', 'software company' ),
                'finance'      => array( 'financial advisor', 'accounting firm', 'insurance broker' ),
                'éducation'    => array( 'training center', 'private tutor', 'private school' ),
                'événementiel' => array( 'event planner', 'wedding planner', 'event agency' ),
            ),
            'es' => array(
                'marketing'    => array( 'agencia de marketing', 'agencia digital', 'agencia web', 'agencia de publicidad' ),
                'consultant'   => array( 'consultoría', 'consultor empresarial', 'coaching profesional' ),
                'informatique' => array( 'agencia informática', 'desarrollador web', 'empresa de software' ),
                'finance'      => array( 'asesor financiero', 'asesoría contable', 'corredor de seguros' ),
                'éducation'    => array( 'centro de formación', 'clases particulares', 'escuela privada' ),
                'événementiel' => array( 'organizador de eventos', 'wedding planner', 'agencia de eventos' ),
            ),
        );
        $niche_groups = array( 'marketing', 'consultant', 'informatique', 'finance', 'éducation', 'événementiel' );
        if ( ! in_array( $group_key, $niche_groups, true ) ) {
            return array();
        }
        $kw_base = isset( $maps[ $lang ][ $group_key ] ) ? $maps[ $lang ][ $group_key ] : ( isset( $maps['fr'][ $group_key ] ) ? $maps['fr'][ $group_key ] : array() );
        $out     = array();
        foreach ( $kw_base as $kw ) {
            $out[] = trim( (string) $kw ) . ' ' . $city;
        }
        return $out;
    }

    /**
     * Extrait les voisins les plus vus à partir des 20 résultats DataForSEO Maps.
     * Agrège les doublons par nom normalisé et trie par fréquence puis meilleur rang.
     *
     * @param array $maps_results Format ['items' => [...]]
     * @param string $business_name Nom établissement client pour exclusion.
     * @param int $limit Nombre max de leaders.
     * @param string $profile_category Catégorie profil pour filtre secteur (optionnel).
     * @return array<int, array<string, mixed>>
     */
    private function extract_most_seen_neighbors_from_maps_items( array $maps_results, $business_name = '', $limit = 3, $profile_category = '' ) {
        $items = isset( $maps_results['items'] ) && is_array( $maps_results['items'] ) ? $maps_results['items'] : [];
        if ( empty( $items ) ) {
            return [];
        }
        $limit = max( 1, (int) $limit );
        $business_lower = $business_name !== '' ? mb_strtolower( trim( (string) $business_name ) ) : '';
        $bucket         = array();

        $category_groups = $this->get_maps_category_groups();

        $find_category_group = function ( $cat_str, $groups ) {
            return $this->find_maps_category_group( $cat_str, $groups );
        };

        $profile_group = $profile_category !== '' ? $find_category_group( $profile_category, $category_groups ) : null;

        foreach ( array_slice( $items, 0, 20 ) as $idx => $item ) {
            $title = isset( $item['title'] ) ? trim( (string) $item['title'] ) : ( isset( $item['original_title'] ) ? trim( (string) $item['original_title'] ) : '' );
            if ( $title === '' ) {
                continue;
            }
            $title_lower = mb_strtolower( $title );
            if ( $business_lower !== '' && ( mb_strpos( $title_lower, $business_lower ) !== false || mb_strpos( $business_lower, $title_lower ) !== false ) ) {
                continue;
            }

            $norm = preg_replace( '/\s+/', ' ', preg_replace( '/[^\p{L}\p{N}\s]/u', ' ', $title_lower ) );
            $norm = trim( (string) $norm );
            if ( $norm === '' ) {
                $norm = $title_lower;
            }

            $rank = isset( $item['rank_absolute'] ) ? (int) $item['rank_absolute'] : ( $idx + 1 );
            $place_id = isset( $item['place_id'] ) ? (string) $item['place_id'] : null;
            $cat = isset( $item['category'] ) ? (string) $item['category'] : null;
            // Filtre catégorie — groupes métier GBP (même famille uniquement)
            if ( $profile_category !== '' ) {
                $item_group = ( $cat !== null && $cat !== '' ) ? $find_category_group( $cat, $category_groups ) : null;
                if ( $profile_group !== null && $item_group !== null ) {
                    if ( $profile_group !== $item_group ) {
                        continue;
                    }
                } elseif ( $profile_group !== null && $item_group === null ) {
                    continue;
                }
                // Si profile_group non résolu mais item appartient à un secteur générique (retail, admin)
                // → exclure uniquement si fallback '' n'est pas actif
                if ( $profile_category !== '' && $profile_group === null ) {
                    $generic_exclude = array( 'retail', 'transport', 'nonprofit' );
                    if ( $item_group !== null && in_array( $item_group, $generic_exclude, true ) ) {
                        continue;
                    }
                }
            }
            $rating = null;
            $rating_raw = isset( $item['rating'] ) ? $item['rating'] : null;
            if ( is_array( $rating_raw ) && isset( $rating_raw['value'] ) ) {
                $rating = (float) $rating_raw['value'];
            } elseif ( isset( $item['rating_value'] ) ) {
                $rating = (float) $item['rating_value'];
            } elseif ( $rating_raw !== null && ! is_array( $rating_raw ) ) {
                $rating = (float) $rating_raw;
            }
            $reviews_count = null;
            if ( is_array( $rating_raw ) && isset( $rating_raw['votes_count'] ) ) {
                $reviews_count = (int) $rating_raw['votes_count'];
            } elseif ( isset( $item['reviews_count'] ) ) {
                $reviews_count = (int) $item['reviews_count'];
            } elseif ( isset( $item['total_reviews'] ) ) {
                $reviews_count = (int) $item['total_reviews'];
            }

            if ( ! isset( $bucket[ $norm ] ) ) {
                $bucket[ $norm ] = [
                    'competitor_name' => strlen( $title ) > 70 ? ( substr( $title, 0, 67 ) . '...' ) : $title,
                    'google_place_id' => $place_id,
                    'main_category'   => $cat,
                    'current_rank'    => $rank,
                    'rating'          => $rating,
                    'reviews_count'   => $reviews_count,
                    '_freq'           => 0,
                    '_best_rank'      => $rank,
                ];
            }

            $bucket[ $norm ]['_freq'] = (int) $bucket[ $norm ]['_freq'] + 1;
            if ( $rank > 0 && $rank < (int) $bucket[ $norm ]['_best_rank'] ) {
                $bucket[ $norm ]['_best_rank'] = $rank;
                $bucket[ $norm ]['current_rank'] = $rank;
            }
            if ( $rating !== null && ( $bucket[ $norm ]['rating'] === null || $rating > (float) $bucket[ $norm ]['rating'] ) ) {
                $bucket[ $norm ]['rating'] = $rating;
            }
            if ( $reviews_count !== null && ( $bucket[ $norm ]['reviews_count'] === null || $reviews_count > (int) $bucket[ $norm ]['reviews_count'] ) ) {
                $bucket[ $norm ]['reviews_count'] = $reviews_count;
            }
        }

        if ( empty( $bucket ) ) {
            return [];
        }

        $leaders = array_values( $bucket );
        usort( $leaders, function ( $a, $b ) {
            $freq_a = isset( $a['_freq'] ) ? (int) $a['_freq'] : 0;
            $freq_b = isset( $b['_freq'] ) ? (int) $b['_freq'] : 0;
            if ( $freq_a !== $freq_b ) {
                return $freq_b <=> $freq_a;
            }
            $rank_a = isset( $a['_best_rank'] ) ? (int) $a['_best_rank'] : 999;
            $rank_b = isset( $b['_best_rank'] ) ? (int) $b['_best_rank'] : 999;
            if ( $rank_a !== $rank_b ) {
                return $rank_a <=> $rank_b;
            }
            $rev_a = isset( $a['reviews_count'] ) && $a['reviews_count'] !== null ? (int) $a['reviews_count'] : -1;
            $rev_b = isset( $b['reviews_count'] ) && $b['reviews_count'] !== null ? (int) $b['reviews_count'] : -1;
            return $rev_b <=> $rev_a;
        } );

        $leaders = array_slice( $leaders, 0, $limit );
        foreach ( $leaders as &$leader ) {
            unset( $leader['_freq'], $leader['_best_rank'] );
        }
        unset( $leader );

        return $leaders;
    }

    /**
     * Tuyau KPI — Compétiteurs (leaders secteur) via DataForSEO Maps live + extraction voisins.
     * N’écrit pas en BDD ; prêt à injecter dans le dashboard (ex. carte « Leaders »).
     *
     * @param int    $profile_id     Contexte logs + propagation DFS.
     * @param string $sector_keyword Ex. « Plombier Paris ».
     * @param string $target_city    Ville (location_code DataForSEO).
     * @param string $country        FR|GB|US|ES.
     * @param string $business_name  Nom client (exclusion du classement concurrents).
     * @param int    $limit          Nombre max de leaders.
     * @param string $category       Catégorie profil (filtre secteur concurrents).
     * @return array{leaders: array<int, array>, maps_items_count: int, wp_error: string|null}
     */
    public function dfs_fetch_competitors_leaders_for_kpi( $profile_id, $sector_keyword, $target_city, $country, $business_name, $limit = 3, $category = '' ) {
        $profile_id     = (int) $profile_id;
        $sector_keyword = trim( (string) $sector_keyword );
        $target_city    = trim( (string) $target_city );
        $country        = strtoupper( trim( (string) $country ) );
        if ( ! in_array( $country, [ 'FR', 'GB', 'US', 'ES' ], true ) ) {
            $country = 'FR';
        }
        $business_name = trim( (string) $business_name );
        $limit         = max( 1, min( 20, (int) $limit ) );


        $out = [
            'leaders'          => [],
            'maps_items_count' => 0,
            'wp_error'         => null,
        ];

        if ( $sector_keyword === '' || $target_city === '' ) {
            return $out;
        }

        $maps = $this->fetch_dataforseo_maps_sector( $sector_keyword, $target_city, $country, $profile_id );
        if ( is_wp_error( $maps ) ) {
            $out['wp_error'] = $maps->get_error_message();
            return $out;
        }

        $items                   = isset( $maps['items'] ) && is_array( $maps['items'] ) ? $maps['items'] : [];
        $out['maps_items_count'] = count( $items );

        $category = trim( (string) $category );
        $out['leaders'] = $this->extract_most_seen_neighbors_from_maps_items( $maps, $business_name, $limit, $category );

        return $out;
    }

    /**
     * Stub — « Your search keywords » : suggestions DFS Labs (keyword_suggestions/live).
     * À brancher sur le même contrat que le REST thème (avision_rest_keyword_suggestions) ou POST direct ici.
     *
     * @return array{suggestions: array<int, string>}
     */
    public function dfs_stub_fetch_your_search_keyword_suggestions_for_kpi( $seed_keyword, $location_name, $location_code, $language_code, $profile_id = 0 ) {
        $profile_id = (int) $profile_id;
        return [ 'suggestions' => [] ];
    }

    /**
     * Tuyau KPI — Growth Radar : lignes mots-clés suivis (positions alimentées par les scans DFS).
     * Lecture BDD uniquement ; prêt pour injection KPI (grille Radar, compteur, etc.).
     *
     * @param int $profile_id avi_saas_profiles.id
     * @param int $limit      Max lignes
     * @return array{rows: array<int, array<string, mixed>>, count: int}
     */
    public function dfs_fetch_growth_radar_keyword_rows_for_kpi( $profile_id, $limit = 100 ) {
        $profile_id = (int) $profile_id;
        $limit      = max( 1, min( 500, (int) $limit ) );


        $out = [ 'rows' => [], 'count' => 0 ];

        global $wpdb;
        if ( ! isset( $wpdb ) || ! $wpdb || $profile_id <= 0 ) {
            return $out;
        }

        $table = $this->table( 'avi_saas_keywords' );
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
            return $out;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table validé SHOW TABLES
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM `{$table}` WHERE profile_id = %d ORDER BY is_strategic DESC, id ASC LIMIT %d",
                $profile_id,
                $limit
            ),
            ARRAY_A
        );

        if ( ! is_array( $rows ) ) {
            $rows = [];
        }
        $out['rows']  = $rows;
        $out['count'] = count( $rows );

        return $out;
    }

    public function get_audit_by_link($link) {
        if (!$this->pdo) return null;

        $stmt = $this->pdo->prepare("
            SELECT a.*, p.* 
            FROM avision_audits a
            LEFT JOIN avision_user_progress p ON a.id = p.audit_id
            WHERE a.audit_link = :link
        ");
        $stmt->execute([':link' => $link]);
        return $stmt->fetch();
    }

    public function get_audit_by_email($email) {
        if (!$this->pdo) return null;

        $stmt = $this->pdo->prepare("
            SELECT a.*, p.* 
            FROM avision_audits a
            LEFT JOIN avision_user_progress p ON a.id = p.audit_id
            WHERE a.email = :email
            ORDER BY a.created_at DESC
            LIMIT 1
        ");
        $stmt->execute([':email' => $email]);
        return $stmt->fetch();
    }

    public function update_progress($audit_id, $data) {
        if (!$this->pdo) return false;

        $fields = [];
        $params = [':audit_id' => $audit_id];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = :{$key}";
            $params[":{$key}"] = $value;
        }
        
        $fields[] = "updated_at = CURRENT_TIMESTAMP";
        
        $stmt = $this->pdo->prepare("
            UPDATE avision_user_progress 
            SET " . implode(', ', $fields) . "
            WHERE audit_id = :audit_id
        ");
        
        return $stmt->execute($params);
    }

    public function update_score($audit_id, $score) {
        if (!$this->pdo) return false;

        $stmt = $this->pdo->prepare("
            UPDATE avision_audits 
            SET score = :score, updated_at = CURRENT_TIMESTAMP
            WHERE id = :audit_id
        ");
        
        return $stmt->execute([':audit_id' => $audit_id, ':score' => $score]);
    }
    
    // ========================================
    // MÉTHODES MODE GARDIEN
    // ========================================
    
    /**
     * Active le Mode Gardien pour un audit
     */
    public function activate_guardian_mode($audit_id, $settings) {
        if (!$this->pdo) return false;
        
        $stmt = $this->pdo->prepare("
            INSERT INTO avision_guardian_settings 
            (audit_id, is_active, auto_respond, response_delay, tone_of_voice, 
             include_seo, notification_email, languages, google_location_id, google_access_token)
            VALUES (:audit_id, true, :auto_respond, :response_delay, :tone_of_voice,
                    :include_seo, :notification_email, :languages, :google_location_id, :google_access_token)
            ON CONFLICT (audit_id) 
            DO UPDATE SET 
                is_active = true,
                auto_respond = :auto_respond,
                response_delay = :response_delay,
                tone_of_voice = :tone_of_voice,
                include_seo = :include_seo,
                notification_email = :notification_email,
                languages = :languages,
                google_location_id = :google_location_id,
                google_access_token = :google_access_token,
                updated_at = CURRENT_TIMESTAMP
        ");
        
        return $stmt->execute([
            ':audit_id' => $audit_id,
            ':auto_respond' => $settings['auto_respond'] ?? true,
            ':response_delay' => $settings['response_delay'] ?? 60,
            ':tone_of_voice' => $settings['tone_of_voice'] ?? 'professional',
            ':include_seo' => $settings['include_seo'] ?? true,
            ':notification_email' => $settings['notification_email'] ?? true,
            ':languages' => implode(',', $settings['languages'] ?? ['fr', 'en']),
            ':google_location_id' => $settings['google_location_id'] ?? null,
            ':google_access_token' => $settings['google_access_token'] ?? null
        ]);
    }
    
    /**
     * Désactive le Mode Gardien
     */
    public function deactivate_guardian_mode($audit_id) {
        if (!$this->pdo) return false;
        
        $stmt = $this->pdo->prepare("
            UPDATE avision_guardian_settings 
            SET is_active = false, updated_at = CURRENT_TIMESTAMP
            WHERE audit_id = :audit_id
        ");
        
        return $stmt->execute([':audit_id' => $audit_id]);
    }
    
    /**
     * Récupère les paramètres du Mode Gardien
     */
    public function get_guardian_settings($audit_id) {
        if (!$this->pdo) return null;
        
        $stmt = $this->pdo->prepare("
            SELECT * FROM avision_guardian_settings 
            WHERE audit_id = :audit_id
        ");
        $stmt->execute([':audit_id' => $audit_id]);
        return $stmt->fetch();
    }
    
    /**
     * Enregistre un avis et sa réponse
     */
    public function save_review_response($data) {
        if (!$this->pdo) return false;
        
        try {
            $this->pdo->beginTransaction();
            
            // Insérer l'avis et la réponse
            $stmt = $this->pdo->prepare("
                INSERT INTO avision_reviews 
                (audit_id, review_id, reviewer_name, rating, review_text, review_language,
                 review_created_at, context_data, response_text, response_published, 
                 response_published_at, response_time_seconds)
                VALUES (:audit_id, :review_id, :reviewer_name, :rating, :review_text, :review_language,
                        :review_created_at, :context_data, :response_text, :response_published,
                        :response_published_at, :response_time_seconds)
                ON CONFLICT (review_id) DO UPDATE SET
                    response_text = :response_text,
                    response_published = :response_published,
                    response_published_at = :response_published_at
            ");
            
            $stmt->execute([
                ':audit_id' => $data['audit_id'],
                ':review_id' => $data['review_id'],
                ':reviewer_name' => $data['reviewer_name'] ?? 'Anonymous',
                ':rating' => $data['stars'],
                ':review_text' => $data['review_text'],
                ':review_language' => $data['language'] ?? 'fr',
                ':review_created_at' => $data['review_created_at'] ?? date('Y-m-d H:i:s'),
                ':context_data' => $data['context'] ?? '{}',
                ':response_text' => $data['response_text'],
                ':response_published' => $data['published'] ?? false,
                ':response_published_at' => $data['published'] ? date('Y-m-d H:i:s') : null,
                ':response_time_seconds' => $data['response_time'] ?? 0
            ]);
            
            // Mettre à jour les statistiques
            $this->update_guardian_statistics($data['audit_id'], $data['stars']);
            
            $this->pdo->commit();
            return true;
            
        } catch (\PDOException $e) {
            $this->pdo->rollBack();
            error_log('Save review error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Met à jour les statistiques du Mode Gardien
     */
    private function update_guardian_statistics($audit_id, $stars) {
        $star_column = "reviews_{$stars}_star" . ($stars > 1 ? 's' : '');
        
        $stmt = $this->pdo->prepare("
            INSERT INTO avision_guardian_stats 
            (audit_id, total_reviews, total_responses, {$star_column}, last_review_at)
            VALUES (:audit_id, 1, 1, 1, CURRENT_TIMESTAMP)
            ON CONFLICT (audit_id) 
            DO UPDATE SET
                total_reviews = avision_guardian_stats.total_reviews + 1,
                total_responses = avision_guardian_stats.total_responses + 1,
                {$star_column} = avision_guardian_stats.{$star_column} + 1,
                last_review_at = CURRENT_TIMESTAMP,
                updated_at = CURRENT_TIMESTAMP
        ");
        
        return $stmt->execute([':audit_id' => $audit_id]);
    }
    
    /**
     * Récupère les statistiques du Mode Gardien
     */
    public function get_guardian_statistics($audit_id) {
        if (!$this->pdo) return null;
        
        $stmt = $this->pdo->prepare("
            SELECT 
                s.*,
                (SELECT AVG(response_time_seconds) / 60 
                 FROM avision_reviews 
                 WHERE audit_id = :audit_id AND response_published = true) as avg_response_time_minutes
            FROM avision_guardian_stats s
            WHERE s.audit_id = :audit_id
        ");
        
        $stmt->execute([':audit_id' => $audit_id]);
        $stats = $stmt->fetch();
        
        if (!$stats) {
            return [
                'total_reviews' => 0,
                'total_responses' => 0,
                'avg_response_time_minutes' => 0,
                'reviews_5_stars' => 0,
                'reviews_4_stars' => 0,
                'reviews_3_stars' => 0,
                'reviews_2_stars' => 0,
                'reviews_1_star' => 0
            ];
        }
        
        return $stats;
    }
    
    /**
     * Récupère l'audit par ID
     */
    public function get_audit_by_id($audit_id) {
        if (!$this->pdo) return null;
        
        $stmt = $this->pdo->prepare("
            SELECT * FROM avision_audits WHERE id = :id
        ");
        $stmt->execute([':id' => $audit_id]);
        return $stmt->fetch();
    }
    
    /**
     * Récupère les avis récents d'un audit
     */
    public function get_recent_reviews($audit_id, $limit = 10) {
        if (!$this->pdo) return [];
        
        $stmt = $this->pdo->prepare("
            SELECT * FROM avision_reviews 
            WHERE audit_id = :audit_id 
            ORDER BY review_created_at DESC 
            LIMIT :limit
        ");
        
        $stmt->execute([':audit_id' => $audit_id, ':limit' => (int) $limit]);
        return $stmt->fetchAll();
    }
    
    /**
     * Récupère les avis en attente de validation (réponse générée mais non publiée)
     */
    public function get_pending_reviews($audit_id) {
        if (!$this->pdo) return [];
        
        $stmt = $this->pdo->prepare("
            SELECT * FROM avision_reviews 
            WHERE audit_id = :audit_id 
              AND response_text IS NOT NULL 
              AND response_text != ''
              AND (response_published = false OR response_published IS NULL)
            ORDER BY received_at DESC
        ");
        
        $stmt->execute([':audit_id' => $audit_id]);
        return $stmt->fetchAll();
    }
    
    /**
     * Marque un avis comme publié
     */
    public function mark_review_published($review_id, $response_text) {
        if (!$this->pdo) return false;
        
        $stmt = $this->pdo->prepare("
            UPDATE avision_reviews 
            SET response_published = true, 
                response_published_at = CURRENT_TIMESTAMP,
                response_text = :response_text
            WHERE review_id = :review_id
        ");
        
        return $stmt->execute([
            ':review_id' => $review_id,
            ':response_text' => $response_text
        ]);
    }
    
    /**
     * Met à jour la réponse d'un avis en attente de validation
     */
    public function update_pending_review_response($review_id, $response_text, $context_json = '{}') {
        if (!$this->pdo) return false;
        
        $stmt = $this->pdo->prepare("
            UPDATE avision_reviews 
            SET response_text = :response_text,
                context_data = :context_data
            WHERE review_id = :review_id
        ");
        
        return $stmt->execute([
            ':review_id' => $review_id,
            ':response_text' => $response_text,
            ':context_data' => $context_json
        ]);
    }
    
    /**
     * Compte les avis en attente depuis plus de 24h
     */
    public function get_pending_reviews_older_than_24h($audit_id) {
        if (!$this->pdo) return [];
        
        $stmt = $this->pdo->prepare("
            SELECT * FROM avision_reviews 
            WHERE audit_id = :audit_id 
              AND response_text IS NOT NULL 
              AND response_text != ''
              AND (response_published = false OR response_published IS NULL)
              AND received_at < NOW() - INTERVAL '24 hours'
            ORDER BY received_at ASC
        ");
        
        $stmt->execute([':audit_id' => $audit_id]);
        return $stmt->fetchAll();
    }
    
    // ========================================
    // SYSTÈME DE NOTIFICATIONS AVI
    // ========================================
    
    /**
     * Audits éligibles au rapport hebdomadaire (Mode Gardien actif)
     */
    public function get_audits_for_weekly_report() {
        if (!$this->pdo) return [];
        
        $stmt = $this->pdo->query("
            SELECT a.id, a.email, a.business_name, a.audit_link, a.language
            FROM avision_audits a
            JOIN avision_guardian_settings g ON g.audit_id = a.id
            WHERE g.is_active = true
            AND (a.last_weekly_report_sent IS NULL 
                 OR a.last_weekly_report_sent < CURRENT_DATE - INTERVAL '6 days')
        ");
        return $stmt ? $stmt->fetchAll() : [];
    }
    
    /**
     * Audits éligibles au rapport mensuel (30j après inscription)
     */
    public function get_audits_for_monthly_report() {
        if (!$this->pdo) return [];
        
        $stmt = $this->pdo->query("
            SELECT a.id, a.email, a.business_name, a.audit_link, a.score, 
                   a.score_at_day_1, a.created_at, a.language
            FROM avision_audits a
            WHERE a.created_at <= CURRENT_TIMESTAMP - INTERVAL '30 days'
            AND (a.last_monthly_report_sent IS NULL 
                 OR a.last_monthly_report_sent < CURRENT_DATE - INTERVAL '28 days')
        ");
        return $stmt ? $stmt->fetchAll() : [];
    }
    
    /**
     * Compte les actions complétées sur la semaine (avis répondus + posts)
     */
    public function get_completed_actions_this_week($audit_id) {
        if (!$this->pdo) return ['reviews' => 0, 'posts' => 0];
        
        $reviews = 0;
        $posts = 0;
        
        try {
            $stmt = $this->pdo->prepare("
                SELECT COUNT(*) as cnt FROM avision_reviews 
                WHERE audit_id = :aid AND response_published = true 
                AND (response_published_at >= CURRENT_DATE - INTERVAL '7 days'
                     OR received_at >= CURRENT_DATE - INTERVAL '7 days')
            ");
            $stmt->execute([':aid' => $audit_id]);
            $row = $stmt->fetch();
            $reviews = (int)($row['cnt'] ?? 0);
        } catch (\PDOException $e) {
            error_log('get_completed_actions reviews: ' . $e->getMessage());
        }
        
        try {
            $stmt = $this->pdo->prepare("
                SELECT COUNT(*) as cnt FROM avision_guardian_posts 
                WHERE audit_id = :aid AND published = true 
                AND published_at >= CURRENT_DATE - INTERVAL '7 days'
            ");
            $stmt->execute([':aid' => $audit_id]);
            $row = $stmt->fetch();
            $posts = (int)($row['cnt'] ?? 0);
        } catch (\PDOException $e) {
            error_log('get_completed_actions posts: ' . $e->getMessage());
        }
        
        return ['reviews' => $reviews, 'posts' => $posts];
    }
    
    /**
     * Marque le dernier rapport hebdomadaire envoyé
     */
    public function update_last_weekly_report_sent($audit_id) {
        if (!$this->pdo) return false;
        $stmt = $this->pdo->prepare("
            UPDATE avision_audits SET last_weekly_report_sent = CURRENT_TIMESTAMP WHERE id = :aid
        ");
        return $stmt->execute([':aid' => $audit_id]);
    }
    
    /**
     * Marque le dernier rapport mensuel envoyé
     */
    public function update_last_monthly_report_sent($audit_id) {
        if (!$this->pdo) return false;
        $stmt = $this->pdo->prepare("
            UPDATE avision_audits SET last_monthly_report_sent = CURRENT_TIMESTAMP WHERE id = :aid
        ");
        return $stmt->execute([':aid' => $audit_id]);
    }
    
    /**
     * Met à jour score_at_day_1 (appelé à la création si pas encore défini)
     */
    public function set_score_at_day_1($audit_id, $score) {
        if (!$this->pdo) return false;
        $stmt = $this->pdo->prepare("
            UPDATE avision_audits SET score_at_day_1 = :score WHERE id = :aid
        ");
        return $stmt->execute([':aid' => $audit_id, ':score' => $score]);
    }

    /**
     * Insère un point dans l'historique Radar (avi_saas_rank_history) pour le graphique.
     *
     * @param int $profile_id
     * @param string|null $date Date du snapshot (Y-m-d H:i:s) ou null = now
     * @param float|null $avg_position Position moyenne
     * @param int $top3_count Nombre en Top 3
     * @param int $top10_count Nombre en Top 10
     * @param float|null $net_worth_value Valeur Net Worth
     * @param int|null $score Score de visibilité (30-42) au moment du snapshot
     * @return bool
     */
    public function insert_rank_history_row( $profile_id, $date = null, $avg_position = null, $top3_count = 0, $top10_count = 0, $net_worth_value = null, $score = null ) {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 ) {
            return false;
        }
        $date = $date ?: gmdate( 'Y-m-d H:i:s' );
        if ( $this->pdo ) {
            try {
                $stmt = $this->pdo->prepare(
                    'INSERT INTO ' . $this->table( 'avi_saas_rank_history' ) . ' (profile_id, date, avg_position, top3_count, top10_count, net_worth_value, score) VALUES (:pid, :date, :avg_pos, :top3, :top10, :net_worth, :score)'
                );
                return $stmt->execute(
                    array(
                        ':pid'       => $profile_id,
                        ':date'      => $date,
                        ':avg_pos'   => $avg_position,
                        ':top3'      => (int) $top3_count,
                        ':top10'     => (int) $top10_count,
                        ':net_worth' => $net_worth_value,
                        ':score'     => $score !== null ? (int) $score : null,
                    )
                );
            } catch ( \PDOException $e ) {
                error_log( '[AVI] insert_rank_history_row PDO failed profile_id=' . $profile_id . ' ' . $e->getMessage() );
                return false;
            }
        }
        // ANTI-RÉGRESSION [02 juin 2026] — Fallback $wpdb si PDO indisponible
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            global $wpdb;
            if ( isset( $wpdb ) && $wpdb instanceof \wpdb ) {
                $this->wpdb = $wpdb;
            }
        }
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            error_log( '[AVI] insert_rank_history_row: no PDO and no wpdb profile_id=' . $profile_id );
            return false;
        }
        $inserted = $this->wpdb->insert(
            $this->table( 'avi_saas_rank_history' ),
            array(
                'profile_id'      => $profile_id,
                'date'            => $date,
                'avg_position'    => $avg_position,
                'top3_count'      => (int) $top3_count,
                'top10_count'     => (int) $top10_count,
                'net_worth_value' => $net_worth_value,
                'score'           => $score !== null ? (int) $score : null,
            ),
            array( '%d', '%s', '%f', '%d', '%d', '%f', '%d' )
        );
        return $inserted !== false;
    }

    /**
     * Passe is_first_sync à 0 pour le profil (premier sync terminé).
     *
     * @param int $profile_id
     * @return bool
     */
    public function set_profile_first_sync_done( $profile_id ) {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 ) {
            return false;
        }
        if ( $this->pdo ) {
            try {
                $stmt = $this->pdo->prepare( 'UPDATE ' . $this->table( 'avi_saas_profiles' ) . ' SET is_first_sync = 0 WHERE id = :id' );
                return $stmt->execute( array( ':id' => $profile_id ) );
            } catch ( \PDOException $e ) {
                error_log( '[AVI] set_profile_first_sync_done PDO failed profile_id=' . $profile_id . ' ' . $e->getMessage() );
                return false;
            }
        }
        // ANTI-RÉGRESSION [02 juin 2026] — Fallback $wpdb si PDO indisponible
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            global $wpdb;
            if ( isset( $wpdb ) && $wpdb instanceof \wpdb ) {
                $this->wpdb = $wpdb;
            }
        }
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            error_log( '[AVI] set_profile_first_sync_done: no PDO and no wpdb profile_id=' . $profile_id );
            return false;
        }
        $updated = $this->wpdb->update(
            $this->table( 'avi_saas_profiles' ),
            array( 'is_first_sync' => 0 ),
            array( 'id' => $profile_id ),
            array( '%d' ),
            array( '%d' )
        );
        return $updated !== false;
    }

    /**
     * Met à jour le score de visibilité du profil.
     *
     * @param int $profile_id
     * @param int $score
     * @return bool
     */
    public function update_profile_score( $profile_id, $score ) {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 ) {
            return false;
        }
        $score = max( 0, min( 100, (int) $score ) );
        // PDO en priorité si disponible (backend PostgreSQL dédié)
        if ( $this->pdo ) {
            try {
                $stmt = $this->pdo->prepare(
                    'UPDATE ' . $this->table( 'avi_saas_profiles' ) . ' SET score = :score WHERE id = :id'
                );
                return $stmt->execute( array( ':score' => $score, ':id' => $profile_id ) );
            } catch ( \PDOException $e ) {
                error_log( '[AVI] update_profile_score PDO failed profile_id=' . $profile_id . ' ' . $e->getMessage() );
                return false;
            }
        }
        // ANTI-RÉGRESSION [02 juin 2026] — Fallback $wpdb WordPress si PDO indisponible
        // DATABASE_URL absent en contexte WP → PDO null → score bloqué à 30 sans ce fallback
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            global $wpdb;
            if ( isset( $wpdb ) && $wpdb instanceof \wpdb ) {
                $this->wpdb = $wpdb;
            }
        }
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            error_log( '[AVI] update_profile_score: no PDO and no wpdb profile_id=' . $profile_id );
            return false;
        }
        $updated = $this->wpdb->update(
            $this->table( 'avi_saas_profiles' ),
            array( 'score' => $score ),
            array( 'id'    => $profile_id ),
            array( '%d' ),
            array( '%d' )
        );
        if ( $updated !== false ) {
            error_log( '[AVI] update_profile_score wpdb OK profile_id=' . $profile_id . ' score=' . $score );
        }
        return $updated !== false;
    }

    /**
     * Récupère le profil avec last_metrics_snapshot (pour calcul score / net worth).
     *
     * @param int $profile_id
     * @return array|null id, business_name, city, category, last_metrics_snapshot (décodé en array si JSON)
     */
    public function get_saas_profile_with_snapshot( $profile_id ) {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 ) {
            return null;
        }
        if ( $this->pdo ) {
            try {
                $stmt = $this->pdo->prepare(
                    'SELECT id, business_name, city, COALESCE(neighborhood, \'\') AS neighborhood, category, last_metrics_snapshot FROM ' . $this->table( 'avi_saas_profiles' ) . ' WHERE id = :id LIMIT 1'
                );
                $stmt->execute( array( ':id' => $profile_id ) );
                $row = $stmt->fetch( \PDO::FETCH_ASSOC );
                if ( ! $row ) {
                    return null;
                }
                if ( ! empty( $row['last_metrics_snapshot'] ) ) {
                    $decoded = json_decode( $row['last_metrics_snapshot'], true );
                    $row['snapshot'] = is_array( $decoded ) ? $decoded : array();
                } else {
                    $row['snapshot'] = array();
                }
                return $row;
            } catch ( \PDOException $e ) {
                error_log( '[AVI] get_saas_profile_with_snapshot PDO failed profile_id=' . $profile_id . ' ' . $e->getMessage() );
            }
        }
        // ANTI-RÉGRESSION [02 juin 2026] — Fallback $wpdb si PDO indisponible
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            global $wpdb;
            if ( isset( $wpdb ) && $wpdb instanceof \wpdb ) {
                $this->wpdb = $wpdb;
            }
        }
        if ( ! ( $this->wpdb instanceof \wpdb ) ) {
            error_log( '[AVI] get_saas_profile_with_snapshot: no PDO and no wpdb profile_id=' . $profile_id );
            return null;
        }
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                'SELECT id, business_name, city, COALESCE(neighborhood, \'\') AS neighborhood, category, last_metrics_snapshot FROM ' . $this->table( 'avi_saas_profiles' ) . ' WHERE id = %d LIMIT 1',
                $profile_id
            ),
            ARRAY_A
        );
        if ( ! $row ) {
            return null;
        }
        if ( ! empty( $row['last_metrics_snapshot'] ) ) {
            $decoded = json_decode( $row['last_metrics_snapshot'], true );
            $row['snapshot'] = is_array( $decoded ) ? $decoded : array();
        } else {
            $row['snapshot'] = array();
        }
        return $row;
    }

    /**
     * Récupère le profil avec les champs nécessaires pour la synchronisation initiale (First Sync).
     * Requête simple alignée sur le script debug (même base et préfixe que $wpdb).
     *
     * @param int $profile_id
     * @return array|null ligne complète du profil ou null
     */
    public function get_saas_profile_for_sync($profile_id) {
        $profile_id = (int) $profile_id;
        if ($profile_id <= 0) {
            return null;
        }
        global $wpdb;
        $table = $this->table('avi_saas_profiles');
        $profile = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table WHERE id = %d",
            $profile_id
        ), ARRAY_A);
        if ($profile) {
            error_log('[AVI] Profile Fetch Success for ID: ' . $profile_id);
        }
        return $profile ?: null;
    }

    /**
     * Met à jour last_metrics_snapshot du profil (JSON : note, avis, site, téléphone, photos, position).
     *
     * @param int $profile_id
     * @param string $json_snapshot
     * @return bool
     */
    public function update_profile_last_metrics_snapshot( $profile_id, $json_snapshot ) {
        // ANTI-RÉGRESSION #189 [23 mai 2026] — wpdb au lieu de PDO
        // ANTI-RÉGRESSION #190 [23 mai 2026] — Protection snapshot contre écrasement null
        global $wpdb;
        if ( (int) $profile_id <= 0 || ! isset( $wpdb ) ) {
            return false;
        }

        $new = json_decode( $json_snapshot, true );
        if ( ! is_array( $new ) ) {
            return false;
        }

        // Garde absolue : si nouveau rating null/0 → conserver la valeur existante
        if ( empty( $new['rating'] ) || (float) $new['rating'] <= 0 ) {
            $existing_json = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT last_metrics_snapshot FROM {$wpdb->prefix}avi_saas_profiles WHERE id=%d",
                    (int) $profile_id
                )
            );
            if ( $existing_json ) {
                $existing = json_decode( $existing_json, true );
                if ( is_array( $existing ) && ! empty( $existing['rating'] ) && (float) $existing['rating'] > 0 ) {
                    $new['rating']       = $existing['rating'];
                    $new['review_count'] = isset( $existing['review_count'] ) && (int) $existing['review_count'] > 0
                        ? $existing['review_count']
                        : ( $new['review_count'] ?? null );
                    $json_snapshot = json_encode( $new );
                    error_log( '[AVI] #190 Snapshot — conservation rating=' . $existing['rating'] . ' profil ' . $profile_id );
                }
            }
        }

        $rows = $wpdb->update(
            $this->table( 'avi_saas_profiles' ),
            array( 'last_metrics_snapshot' => (string) $json_snapshot ),
            array( 'id' => (int) $profile_id ),
            array( '%s' ),
            array( '%d' )
        );
        return $rows !== false;
    }

    /**
     * Insère ou remplace les 3 leaders secteur pour un profil (avi_saas_competitors).
     * Utilise $wpdb avec le préfixe WordPress (ex. wor1515_) pour rester cohérent avec le thème.
     *
     * @param int $profile_id
     * @param array[] $leaders [ ['competitor_name' =>, 'google_place_id' =>, 'main_category' =>, 'current_rank' =>, 'rating' =>, 'reviews_count' =>], ... ]
     * @return bool
     */
    public function replace_profile_competitors($profile_id, array $leaders) {
        if ((int) $profile_id <= 0) return false;
        $profile_id = (int) $profile_id;
        global $wpdb;
        if (!isset($wpdb) || !$wpdb) {
            return false;
        }
        $table = $this->table( 'avi_saas_competitors' );
        $wpdb->delete($table, ['profile_id' => $profile_id], ['%d']);
        $now = gmdate('Y-m-d H:i:s');
        $insert_count = 0;
        foreach ($leaders as $i => $row) {
            $rank = isset($row['current_rank']) ? (int) $row['current_rank'] : ($i + 1);
            $rating = isset($row['rating']) ? (float) $row['rating'] : null;
            $reviews_count = isset($row['reviews_count']) ? (int) $row['reviews_count'] : null;
            $result = $wpdb->insert($table, [
                'profile_id'       => $profile_id,
                'competitor_name'  => isset($row['competitor_name']) ? substr($row['competitor_name'], 0, 255) : '',
                'google_place_id'  => isset($row['google_place_id']) ? substr($row['google_place_id'], 0, 255) : null,
                'main_category'    => isset($row['main_category']) ? substr($row['main_category'], 0, 255) : null,
                'current_rank'     => $rank,
                'rating'           => $rating,
                'reviews_count'    => $reviews_count,
                'last_check'       => $now,
                'created_at'       => $now,
                'updated_at'       => $now,
            ], [
                '%d', '%s', '%s', '%s', '%d', '%f', '%d', '%s', '%s', '%s',
            ]);
            if ($result !== false) {
                $insert_count++;
            } else {
                error_log('[AVI] replace_profile_competitors: wpdb->insert failed for leader ' . ($i + 1) . ', table=' . $table . ', last_error=' . $wpdb->last_error);
            }
        }
        error_log('[AVI] replace_profile_competitors: inserted ' . $insert_count . ' leaders into ' . $table . ' for profile_id=' . $profile_id);

        if ($profile_id === 1) {
            $kw_table = $this->table( 'avi_saas_keywords' );
            $updated = $wpdb->query($wpdb->prepare(
                "UPDATE `{$kw_table}` SET keyword = REPLACE(keyword, 'Meilleur ', 'Best ') WHERE profile_id = 1 AND keyword LIKE %s",
                'Meilleur %'
            ));
            if ($updated > 0) {
                error_log('[AVI] replace_profile_competitors: forced Meilleur->Best for profile 1, rows updated=' . $updated);
            }
        }
        return $insert_count > 0;
    }

    /**
     * Synchronisation initiale (First Sync) : récupère infos Google, scan DataForSEO Secteur,
     * enregistre les 3 leaders et met à jour last_metrics_snapshot.
     * À appeler lorsque is_first_sync = 1.
     *
     * @param int $profile_id
     * @return array|false ['success' => bool, 'position' => int|string, 'leaders' => array, 'google' => array] ou false
     */
    public function run_initial_sync($profile_id) {
        set_time_limit(300); // 5 minutes pour Google + DataForSEO
        // Routage interne (éviter 502) : tout appel au backend Avision DOIT utiliser l'IP locale, pas l'URL publique.
        // URL : http://127.0.0.1:8001 (ou AVISION_BACKEND_INTERNAL_URL si définie). Headers : 'Host' => 'engine.avision.org'.
        // Ce flux n'appelle que Google API et DataForSEO (pas engine.avision.org).
        if (!headers_sent()) {
            header('Content-Type: application/json');
        }
        $profile_id = (int) $profile_id;
        error_log('[Avision First Sync] run_initial_sync started for profile_id=' . $profile_id);
        if ($profile_id <= 0) {
            error_log('[Avision First Sync] Abort: profile_id <= 0');
            return false;
        }

        $profile = $this->get_saas_profile_for_sync($profile_id);

        if (!$profile || empty($profile['google_access_token'])) {
            error_log('[Avision First Sync] Abort: profile not found or missing google_access_token for profile_id=' . $profile_id);
            return false;
        }

        $city = isset($profile['city']) ? trim($profile['city']) : '';
        $category = isset($profile['category']) ? trim($profile['category']) : '';
        $business_name = isset($profile['business_name']) ? trim($profile['business_name']) : '';
        $location_id = isset($profile['google_location_name']) ? trim($profile['google_location_name']) : '';
        $country = isset($profile['country']) ? strtoupper(trim((string) $profile['country'])) : 'FR';

        // ANTI-RÉGRESSION #186 — Ne jamais lancer sans google_location_name
        if ( empty( $location_id ) ) {
            error_log( '[AVI] run_initial_sync ABORT — google_location_name vide profil ' . $profile_id );
            return false;
        }

        $category_to_search_keyword = array(
            'consultant en marketing'  => 'Marketing',
            'consultant marketing'     => 'Marketing',
            'agence marketing'         => 'Marketing',
            'agence de communication'  => 'Communication',
            'agence web'               => 'Agence web',
            'consultant seo'           => 'Marketing',
            'consultant digital'       => 'Marketing digital',
            'restaurant'               => 'Restaurant',
            'dentiste'                 => 'Dentiste',
            'avocat'                   => 'Avocat',
            'plombier'                 => 'Plombier',
            'coiffeur'                 => 'Coiffeur',
            'médecin'                  => 'Médecin',
            'kinésithérapeute'         => 'Kinésithérapeute',
            'ostéopathe'               => 'Ostéopathe',
            'architecte'               => 'Architecte',
            'comptable'                => 'Comptable',
            'expert-comptable'         => 'Expert-comptable',
        );
        $cat_lower   = mb_strtolower( trim( $category ) );
        $search_base = $category;
        foreach ( $category_to_search_keyword as $key => $mapped ) {
            if ( mb_strpos( $cat_lower, $key ) !== false ) {
                $search_base = $mapped;
                error_log( '[AVI] Category mapped: ' . $category . ' → ' . $search_base );
                break;
            }
        }
        // $search_base utilisé UNIQUEMENT pour les recherches DataForSEO
        // NE PAS modifier $profile['category'] ni $category
        // Sinon la règle #154 détecte un faux changement → purge keywords
        $category_for_search = $search_base; // alias pour DataForSEO uniquement

        // Nettoyage immédiat : remplacer "Meilleur ..." par "Best ..." ou "Mejor ..." selon le pays
        if ($profile_id === 1 && ($country === 'GB' || $country === 'US')) {
            global $wpdb;
            $kw_table = $this->table('avi_saas_keywords');
            $wpdb->query($wpdb->prepare(
                "UPDATE {$kw_table} SET keyword = REPLACE(keyword, 'Meilleur ', 'Best ') WHERE profile_id = %d AND keyword LIKE 'Meilleur %%'",
                $profile_id
            ));
        }

        // 1. Récupération Google (note, avis, site, téléphone, photos)
        error_log('[Avision First Sync] Step 1: Calling Google API for location_id=' . $location_id);
        // ANTI-RÉGRESSION #217 [02 juin 2026] — Refresh token préventif avant fetch metrics
        $this->refresh_google_access_token_if_needed( $profile_id );
        $profile = $this->get_saas_profile_for_sync( $profile_id ); // relire token frais
        $google_data = $this->fetch_google_business_metrics( $profile['google_access_token'], $location_id, $profile_id );
        // Mettre à jour $profile en mémoire pour que category_for_filter en Step 2 le lise
        if ( ! empty( $google_data['google_primary_category'] ) ) {
            $profile['google_primary_category'] = $google_data['google_primary_category'];
        }
        error_log('[Avision First Sync] Step 1: Google API done. rating=' . (isset($google_data['rating']) ? $google_data['rating'] : 'n/a') . ', review_count=' . (isset($google_data['review_count']) ? $google_data['review_count'] : 'n/a'));

        global $wpdb;

        // ANTI-RÉGRESSION #154 [Sprint 30] — Purge KW si category changée
        // old_category_before_google : compare category DB = 'client' vs GBP réelle
        $old_category_before_google = isset( $profile['category'] ) ? $profile['category'] : '';
        $new_category_from_google   = isset( $profile['google_primary_category'] )
            ? $profile['google_primary_category'] : '';
        if (
            ! empty( $old_category_before_google ) &&
            ! empty( $new_category_from_google ) &&
            strtolower( $old_category_before_google ) !== strtolower( $new_category_from_google )
        ) {
            error_log( '[AVI] #154 category changée old_category_before_google='
                . $old_category_before_google . ' → ' . $new_category_from_google
                . ' profile_id=' . $profile_id );
            $wpdb->delete( $wpdb->prefix . 'avi_saas_keywords', array( 'profile_id' => $profile_id ) );
            $this->insert_initial_keywords_from_profile_data( $profile_id, $profile );
        }

        // Fallback neighborhood depuis pré-inscription [Sprint 30]
        // Règle #151 — chaîne complète neighborhood dans run_initial_sync
        if ( empty( $profile['neighborhood'] ) && ! empty( $profile['email'] ) ) {
            $pre_neighborhood = $wpdb->get_var( $wpdb->prepare(
                "SELECT neighborhood FROM {$wpdb->prefix}avi_pre_inscription
         WHERE email = %s AND neighborhood != ''
         ORDER BY created_at DESC LIMIT 1",
                $profile['email']
            ) );
            if ( ! empty( $pre_neighborhood ) ) {
                $profile['neighborhood'] = sanitize_text_field( (string) $pre_neighborhood );
                $wpdb->update(
                    $wpdb->prefix . 'avi_saas_profiles',
                    array( 'neighborhood' => $profile['neighborhood'] ),
                    array( 'id' => $profile_id )
                );
                error_log( '[AVI] Fallback neighborhood depuis pré-inscription profile_id='
                    . $profile_id . ' neighborhood=' . $profile['neighborhood'] );
            }
        }

        // 2. Scan Radar : les 3 mots-clés du profil (avi_saas_keywords)
        // ANTI-RÉGRESSION #176 — ne jamais régénérer si au moins 1 keyword existe (COUNT total, sans filtre scan_status / is_strategic).
        global $wpdb;
        $total_kw = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}avi_saas_keywords WHERE profile_id = %d",
                $profile_id
            )
        );
        if ( $total_kw === 0 ) {
            error_log( '[AVI] Auto-populating 3 keywords for profile ' . $profile_id );
            $this->insert_initial_keywords_from_profile_data( $profile_id, $profile );
        }
        $keywords = $this->get_keywords_by_profile_id_wpdb( $profile_id, 3 );
        error_log('[Avision First Sync] Step 2: got ' . count($keywords) . ' keywords for profile_id=' . $profile_id . ', proceeding to DataForSEO calls');
        error_log('[AVI] Starting DataForSEO scan for ' . count($keywords) . ' keywords');
        $business_lower = mb_strtolower($business_name);
        $ranks_for_avg = array();
        $leaders = array();
        $first_scan_items = array();

        // ── Résolution catégorie réelle pour filtre concurrent ──
        // google_primary_category disponible après Step 1 (fetch_google_business_metrics)
        $hood = isset( $profile['neighborhood'] ) ? trim( (string) $profile['neighborhood'] ) : '';
        $category_for_filter = '';
        if ( ! empty( $google_data['google_primary_category'] ) ) {
            $category_for_filter = trim( (string) $google_data['google_primary_category'] );
        } elseif ( ! empty( $profile['google_primary_category'] ) ) {
            $category_for_filter = trim( (string) $profile['google_primary_category'] );
        } elseif ( ! empty( $category )
            && strtolower( trim( $category ) ) !== 'client'
            && strtolower( trim( $category ) ) !== '' ) {
            $category_for_filter = $category;
        } elseif ( ! empty( $category_for_search ) ) {
            // Extraire le métier du keyword (ex: "Consultant en marketing Pau" → "Consultant en marketing")
            $category_for_filter = trim( str_ireplace(
                array( $city, $hood, 'à proximité', 'near me', 'cerca de mi' ),
                '',
                $category_for_search
            ) );
        }
        error_log( '[AVI] category_for_filter résolu: "' . $category_for_filter
            . '" (category="' . $category . '", google_primary="'
            . ( isset( $google_data['google_primary_category'] ) ? $google_data['google_primary_category'] : '' ) . '")' );

        $_expanded_keywords = array();
        $_profile_group_for_expand = $category_for_filter !== ''
            ? $this->find_maps_category_group( $category_for_filter, $this->get_maps_category_groups() )
            : null;
        $profile_lang_expand = 'fr';
        if ( isset( $profile['lang'] ) && is_string( $profile['lang'] ) && trim( $profile['lang'] ) !== '' ) {
            $profile_lang_expand = strtolower( trim( $profile['lang'] ) );
        } elseif ( in_array( $country, array( 'GB', 'US', 'CA', 'AU', 'IE' ), true ) ) {
            $profile_lang_expand = 'en';
        } elseif ( in_array( $country, array( 'ES', 'MX', 'AR', 'CO', 'CL', 'PE' ), true ) ) {
            $profile_lang_expand = 'es';
        }
        if ( $_profile_group_for_expand !== null && $_profile_group_for_expand !== '' ) {
            $_expanded_keywords = $this->get_maps_niche_expanded_keywords(
                $_profile_group_for_expand,
                $city,
                $profile_lang_expand
            );
            if ( ! empty( $_expanded_keywords ) ) {
                error_log( '[AVI] Secteur niche "' . $_profile_group_for_expand
                    . '" — keywords élargis: ' . implode( ', ', $_expanded_keywords ) );
            }
        }

        $kw_index = 0;
        foreach ($keywords as $kw) {
            $kw_index++;
            $kw_id = isset($kw['id']) ? (int) $kw['id'] : 0;
            $kw_keyword = isset($kw['keyword']) ? trim((string) $kw['keyword']) : '';
            $kw_city = isset($kw['target_city']) && trim((string) $kw['target_city']) !== '' ? trim($kw['target_city']) : $city;
            if ($kw_keyword === '') continue;

            error_log('[Avision First Sync] Step 2: DataForSEO call ' . $kw_index . '/' . count($keywords) . ' keyword="' . $kw_keyword . '" location="' . $kw_city . '" country="' . $country . '"');
            $maps_results = $this->fetch_dataforseo_maps_sector($kw_keyword, $kw_city, $country, $profile_id);
            if (is_wp_error($maps_results)) {
                error_log('[Avision First Sync] DataForSEO error: ' . $maps_results->get_error_message());
                return false;
            }
            error_log('[Avision First Sync] DataForSEO: response received for "' . $kw_keyword . '", items count=' . (isset($maps_results['items']) ? count($maps_results['items']) : 0));
            $client_rank_kw = null;

            if (!empty($maps_results['items'])) {
                $items = $maps_results['items'];
                if (empty($first_scan_items)) {
                    $first_scan_items = $items;
                    $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                        array( 'items' => $items ),
                        $business_name,
                        3,
                        $category_for_filter
                    );
                    // ── fallback sans filtre si catégorie trop niche ──
                    if ( empty( $leaders ) && $category_for_filter !== '' ) {
                        error_log( '[AVI] category_for_filter "' . $category_for_filter
                            . '" → 0 leaders, fallback sans filtre' );
                        $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                            array( 'items' => $items ),
                            $business_name,
                            3,
                            ''
                        );
                    }
                    foreach ( $leaders as $l ) {
                        error_log('[AVI] Extracted Neighbor Leader: ' . ( isset( $l['competitor_name'] ) ? $l['competitor_name'] : '?' ) . ' | rank=' . ( isset( $l['current_rank'] ) ? $l['current_rank'] : 'n/a' ));
                    }
                }
                foreach ($items as $idx => $item) {
                    $title = isset($item['title']) ? $item['title'] : (isset($item['original_title']) ? $item['original_title'] : '');
                    $title_lower = $title !== '' ? mb_strtolower($title) : '';
                    // Matching souple : insensible à la casse, le nom du business est contenu dans le résultat (ou l'inverse).
                    if ($business_lower !== '' && $title_lower !== '' && (mb_strpos($title_lower, $business_lower) !== false || mb_strpos($business_lower, $title_lower) !== false)) {
                        $client_rank_kw = isset($item['rank_absolute']) ? (int) $item['rank_absolute'] : ($idx + 1);
                        break;
                    }
                }
            }

            if ($client_rank_kw !== null && $client_rank_kw <= 20) {
                $ranks_for_avg[] = $client_rank_kw;
            } else {
                $ranks_for_avg[] = 101;
            }
            $rank_for_update = ($client_rank_kw !== null) ? (int) $client_rank_kw : 101;
            $this->update_keyword_scan_completed($kw_id, $rank_for_update);
        }

        // KPI : moyenne des positions (101 = non trouvé pour un mot-clé)
        $avg_position = count($ranks_for_avg) > 0 ? round(array_sum($ranks_for_avg) / count($ranks_for_avg), 1) : 101;
        $client_position = $avg_position;
        $average_position = $client_position;
        error_log('[AVI] KPI Average Position calculated: ' . $average_position);

        // Leaders : cascade 4 niveaux si le scan Radar n'a pas extrait de voisins
        if ( empty( $leaders ) ) {
            $hood = isset( $profile['neighborhood'] ) ? trim( (string) $profile['neighborhood'] ) : '';

            $fallback_levels = array(
                1 => trim( $category_for_search . ' ' . $hood ),
                2 => trim( $category_for_search . ' ' . $city ),
                3 => trim( $category_for_search . ' ' . $hood ),
                4 => trim( $category_for_search . ' ' . $city ),
            );
            $fallback_logs = array(
                1 => '[AVI] Leaders fallback niveau 1 utilisé: ',
                2 => '[AVI] Leaders fallback niveau 2 utilisé: ',
                3 => '[AVI] Leaders fallback niveau 3 utilisé: ',
                4 => '[AVI] Leaders fallback niveau 4 utilisé: ',
            );
            foreach ( $fallback_levels as $lvl => $keyword_n ) {
                if ( $keyword_n === '' ) {
                    continue;
                }
                $maps_fallback = $this->fetch_dataforseo_maps_sector( $keyword_n, $city, $country, $profile_id );
                $leaders       = array();
                if ( ! is_wp_error( $maps_fallback ) && ! empty( $maps_fallback['items'] ) ) {
                    $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                        array( 'items' => $maps_fallback['items'] ),
                        $business_name,
                        3,
                        $category_for_filter
                    );
                    // ── fallback sans filtre si catégorie trop niche ──
                    if ( empty( $leaders ) && $category_for_filter !== '' ) {
                        $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                            array( 'items' => $maps_fallback['items'] ),
                            $business_name,
                            3,
                            ''
                        );
                    }
                }
                if ( ! isset( $fallback_logs[ $lvl ] ) ) {
                    continue;
                }
                error_log( $fallback_logs[ $lvl ] . $keyword_n . ' → ' . count( $leaders ) . ' résultats' );
                if ( count( $leaders ) >= 1 ) {
                    break;
                }
            }

            // Fallback niveau 5 — keywords élargis secteurs niches
            if ( empty( $leaders ) && ! empty( $_expanded_keywords ) ) {
                foreach ( $_expanded_keywords as $_exp_kw ) {
                    $maps_exp = $this->fetch_dataforseo_maps_sector(
                        $_exp_kw,
                        $city,
                        $country,
                        $profile_id
                    );
                    if ( ! is_wp_error( $maps_exp ) && ! empty( $maps_exp['items'] ) ) {
                        $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                            array( 'items' => $maps_exp['items'] ),
                            $business_name,
                            3,
                            $category_for_filter
                        );
                        if ( empty( $leaders ) ) {
                            $leaders = $this->extract_most_seen_neighbors_from_maps_items(
                                array( 'items' => $maps_exp['items'] ),
                                $business_name,
                                3,
                                ''
                            );
                        }
                        if ( ! empty( $leaders ) ) {
                            error_log( '[AVI] Leaders via keyword élargi "' . $_exp_kw
                                . '" → ' . count( $leaders ) . ' résultats' );
                            break;
                        }
                    }
                }
            }
        }

        // 3. Stockage : 3 leaders dans avi_saas_competitors
        error_log('[Avision First Sync] Step 3: Saving ' . count($leaders) . ' leaders for profile_id=' . $profile_id);
        $leaders_saved = $this->replace_profile_competitors($profile_id, $leaders);

        // 4. last_metrics_snapshot (JSON) — position_rank = moyenne des 3 mots-clés
        global $wpdb;
        // Lire les valeurs existantes en DB comme garde-fou
        $existing_rating = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT average_rating FROM {$wpdb->prefix}avi_saas_profiles WHERE id=%d",
                $profile_id
            )
        );
        $existing_reviews = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT review_count FROM {$wpdb->prefix}avi_saas_profiles WHERE id=%d",
                $profile_id
            )
        );

        // Priorité : données GBP fraîches si valides, sinon conservation DB
        $snap_rating       = ( isset( $google_data['rating'] ) && (float) $google_data['rating'] > 0 )
            ? $google_data['rating']
            : ( $existing_rating > 0 ? (float) $existing_rating : null );
        $snap_review_count = ( isset( $google_data['review_count'] ) && (int) $google_data['review_count'] > 0 )
            ? $google_data['review_count']
            : ( $existing_reviews > 0 ? (int) $existing_reviews : null );

        $snapshot = array(
            'rating'         => $snap_rating,
            'review_count'   => $snap_review_count,
            'website'        => isset( $google_data['website'] ) ? $google_data['website'] : null,
            'phone'          => isset( $google_data['phone'] ) ? $google_data['phone'] : null,
            'total_photos'   => isset( $google_data['total_photos'] ) ? $google_data['total_photos'] : null,
            'has_hours'      => ! empty( $google_data['has_hours'] ),
            'position_rank'  => $client_position,
            'synced_at'      => gmdate( 'Y-m-d\TH:i:s\Z' ),
        );
        $this->update_profile_last_metrics_snapshot( $profile_id, json_encode( $snapshot ) );
        // Ne jamais UPDATE si la valeur existante est meilleure (R3)
        $existing_rating = (float) $wpdb->get_var(
            $wpdb->prepare( "SELECT average_rating FROM {$wpdb->prefix}avi_saas_profiles WHERE id=%d", $profile_id )
        );
        $new_rating = isset( $google_data['rating'] ) ? (float) $google_data['rating'] : 0;

        if ( $new_rating > 0 ) {
            $wpdb->update(
                $wpdb->prefix . 'avi_saas_profiles',
                array(
                    'average_rating' => $new_rating,
                    'review_count'   => max( (int) ( $google_data['review_count'] ?? 0 ), 0 ),
                ),
                array( 'id' => $profile_id ),
                array( '%f', '%d' ),
                array( '%d' )
            );
            error_log( '[AVI] run_initial_sync: UPDATE average_rating=' . $new_rating . ' review_count=' . ( $google_data['review_count'] ?? 0 ) . ' profile_id=' . $profile_id );
        } elseif ( $existing_rating > 0 ) {
            error_log( '[AVI] Garde R3 — conservation rating=' . $existing_rating . ' profil ' . $profile_id );
        }
        // ANTI-RÉGRESSION #218 [02 juin 2026] — Log si rating vide après fetch GBP
        if ( $new_rating <= 0 ) {
            error_log( '[AVI Fleet] reputation_empty profile_id=' . $profile_id
                . ' rating=' . ( $google_data['rating'] ?? 'null' )
                . ' review_count=' . ( $google_data['review_count'] ?? 'null' )
                . ' http=' . ( $google_data['last_http_code'] ?? 'unknown' )
            );
        }
        error_log('[Avision First Sync] Step 4: Snapshot saved for profile_id=' . $profile_id);

        $table = $this->table('avi_saas_profiles');
        $step1_ok = ! empty( $google_data['rating'] ) || ! empty( $google_data['google_primary_category'] );
        if ( $leaders_saved && $step1_ok ) {
            $wpdb->update( $table, array( 'is_first_sync' => 0 ), array( 'id' => $profile_id ), array( '%d' ), array( '%d' ) );
        } else {
            error_log( '[AVI] run_initial_sync INCOMPLETE — is_first_sync reste 1 profil ' . $profile_id );
        }

        // Email transactionnel: OUT_OF_INFUSION (J0 termine, donnees pretes).
        // ANTI-DOUBLON — envoi unique vers l'utilisateur, multiple vers admin
        $infusion_email_sent = get_user_meta( (int) $profile['user_id'], 'avi_infusion_email_sent_' . $profile_id, true );
        if (!empty($profile['user_id']) && class_exists('Avision\\Avision_Email_Manager') && empty($infusion_email_sent)) {
            $dashboard_url = add_query_arg(['audit_id' => $profile_id], home_url('/premium-dashboard'));
            $email_sent = Avision_Email_Manager::send_avi_email(
                (int) $profile['user_id'],
                Avision_Email_Manager::TEMPLATE_OUT_OF_INFUSION,
                [
                    'profile_id' => $profile_id,
                    'business_name' => $business_name,
                    'dashboard_url' => $dashboard_url,
                    'score' => isset($profile['score']) ? (string) $profile['score'] : '',
                ]
            );
            if ($email_sent) {
                // Marquer envoi unique pour l'utilisateur — admin reçoit toujours
                update_user_meta( (int) $profile['user_id'], 'avi_infusion_email_sent_' . $profile_id, current_time('mysql') );
                // Notification admin séparée — toujours envoyée (sans garde)
                wp_mail(
                    'toniosbh@gmail.com',
                    '[AVI Admin] Infusion terminée — ' . $business_name . ' (profil ' . $profile_id . ')',
                    'Infusion cycle terminé pour ' . $business_name . ' (profile_id=' . $profile_id . ') le ' . current_time('mysql') . '.'
                );
            }
            if (!$email_sent) {
                error_log('[Avision First Sync] OUT_OF_INFUSION email not sent for profile_id=' . $profile_id);
            }
        }

        error_log('[Avision First Sync] run_initial_sync completed successfully for profile_id=' . $profile_id . ', position=' . $client_position);
        return [
            'success' => true,
            'position' => $client_position,
            'avg_position' => $avg_position,
            'leaders' => $leaders,
            'google' => $google_data,
        ];
    }

    /**
     * Retourne les profils B2C initialisés mais avec données GBP manquantes.
     * ANTI-RÉGRESSION #187 [23 mai 2026]
     *
     * @param int $limit Nombre max de profils à retourner.
     * @return array<int, array<string, mixed>>
     */
    public function get_incomplete_b2c_profiles( $limit = 5 ) {
        global $wpdb;
        $table = $this->table( 'avi_saas_profiles' );
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, user_id, google_location_name, google_access_token,
                google_place_id, google_primary_category,
                review_count, average_rating, category, score
         FROM {$table}
         WHERE is_first_sync = 0
           AND google_location_name IS NOT NULL
           AND google_location_name != ''
           AND google_location_name NOT LIKE 'demo%%'
           AND google_location_name NOT LIKE 'accounts/demo%%'
           AND user_id IS NOT NULL
           AND (
               ( google_primary_category IS NULL OR google_primary_category = '' )
               OR ( google_place_id IS NULL OR google_place_id = '' )
               OR ( review_count = 0 AND average_rating IS NULL )
           )
         ORDER BY id DESC
         LIMIT %d",
                $limit
            ),
            ARRAY_A
        );
    }

    /**
     * Répare chirurgicalement un profil avec données GBP manquantes.
     * Ne relance que les étapes défaillantes — ne pas réécrire les données valides.
     * ANTI-RÉGRESSION #187 [23 mai 2026]
     *
     * @param int $profile_id ID profil SaaS.
     * @return bool True si au moins une réparation a été effectuée.
     */
    public function repair_incomplete_profile( $profile_id ) {
        global $wpdb;
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 ) {
            return false;
        }

        $profile = $this->get_saas_profile_for_sync( $profile_id );
        if ( ! is_array( $profile ) || empty( $profile['google_location_name'] ) || empty( $profile['google_access_token'] ) ) {
            error_log( '[AVI REPAIR] Profil ' . $profile_id . ' — skip (location ou token manquant)' );
            return false;
        }

        $table    = $this->table( 'avi_saas_profiles' );
        $repaired = array();

        // Étape A — données GBP manquantes → refaire Step 1
        $needs_gbp = empty( $profile['google_primary_category'] )
            || empty( $profile['google_place_id'] )
            || ( (int) $profile['review_count'] === 0 && empty( $profile['average_rating'] ) );

        if ( $needs_gbp ) {
            $google_data = $this->fetch_google_business_metrics(
                $profile['google_access_token'],
                trim( (string) $profile['google_location_name'] ),
                $profile_id
            );

            // Rating + reviews
            if ( isset( $google_data['rating'] ) && (float) $google_data['rating'] > 0 ) {
                $wpdb->update(
                    $table,
                    array(
                        'average_rating' => (float) $google_data['rating'],
                        'review_count'   => (int) ( $google_data['review_count'] ?? 0 ),
                    ),
                    array( 'id' => $profile_id ),
                    array( '%f', '%d' ),
                    array( '%d' )
                );
                $repaired[] = 'rating=' . $google_data['rating'];
            }

            // google_primary_category
            if ( ! empty( $google_data['google_primary_category'] ) ) {
                $wpdb->update(
                    $table,
                    array( 'google_primary_category' => $google_data['google_primary_category'] ),
                    array( 'id' => $profile_id ),
                    array( '%s' ),
                    array( '%d' )
                );
                $repaired[] = 'category=' . $google_data['google_primary_category'];
            }

            // google_place_id
            if ( ! empty( $google_data['google_place_id'] ) && empty( $profile['google_place_id'] ) ) {
                $wpdb->update(
                    $table,
                    array( 'google_place_id' => $google_data['google_place_id'] ),
                    array( 'id' => $profile_id ),
                    array( '%s' ),
                    array( '%d' )
                );
                $repaired[] = 'place_id';
            }
        }

        // Étape B — competitors manquants → run_initial_sync complet
        $comp_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}avi_saas_competitors WHERE profile_id=%d",
                $profile_id
            )
        );

        if ( $comp_count === 0 ) {
            $wpdb->update( $table, array( 'is_first_sync' => 1 ), array( 'id' => $profile_id ), array( '%d' ), array( '%d' ) );
            $this->run_initial_sync( $profile_id );
            $repaired[] = 'full_resync';
        }

        if ( ! empty( $repaired ) ) {
            error_log( '[AVI REPAIR] ✅ Profil ' . $profile_id . ' réparé: ' . implode( ', ', $repaired ) );
            return true;
        }

        error_log( '[AVI REPAIR] Profil ' . $profile_id . ' — rien à réparer' );
        return false;
    }

    /**
     * Cerveau : calcule score de visibilité + Net Worth, enregistre le premier point dans avi_saas_rank_history,
     * met à jour le score du profil et passe is_first_sync à 0.
     * À appeler après run_initial_sync() pour finaliser le First Sync.
     *
     * @param int $profile_id
     * @return array|false ['score' => int, 'net_worth' => float, 'saved' => bool] ou false
     */
    public function run_visibility_calculation_and_save($profile_id) {
        $profile_id = (int) $profile_id;
        if ($profile_id <= 0) return false;


        $row = $this->get_saas_profile_with_snapshot($profile_id);
        if (!$row || empty($row['snapshot'])) {
            return false;
        }

        $snap = $row['snapshot'];
        $category = isset($row['category']) ? trim((string) $row['category']) : '';
        $category_precise = $category !== '' && strlen($category) > 2 && strtolower($category) !== 'other' && strtolower($category) !== 'generic';

        $profile_data = [
            'has_website' => !empty($snap['website']),
            'has_hours' => !empty($snap['has_hours']),
            'has_phone' => !empty($snap['phone']),
            'category_precise' => $category_precise,
            'review_count' => isset($snap['review_count']) ? (int) $snap['review_count'] : 0,
            'rating' => isset($snap['rating']) ? (float) $snap['rating'] : 0.0,
            'position_rank' => isset($snap['position_rank']) ? $snap['position_rank'] : null,
        ];

        if (function_exists('avi_calculate_visibility_score')) {
            $result = avi_calculate_visibility_score($profile_data);
        } else {
            $result = ['score' => 30, 'net_worth' => 50.0];
        }

        $score = isset($result['score']) ? (int) $result['score'] : 30;
        $net_worth = isset($result['net_worth']) ? (float) $result['net_worth'] : 50.0;

        $position_rank = $profile_data['position_rank'];
        $avg_position = null;
        if (is_numeric($position_rank) && $position_rank !== '') {
            $avg_position = (float) $position_rank;
        }

        $this->insert_rank_history_row($profile_id, null, $avg_position, 0, 0, $net_worth, $score);
        $this->update_profile_score($profile_id, $score);
        $this->set_profile_first_sync_done($profile_id);


        return [
            'score' => $score,
            'net_worth' => $net_worth,
            'saved' => true,
        ];
    }

    /**
     * Assure les colonnes OAuth Google sur avi_saas_profiles (MySQL / $wpdb).
     *
     * @return void
     */
    public function ensure_saas_profile_google_oauth_columns(): void {
        global $wpdb;
        if ( ! isset( $wpdb ) || ! $wpdb ) {
            return;
        }
        $table = $this->table( 'avi_saas_profiles' );
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
            return;
        }
        $tquoted = '`' . str_replace( '`', '``', $table ) . '`';
        $need    = array(
            'google_token_expires_at' => "ALTER TABLE {$tquoted} ADD COLUMN `google_token_expires_at` DATETIME NULL DEFAULT NULL",
            'google_refresh_token'    => "ALTER TABLE {$tquoted} ADD COLUMN `google_refresh_token` TEXT NULL",
        );
        foreach ( $need as $col => $alter_sql ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de colonne fixe, table validée.
            $found = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $col ) );
            if ( $found !== $col ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DDL après validation.
                $wpdb->query( $alter_sql );
                if ( $wpdb->last_error !== '' && function_exists( 'error_log' ) ) {
                }
            }
        }
    }

    /**
     * Indique si un rafraîchissement proactif est nécessaire (expiration connue ou access_token vide).
     *
     * @param array<string, mixed> $row Ligne profil.
     * @return bool
     */
    private function profile_needs_proactive_google_token_refresh( array $row ): bool {
        $at = isset( $row['google_access_token'] ) ? trim( (string) $row['google_access_token'] ) : '';
        $rt = isset( $row['google_refresh_token'] ) ? trim( (string) $row['google_refresh_token'] ) : '';
        if ( $rt === '' ) {
            return false;
        }
        if ( $at === '' ) {
            return true;
        }
        $exp = isset( $row['google_token_expires_at'] ) ? trim( (string) $row['google_token_expires_at'] ) : '';
        if ( $exp === '' ) {
            return false;
        }
        $ts = strtotime( $exp );
        if ( $ts === false ) {
            return false;
        }

        return $ts <= ( time() + 120 );
    }

    /**
     * Rafraîchit le jeton Google OAuth si expiré (ou si $force) et met à jour avi_saas_profiles.
     *
     * @param int  $profile_id ID ligne profil.
     * @param bool $force      true après HTTP 401.
     * @return bool true si un jeton utilisable est en base après l’appel (refresh OK ou déjà valide sans action).
     */
    public function refresh_google_access_token_if_needed( int $profile_id, bool $force = false ): bool {
        $profile_id = (int) $profile_id;
        if ( $profile_id <= 0 || ! function_exists( 'wp_remote_post' ) ) {
            return false;
        }
        $this->ensure_saas_profile_google_oauth_columns();
        $row = $this->get_saas_profile_for_sync( $profile_id );
        if ( ! is_array( $row ) ) {
            return false;
        }
        $refresh_token = isset( $row['google_refresh_token'] ) ? trim( (string) $row['google_refresh_token'] ) : '';
        if ( $refresh_token === '' ) {
            if ( function_exists( 'error_log' ) ) {
            }

            return false;
        }
        if ( ! $force && ! $this->profile_needs_proactive_google_token_refresh( $row ) ) {
            return true;
        }
        $creds = function_exists( 'avision_google_oauth_get_client_credentials' )
            ? \avision_google_oauth_get_client_credentials()
            : null;
        if ( ! is_array( $creds ) || $creds['client_id'] === '' || $creds['client_secret'] === '' ) {
            if ( function_exists( 'error_log' ) ) {
            }

            return false;
        }
        $token_url = 'https://oauth2.googleapis.com/token';
        $response  = wp_remote_post(
            $token_url,
            array(
                'timeout' => 30,
                'body'    => array(
                    'client_id'     => $creds['client_id'],
                    'client_secret' => $creds['client_secret'],
                    'refresh_token' => $refresh_token,
                    'grant_type'    => 'refresh_token',
                ),
            )
        );
        if ( is_wp_error( $response ) ) {
            if ( function_exists( 'error_log' ) ) {
            }

            return false;
        }
        $http = (int) wp_remote_retrieve_response_code( $response );
        $raw  = wp_remote_retrieve_body( $response );
        $data = json_decode( $raw, true );
        if ( $http !== 200 || ! is_array( $data ) || empty( $data['access_token'] ) ) {
            if ( function_exists( 'error_log' ) ) {
            }

            return false;
        }
        $new_access = trim( (string) $data['access_token'] );
        $expires_in = isset( $data['expires_in'] ) ? (int) $data['expires_in'] : 3600;
        $expires_at = gmdate( 'Y-m-d H:i:s', time() + max( 60, $expires_in ) );

        global $wpdb;
        if ( ! isset( $wpdb ) || ! $wpdb ) {
            return false;
        }
        $table = $this->table( 'avi_saas_profiles' );
        $update = array(
            'google_access_token'     => $new_access,
            'google_token_expires_at' => $expires_at,
            'updated_at'              => function_exists( 'current_time' ) ? current_time( 'mysql' ) : gmdate( 'Y-m-d H:i:s' ),
        );
        $formats = array( '%s', '%s', '%s' );
        if ( ! empty( $data['refresh_token'] ) && is_string( $data['refresh_token'] ) ) {
            $update['google_refresh_token'] = trim( $data['refresh_token'] );
            $formats[]                      = '%s';
        }
        $wpdb->update( $table, $update, array( 'id' => $profile_id ), $formats, array( '%d' ) );
        if ( function_exists( 'error_log' ) ) {
        }

        return true;
    }

    /**
     * Récupère les métriques vitales de la fiche Google (note, avis, site, téléphone, photos).
     * Utilise l'API Business Information v1 quand location_id est au format accounts/xxx/locations/yyy.
     *
     * @param string $access_token
     * @param string $location_id Format accounts/xxx/locations/yyy ou vide
     * @param int    $profile_id  Contexte log (0 si inconnu)
     * @return array
     */
    private function fetch_google_business_metrics($access_token, $location_id, $profile_id = 0) {
        $out = ['rating' => null, 'review_count' => null, 'website' => null, 'phone' => null, 'total_photos' => null, 'has_hours' => false];
        $profile_id = (int) $profile_id;
        if ($location_id === '' || !function_exists('wp_remote_get')) {
            error_log('[Avision First Sync] Google API: skip (empty location_id or no wp_remote_get)');
            $out['api_error'] = $location_id === ''
                ? 'skip: empty location_id'
                : 'skip: wp_remote_get unavailable';
            return $out;
        }
        $location_name = ltrim($location_id, '/');
        $read_mask = 'title,storefrontAddress,phoneNumbers,websiteUri,categories,regularHours,profile';
        $url = 'https://mybusinessbusinessinformation.googleapis.com/v1/' . $location_name . '?readMask=' . urlencode($read_mask);
        error_log('[Avision First Sync] Google API: GET ' . $location_name . ' (readMask=' . $read_mask . ')');

        for ( $attempt = 1; $attempt <= 2; $attempt++ ) {
            if ( $profile_id > 0 ) {
                if ( $attempt === 1 ) {
                    $this->refresh_google_access_token_if_needed( $profile_id, false );
                }
                $prow = $this->get_saas_profile_for_sync( $profile_id );
                if ( is_array( $prow ) && ! empty( $prow['google_access_token'] ) ) {
                    $access_token = (string) $prow['google_access_token'];
                }
            }

            $response = wp_remote_get($url, [
                'headers' => ['Authorization' => 'Bearer ' . $access_token, 'Content-Type' => 'application/json'],
                'timeout' => 60,
            ]);
            if (is_wp_error($response)) {
                error_log('[Avision First Sync] Google API: WP_Error ' . $response->get_error_message());
                $out['api_error'] = wp_json_encode(
                    array(
                        'type'    => 'wp_remote_error',
                        'code'    => $response->get_error_code(),
                        'message' => $response->get_error_message(),
                    ),
                    JSON_UNESCAPED_UNICODE
                );
                return $out;
            }
            $code = wp_remote_retrieve_response_code($response);
            if ( $code === 401 && $profile_id > 0 && $attempt < 2 ) {
                $refreshed = false;
                if ( class_exists( __NAMESPACE__ . '\\Avision_API' ) ) {
                    $refreshed = Avision_API::refresh_google_token( $profile_id );
                } else {
                    if ( function_exists( 'error_log' ) ) {
                        error_log( '🔄 Token expiré, tentative de rafraîchissement pour le profil ' . $profile_id . '...' );
                    }
                    $refreshed = $this->refresh_google_access_token_if_needed( $profile_id, true );
                }
                if ( $refreshed ) {
                    $prow2 = $this->get_saas_profile_for_sync( $profile_id );
                    if ( is_array( $prow2 ) && ! empty( $prow2['google_access_token'] ) ) {
                        $access_token = (string) $prow2['google_access_token'];
                        continue;
                    }
                }
            }
            if ($code !== 200) {
                error_log('[Avision First Sync] Google API: HTTP ' . $code . ' (expected 200)');
                $raw_body         = wp_remote_retrieve_body($response);
                $out['api_error'] = $raw_body !== ''
                    ? $raw_body
                    : ( 'HTTP ' . (int) $code . ' (empty response body)' );
                return $out;
            }
            $raw_success_body = wp_remote_retrieve_body($response);
            $body = json_decode($raw_success_body, true);
            if (!is_array($body)) {
                error_log('[Avision First Sync] Google API: response body not valid JSON');
                $out['api_error'] = $raw_success_body !== ''
                    ? $raw_success_body
                    : 'HTTP 200 but body is not valid JSON (empty)';
                return $out;
            }
            error_log('[Avision First Sync] Google API: HTTP 200, body parsed OK');

            // ANTI-RÉGRESSION #185 [23 mai 2026] — Persistance google_primary_category depuis GBP
            // Extraire categories.primaryCategory.displayName
            if ( ! empty( $body['categories']['primaryCategory']['displayName'] ) ) {
                $gbp_primary_cat = sanitize_text_field( (string) $body['categories']['primaryCategory']['displayName'] );
                $out['google_primary_category'] = $gbp_primary_cat;
                if ( $profile_id > 0 ) {
                    global $wpdb;
                    $wpdb->update(
                        $this->table( 'avi_saas_profiles' ),
                        array( 'google_primary_category' => $gbp_primary_cat ),
                        array( 'id' => $profile_id ),
                        array( '%s' ),
                        array( '%d' )
                    );
                    error_log( '[AVI] google_primary_category persisté: "' . $gbp_primary_cat . '" profil ' . $profile_id );
                }
            }

            if (isset($body['websiteUri']) && $body['websiteUri'] !== '') {
                $out['website'] = $body['websiteUri'];
            }
            if (!empty($body['phoneNumbers']['primaryPhone'])) {
                $out['phone'] = $body['phoneNumbers']['primaryPhone'];
            }
            if (isset($body['metadata']['newReviewCount'])) {
                $out['review_count'] = (int) $body['metadata']['newReviewCount'];
            }
            if (isset($body['profile']['totalReviewCount'])) {
                $out['review_count'] = (int) $body['profile']['totalReviewCount'];
            }
            if (isset($body['profile']['averageRating'])) {
                $out['rating'] = (float) $body['profile']['averageRating'];
            }
            if (isset($body['profile']['profilePhotoUri']) && $body['profile']['profilePhotoUri'] !== '') {
                $out['total_photos'] = 1;
            }
            if (!empty($body['regularHours'])) {
                $out['has_hours'] = true;
            }
            // ── APPEL SÉPARÉ METADATA (place_id uniquement) ──
            // readMask principal exclut metadata (règle #137).
            // Un appel minimal séparé avec readMask=metadata est nécessaire.
            if ( $profile_id > 0 && empty( $out['google_place_id'] ) ) {
                $meta_url  = 'https://mybusinessbusinessinformation.googleapis.com/v1/'
                    . $location_name . '?readMask=' . urlencode( 'metadata' );
                $meta_resp = wp_remote_get(
                    $meta_url,
                    array(
                        'headers' => array( 'Authorization' => 'Bearer ' . $access_token ),
                        'timeout' => 10,
                    )
                );
                if ( ! is_wp_error( $meta_resp )
                    && (int) wp_remote_retrieve_response_code( $meta_resp ) === 200 ) {
                    $meta_body = json_decode( wp_remote_retrieve_body( $meta_resp ), true );
                    $place_id_meta = '';
                    if ( is_array( $meta_body ) && ! empty( $meta_body['metadata']['placeId'] ) ) {
                        $place_id_meta = sanitize_text_field( (string) $meta_body['metadata']['placeId'] );
                    } elseif ( is_array( $meta_body ) && ! empty( $meta_body['metadata']['newReviewUri'] ) ) {
                        $_kv = array();
                        parse_str(
                            (string) parse_url( $meta_body['metadata']['newReviewUri'], PHP_URL_QUERY ),
                            $_kv
                        );
                        $place_id_meta = isset( $_kv['placeid'] ) ? sanitize_text_field( (string) $_kv['placeid'] ) : '';
                    }
                    if ( $place_id_meta !== '' ) {
                        global $wpdb;
                        if ( isset( $wpdb ) && $wpdb ) {
                            $wpdb->update(
                                $this->table( 'avi_saas_profiles' ),
                                array( 'google_place_id' => $place_id_meta ),
                                array( 'id' => $profile_id ),
                                array( '%s' ),
                                array( '%d' )
                            );
                        }
                        $out['google_place_id'] = $place_id_meta;
                        error_log( '[Avision First Sync] place_id extrait: ' . $place_id_meta . ' (profil ' . $profile_id . ')' );
                    }
                }
                // Échec appel metadata → silencieux (non bloquant)
            }
            // ANTI-RÉGRESSION #163 [18 mai 2026] — persistance google_place_id
            // Lecture directe metadata.placeId (plus fiable que parse newReviewUri)
            // Exécuté indépendamment du flux rating/review — NE JAMAIS déplacer dans le bloc Places
            if ( $profile_id > 0 && empty( $out['google_place_id'] ) ) {
                $_gpi_direct = '';
                if ( ! empty( $body['metadata']['placeId'] ) ) {
                    $_gpi_direct = sanitize_text_field( (string) $body['metadata']['placeId'] );
                } elseif ( ! empty( $body['metadata']['newReviewUri'] ) ) {
                    $_gpi_kv = array();
                    parse_str( (string) parse_url( $body['metadata']['newReviewUri'], PHP_URL_QUERY ), $_gpi_kv );
                    $_gpi_direct = isset( $_gpi_kv['placeid'] ) ? sanitize_text_field( $_gpi_kv['placeid'] ) : '';
                }
                if ( $_gpi_direct !== '' ) {
                    global $wpdb;
                    if ( isset( $wpdb ) && $wpdb ) {
                        $wpdb->update(
                            $wpdb->prefix . 'avi_saas_profiles',
                            array( 'google_place_id' => $_gpi_direct ),
                            array( 'id' => $profile_id ),
                            array( '%s' ),
                            array( '%d' )
                        );
                        $out['google_place_id'] = $_gpi_direct;
                    }
                }
            }
            // ═══════════════════════════════════════════════════════════════
            // ANTI-RÉGRESSION #161 [18 mai 2026] — Places API (New) rating/reviews
            // via google_place_id (#164). NE JAMAIS supprimer sans autorisation Anthony.
            // ═══════════════════════════════════════════════════════════════
            $places_api_key = defined( 'AVI_GOOGLE_PLACES_API_KEY' )
                ? trim( (string) AVI_GOOGLE_PLACES_API_KEY )
                : '';
            $place_id_for_places = '';
            if ( $profile_id > 0 ) {
                $profile_places_row = $this->get_saas_profile_for_sync( $profile_id );
                if ( is_array( $profile_places_row ) && ! empty( $profile_places_row['google_place_id'] ) ) {
                    $place_id_for_places = trim( (string) $profile_places_row['google_place_id'] );
                }
            }
            if ( $place_id_for_places === '' && ! empty( $out['google_place_id'] ) ) {
                $place_id_for_places = trim( (string) $out['google_place_id'] );
            }
            if ( $place_id_for_places !== '' && $places_api_key !== '' && function_exists( 'wp_remote_get' ) ) {
                $place_resource = $place_id_for_places;
                if ( strpos( $place_resource, 'places/' ) === 0 ) {
                    $place_resource = substr( $place_resource, 7 );
                }
                $places_new_url = 'https://places.googleapis.com/v1/places/' . rawurlencode( $place_resource );
                $places_resp    = wp_remote_get(
                    $places_new_url,
                    array(
                        'timeout' => 15,
                        'headers' => array(
                            'X-Goog-Api-Key'     => $places_api_key,
                            'X-Goog-FieldMask'   => 'rating,userRatingCount',
                        ),
                    )
                );
                if ( ! is_wp_error( $places_resp ) && (int) wp_remote_retrieve_response_code( $places_resp ) === 200 ) {
                    $places_body = json_decode( wp_remote_retrieve_body( $places_resp ), true );
                    if ( is_array( $places_body ) ) {
                        // ANTI-RÉGRESSION #161 garde anti-écrasement
                        // NE PAS écraser si Places retourne 0 et valeur GBP déjà présente
                        $places_review_count = isset( $places_body['userRatingCount'] )
                            ? (int) $places_body['userRatingCount'] : null;
                        $places_rating = isset( $places_body['rating'] )
                            ? (float) $places_body['rating'] : null;

                        // Ne jamais écraser une valeur existante non-nulle avec null/0
                        global $wpdb;
                        $existing_rating = (float) $wpdb->get_var( $wpdb->prepare(
                            "SELECT average_rating FROM {$wpdb->prefix}avi_saas_profiles WHERE id=%d",
                            $profile_id
                        ) );
                        if ( $places_rating !== null && $places_rating > 0 ) {
                            $out['rating'] = $places_rating;
                            $out['review_count'] = $places_review_count ?? 0;
                        } elseif ( $existing_rating > 0 ) {
                            // Garder la valeur existante si Places API ne retourne rien de valide
                            $out['rating'] = $existing_rating;
                            error_log( '[AVI] Places API rating absent — conservation valeur DB: ' . $existing_rating . ' profil ' . $profile_id );
                        }
                        $out['reviews'] = array();
                        if ( ! empty( $places_body['reviews'] ) && is_array( $places_body['reviews'] ) ) {
                            foreach ( $places_body['reviews'] as $pr ) {
                                if ( ! is_array( $pr ) ) {
                                    continue;
                                }
                                $review_name = isset( $pr['name'] ) ? (string) $pr['name'] : '';
                                $review_id   = '';
                                if ( $review_name !== '' && preg_match( '#/reviews/([^/]+)$#', $review_name, $rm ) ) {
                                    $review_id = $rm[1];
                                }
                                $text_val = '';
                                if ( ! empty( $pr['text']['text'] ) ) {
                                    $text_val = (string) $pr['text']['text'];
                                } elseif ( ! empty( $pr['originalText']['text'] ) ) {
                                    $text_val = (string) $pr['originalText']['text'];
                                }
                                $orig_val = '';
                                if ( ! empty( $pr['originalText']['text'] ) ) {
                                    $orig_val = (string) $pr['originalText']['text'];
                                }
                                $out['reviews'][] = array(
                                    'review_id'    => $review_id,
                                    'rating'       => isset( $pr['rating'] ) ? (int) $pr['rating'] : null,
                                    'text'         => $text_val,
                                    'author'       => isset( $pr['authorAttribution']['displayName'] )
                                        ? (string) $pr['authorAttribution']['displayName']
                                        : '',
                                    'publishTime'  => isset( $pr['publishTime'] ) ? (string) $pr['publishTime'] : '',
                                    'originalText' => $orig_val,
                                );
                            }
                        }
                        error_log(
                            '[AVI] Places API (New) rating=' . ( isset( $out['rating'] ) ? $out['rating'] : 'n/a' )
                            . ' reviews=' . ( isset( $out['review_count'] ) ? $out['review_count'] : 'n/a' )
                            . ' profile_id=' . $profile_id
                        );
                    }
                }
            }
            return $out;
        }

        return $out;
    }

    /**
     * Réputation locale (étoiles + volume d’avis) via Google Business Information API v1.
     * Délègue à fetch_google_business_metrics (même GET ; readMask profile + metadata).
     *
     * @param string $access_token OAuth Google valide.
     * @param string $location_id  ex. accounts/…/locations/…
     * @param int    $profile_id   Contexte log.
     * @return array{rating: ?float, review_count: ?int, api_error?: string}
     */
    public function fetch_gbp_local_reputation( $access_token, $location_id, $profile_id = 0 ) {
        $profile_id = (int) $profile_id;
        $full = $this->fetch_google_business_metrics( $access_token, $location_id, $profile_id );
        $rep  = [
            'rating'       => isset( $full['rating'] ) ? $full['rating'] : null,
            'review_count' => isset( $full['review_count'] ) ? $full['review_count'] : null,
        ];
        if ( isset( $full['api_error'] ) && is_string( $full['api_error'] ) && $full['api_error'] !== '' ) {
            $rep['api_error'] = $full['api_error'];
        }
        return $rep;
    }

    /**
     * Appel DataForSEO Google Maps Live — recherche secteur (keyword + location).
     * Langue selon pays (FR, GB, US, ES) ; location_code selon ville (mapping FR).
     *
     * @param string $keyword Ex. "Plombier Paris"
     * @param string $target_city Ex. "Paris", "Pau"
     * @param string $country Code pays FR, GB, US, ES (défaut FR)
     * @param int    $profile_id Contexte log (0 si inconnu)
     * @return array|\WP_Error ['items' => array of maps_search items] ou WP_Error si clés manquantes
     */
    private function fetch_dataforseo_maps_sector($keyword, $target_city, $country = 'FR', $profile_id = 0) {
        $result = ['items' => []];
        $profile_id = (int) $profile_id;
        if (!function_exists('wp_remote_post') || !function_exists('apply_filters')) {
            error_log('[Avision DataForSEO] Abort: wp_remote_post or apply_filters not available');
            return $result;
        }

        // S10-A : circuit breaker — si ouvert, ne pas appeler DataForSEO (réessayer plus tard).
        if ( $this->is_dataforseo_circuit_open() ) {
            if ( function_exists( 'error_log' ) ) {
                error_log( '[AVI-S10A] Circuit ouvert — appel DataForSEO ignoré profile_id=' . (int) $profile_id . ' keyword="' . (string) $keyword . '"' );
            }
            return new \WP_Error( 'dataforseo_circuit_open', 'DataForSEO circuit breaker is open' );
        }

        $country = strtoupper(trim((string) $country));
        if (!in_array($country, ['FR', 'GB', 'US', 'ES'], true)) {
            $country = 'FR';
        }
        $language_code = ($country === 'GB' || $country === 'US') ? 'en' : ($country === 'ES' ? 'es' : 'fr');

        // 1. Pont credentials : filtre WP + AVISION_* + getenv/$_ENV (voir avision-dataforseo-credentials.php)
        if ( function_exists( 'avision_get_dataforseo_credentials_resolved' ) ) {
            $resolved = \avision_get_dataforseo_credentials_resolved();
            if ( ! is_array( $resolved ) || $resolved['login'] === '' || $resolved['password'] === '' ) {
                error_log( '[Avision DataForSEO] Credentials missing after resolver (filter / constants / env)' );

                return new \WP_Error( 'missing_keys', 'No DataForSEO keys found' );
            }
            $login    = $resolved['login'];
            $password = $resolved['password'];
            $src      = isset( $resolved['source'] ) ? (string) $resolved['source'] : 'resolved';
            error_log( '[Avision DataForSEO] Credentials OK (' . $src . '), calling API' );
        } else {
            $credentials = apply_filters( 'avision_dataforseo_credentials', [] );
            $login       = isset( $credentials['login'] ) ? trim( (string) $credentials['login'] ) : '';
            $password    = isset( $credentials['password'] ) ? trim( (string) $credentials['password'] ) : '';
            $from_constants = false;

            if ( ( $login === '' || $password === '' ) && defined( 'AVISION_DATAFORSEO_LOGIN' ) && defined( 'AVISION_DATAFORSEO_PASSWORD' ) ) {
                $login    = trim( (string) AVISION_DATAFORSEO_LOGIN );
                $password = trim( (string) AVISION_DATAFORSEO_PASSWORD );
                if ( $login !== '' && $password !== '' ) {
                    $from_constants = true;
                    error_log( '[AVI] Credentials source: Constants' );
                }
            }

            if ( $login === '' || $password === '' ) {
                error_log( '[Avision DataForSEO] Credentials missing: filter empty and constants AVISION_DATAFORSEO_LOGIN/PASSWORD missing or empty' );

                return new \WP_Error( 'missing_keys', 'No DataForSEO keys found' );
            }

            error_log( '[Avision DataForSEO] Credentials OK' . ( $from_constants ? ' (constants)' : ' (filter)' ) . ', calling API' );
        }

        $credentials = [ 'login' => $login, 'password' => $password ];
        $city              = $target_city !== '' ? trim( (string) $target_city ) : '';
        // ANTI-RÉGRESSION #188 [23 mai 2026] — Résolution location_code depuis DB cache
        // Table wor1515_avi_dataforseo_locations (224k+ locations mondiales)
        $dfs_location_code = 1006094; // fallback Paris si rien trouvé
        global $wpdb;
        if ( $city !== '' && isset( $wpdb ) && $wpdb ) {
            $city_lower = mb_strtolower( trim( $city ) );
            $country_iso = strtoupper( trim( (string) $country ) );
            if ( $country_iso === '' ) {
                $country_iso = 'FR';
            }
            $loc_table = $wpdb->prefix . 'avi_dataforseo_locations';
            // 1. Correspondance exacte
            $found_code = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT location_code FROM {$loc_table}
         WHERE location_name_lower = %s AND country_iso = %s
         LIMIT 1",
                    $city_lower,
                    $country_iso
                )
            );
            // 2. Correspondance partielle si rien trouvé
            if ( ! $found_code ) {
                $found_code = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT location_code FROM {$loc_table}
             WHERE location_name_lower LIKE %s AND country_iso = %s
             ORDER BY LENGTH(location_name) ASC
             LIMIT 1",
                        '%' . $wpdb->esc_like( $city_lower ) . '%',
                        $country_iso
                    )
                );
            }
            // 3. Fallback sans filtre pays
            if ( ! $found_code ) {
                $found_code = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT location_code FROM {$loc_table}
             WHERE location_name_lower LIKE %s
             ORDER BY LENGTH(location_name) ASC
             LIMIT 1",
                        '%' . $wpdb->esc_like( $city_lower ) . '%'
                    )
                );
            }
            if ( $found_code ) {
                $dfs_location_code = (int) $found_code;
                error_log( '[AVI] location_code résolu depuis DB: "' . $city . '" → ' . $dfs_location_code . ' (' . $country_iso . ')' );
            } else {
                error_log( '[AVI] location_code non trouvé pour "' . $city . '" → fallback 1006094' );
            }
        }
        $post_data = [[
            'keyword'       => $keyword,
            'location_code' => $dfs_location_code,
            'language_code' => $language_code,
            'device'        => 'desktop',
            'depth'         => 20,
        ]];
        error_log( '[AVI] Calling DataForSEO for ' . $keyword . ' location_code=' . $dfs_location_code . ' city=' . $city );
        error_log( '[Avision DataForSEO] POST https://api.dataforseo.com/v3/serp/google/maps/live/advanced keyword="' . $keyword . '" location_code=' . $dfs_location_code );
        $dfs_endpoint = 'https://api.dataforseo.com/v3/serp/google/maps/live/advanced';
        $response = wp_remote_post(
            $dfs_endpoint,
            [
                'headers' => [
                    'Authorization' => 'Basic ' . base64_encode($credentials['login'] . ':' . $credentials['password']),
                    'Content-Type' => 'application/json',
                ],
                'body' => wp_json_encode($post_data),
                'timeout' => 60,
            ]
        );
        if (is_wp_error($response)) {
            error_log('[Avision DataForSEO] wp_remote_post error: ' . $response->get_error_message());
            $this->record_dataforseo_error();
            return $result;
        }
        $code = wp_remote_retrieve_response_code($response);
        $body_raw = wp_remote_retrieve_body($response);
        error_log('[Avision DataForSEO] Response received: HTTP ' . $code . ', body length=' . strlen($body_raw));
        if ($code === 401) {
            error_log('!!! CRITICAL: DataForSEO Auth Failed. Check login/password !!!');
        }
        if ($code !== 200) {
            error_log('[Avision DataForSEO] Non-200 response, first 200 chars: ' . substr($body_raw, 0, 200));
            $this->record_dataforseo_error();
            return new \WP_Error('dataforseo_http_error', 'DataForSEO API returned ' . $code, ['status' => $code]);
        }
        // Succès HTTP : réinitialiser le circuit.
        $this->reset_dataforseo_circuit();
        $data = json_decode($body_raw, true);
        if (empty($data['tasks'][0]['result'][0]['items'])) {
            error_log('[Avision DataForSEO] No items in response (tasks[0].result[0].items empty or missing)');
            return ['items' => []];
        }
        $result0 = isset($data['tasks'][0]['result'][0]) ? $data['tasks'][0]['result'][0] : [];
        $items_count = isset($result0['items_count']) ? $result0['items_count'] : (isset($result0['items']) ? count($result0['items']) : 0);
        error_log('[Avision DataForSEO] 200 OK — tasks[0].result[0].items_count = ' . $items_count);
        foreach ($data['tasks'][0]['result'][0]['items'] as $item) {
            if (isset($item['type']) && $item['type'] === 'maps_search') {
                $result['items'][] = $item;
            }
        }
        error_log('[Avision DataForSEO] Parsed maps_search items: ' . count($result['items']));
        // Toujours renvoyer un tableau ['items' => ...] (jamais false) pour éviter 502 en aval.
        return $result;
    }
}
