<?php
/**
 * Plugin Name: Avision Dashboard (SaaS V9)
 * Plugin URI: https://avision.io
 * Description: Local SEO Audit Tool & Dashboard - Gamified progression system
 * Version: 9.0.3
 * Author: Anthony
 * Text Domain: avision-dashboard
 * Domain Path: /languages
 */

error_log( '--- [BALISE 2] Plugin AVI Chargé --- URL: ' . ( isset( $_SERVER['REQUEST_URI'] ) ? (string) $_SERVER['REQUEST_URI'] : '' ) . ' --- GET: ' . json_encode( isset( $_GET ) ? $_GET : array(), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) );

// Handle standalone mode (Replit) vs WordPress mode
if (!defined('ABSPATH')) {
    define('ABSPATH', dirname(dirname(dirname(dirname(__DIR__)))) . '/');
}

define('AVISION_VERSION', '9.0.3');

/** Table pré-inscription B2C/B2B (nom complet MySQL, sans dépendre du préfixe WP). */
if ( ! defined( 'AVISION_PRE_INSCRIPTION_TABLE' ) ) {
	define( 'AVISION_PRE_INSCRIPTION_TABLE', 'wor1515_avi_pre_inscription' );
}

/**
 * Retour Stripe / dashboard : activer le bypass des redirections forcées vers /connexion/ (status=success ou pid en query).
 *
 * @return bool
 */
function avision_bypass_force_login_redirects_should_activate() {
	if ( ! isset( $_GET ) || ! is_array( $_GET ) ) {
		return false;
	}
	if ( isset( $_GET['status'] ) && function_exists( 'sanitize_text_field' ) && function_exists( 'wp_unslash' ) ) {
		if ( sanitize_text_field( wp_unslash( (string) $_GET['status'] ) ) === 'success' ) {
			return true;
		}
	}
	if ( isset( $_GET['pid'] ) && is_string( $_GET['pid'] ) && function_exists( 'wp_unslash' ) ) {
		if ( trim( wp_unslash( $_GET['pid'] ) ) !== '' ) {
			return true;
		}
	}
	return false;
}

/**
 * Neutralise les redirections vers la page de connexion pour cette requête (succès paiement / profil en URL).
 *
 * @param string|false $location URL de redirection.
 * @param int          $status   Code HTTP.
 * @return string|false
 */
function avision_bypass_force_login_wp_redirect( $location, $status ) {
	unset( $status );
	if ( empty( $GLOBALS['avision_bypass_force_login'] ) ) {
		return $location;
	}
	if ( false === $location || null === $location ) {
		return $location;
	}
	$location = (string) $location;
	if ( $location === '' ) {
		return $location;
	}
	if ( stripos( $location, 'connexion' ) === false ) {
		return $location;
	}
	if ( function_exists( 'error_log' ) ) {
		error_log( '[AVI BYPASS] Redirection vers connexion neutralisée : ' . $location );
	}
	return false;
}

/**
 * Évite que les liens « connexion » pointent vers /connexion/ pendant le bypass (fallback accueil).
 *
 * @param string $login_url    URL de connexion.
 * @param string $redirect     Redirect cible.
 * @param bool   $force_reauth Force réauth.
 * @return string
 */
function avision_bypass_force_login_login_url( $login_url, $redirect, $force_reauth ) {
	unset( $redirect, $force_reauth );
	if ( empty( $GLOBALS['avision_bypass_force_login'] ) ) {
		return $login_url;
	}
	$login_url = (string) $login_url;
	if ( stripos( $login_url, 'connexion' ) !== false && function_exists( 'home_url' ) ) {
		return home_url( '/' );
	}
	return $login_url;
}

/**
 * Point d’ancrage template_redirect (priorité 0) : le blocage effectif des redirections passe par le filtre wp_redirect.
 *
 * @return void
 */
function avision_bypass_force_login_template_redirect() {
	if ( empty( $GLOBALS['avision_bypass_force_login'] ) ) {
		return;
	}
	// Les redirections vers /connexion/ sont annulées via avision_bypass_force_login_wp_redirect (filtre wp_redirect).
}

/**
 * Enregistre le bypass Force Login si l’URL porte status=success ou pid=.
 *
 * @return void
 */
function avision_bypass_force_login_redirects() {
	try {
		if ( ! avision_bypass_force_login_redirects_should_activate() ) {
			return;
		}
		$GLOBALS['avision_bypass_force_login'] = true;
		if ( function_exists( 'error_log' ) ) {
			error_log( '--- [BALISE 2.5] Bypass Force Login activé ---' );
		}
		add_filter( 'wp_redirect', 'avision_bypass_force_login_wp_redirect', 9999, 2 );
		add_filter( 'login_url', 'avision_bypass_force_login_login_url', 9999, 3 );
		add_action( 'template_redirect', 'avision_bypass_force_login_template_redirect', 0 );
	} catch ( Exception $e ) {
		if ( function_exists( 'error_log' ) ) {
			error_log( '[AVI BYPASS] Exception : ' . $e->getMessage() );
		}
	}
}

add_action( 'init', 'avision_bypass_force_login_redirects', 0 );

/**
 * Table profils SaaS (nom MySQL complet, ex. wor1515_avi_saas_profiles).
 * Optionnel : define( 'AVISION_SAAS_PROFILES_TABLE', 'wor1515_avi_saas_profiles' ); dans wp-config.php
 */

/**
 * Table identité mw_user (nom MySQL complet si préfixe / nom custom).
 * Optionnel : define( 'AVISION_MW_USER_TABLE', 'mw_user' ); ou wor1515_mw_user, etc.
 */

/**
 * Stripe — configuration (tests / prod) sans interface d’admin obligatoire.
 *
 * wp-config.php (recommandé) :
 *   define( 'AVISION_STRIPE_SECRET_KEY', 'sk_test_...' );
 *   define( 'AVISION_STRIPE_PRICE_ID', 'price_...' ); // optionnel : sinon prix dynamique (unit_amount)
 * Ou options WordPress : avision_stripe_secret_key, avision_stripe_price_id
 *
 * Clés test : sk_test_… / pk_test_… utilisent la même API api.stripe.com ; le Checkout hébergé
 * fonctionne dès que la clé secrète test est configurée (le front n’a pas besoin de pk_ pour cette route).
 */

/**
 * Vérifie si la clé secrète Stripe est renseignée (appel API Checkout possible).
 *
 * @return bool
 */
function avision_stripe_secret_key_configured() {
	$const = ( defined( 'AVISION_STRIPE_SECRET_KEY' ) && is_string( AVISION_STRIPE_SECRET_KEY ) ) ? trim( AVISION_STRIPE_SECRET_KEY ) : '';
	$opt   = function_exists( 'get_option' ) ? get_option( 'avision_stripe_secret_key', '' ) : '';
	$opt   = is_string( $opt ) ? trim( $opt ) : '';
	$key   = $const !== '' ? $const : $opt;
	$ok    = is_string( $key ) && trim( $key ) !== '';
	if ( ! $ok ) {
		error_log( "[AVI STRIPE] Erreur : Clé secrète non configurée." );
	}
	return $ok;
}

/**
 * Indique si la clé secrète configurée est une clé de test Stripe.
 *
 * @return bool
 */
function avision_stripe_is_test_mode() {
	$key = defined( 'AVISION_STRIPE_SECRET_KEY' ) && AVISION_STRIPE_SECRET_KEY ? AVISION_STRIPE_SECRET_KEY : get_option( 'avision_stripe_secret_key', '' );
	return is_string( $key ) && strpos( trim( $key ), 'sk_test_' ) === 0;
}

/**
 * Expose au JS un flag Stripe (demo-dashboard).
 * Le thème enqueuse `demo-dashboard-js`; on localise ici pour éviter les divergences.
 */
function avision_localize_demo_dashboard_stripe_config() {
	if ( ! function_exists( 'wp_script_is' ) || ! function_exists( 'wp_localize_script' ) ) {
		return;
	}
	if ( ! wp_script_is( 'demo-dashboard-js', 'enqueued' ) && ! wp_script_is( 'demo-dashboard-js', 'registered' ) ) {
		return;
	}
	error_log( "[AVI DEBUG] Stripe Constant Defined: " . ( defined( 'AVISION_STRIPE_SECRET_KEY' ) ? 'YES' : 'NO' ) );
	$configured = function_exists( 'avision_stripe_secret_key_configured' ) ? avision_stripe_secret_key_configured() : false;
	wp_localize_script(
		'demo-dashboard-js',
		'AvisionStripeConfig',
		array(
			'stripeConfigured' => (bool) $configured,
		)
	);
}
add_action( 'wp_enqueue_scripts', 'avision_localize_demo_dashboard_stripe_config', 30 );

/**
 * Price ID Stripe (Catalogue) — si vide, Checkout utilise price_data + unit_amount.
 *
 * @return string
 */
function avision_stripe_get_price_id() {
	if ( defined( 'AVISION_STRIPE_PRICE_ID' ) && is_string( AVISION_STRIPE_PRICE_ID ) && AVISION_STRIPE_PRICE_ID !== '' ) {
		return AVISION_STRIPE_PRICE_ID;
	}
	$opt = get_option( 'avision_stripe_price_id', '' );
	return is_string( $opt ) && $opt !== '' ? $opt : '';
}

/**
 * Retourne la config d'upgrade in-app (type, montant, mode, labels).
 *
 * @param string $upgrade_type
 * @return array|null
 */
function avision_get_upgrade_checkout_config( $upgrade_type ) {
	$type = is_string( $upgrade_type ) ? strtolower( trim( $upgrade_type ) ) : '';
	$map  = [
		'guardian' => [
			'price_constant' => 'AVISION_STRIPE_PRICE_ID_GUARDIAN',
			'price_option'   => 'avision_stripe_price_id_guardian',
			'unit_amount'    => 2900,
			'mode'           => 'subscription',
			'name'           => 'AVI — Mode Gardien',
			'description'    => 'Protection continue de la fiche Google Business Profile',
		],
		'keyword_75' => [
			'price_constant' => 'AVISION_STRIPE_PRICE_ID_KEYWORD_75',
			'price_option'   => 'avision_stripe_price_id_keyword_75',
			'unit_amount'    => 700,
			'mode'           => 'subscription',
			'name'           => 'AVI — Radar Hebdomadaire (75 mots-cles)',
			'description'    => 'Scan hebdomadaire prioritaire (75 mots-cles)',
		],
		'daily_elite' => [
			'price_constant' => 'AVISION_STRIPE_PRICE_ID_DAILY_ELITE',
			'price_option'   => 'avision_stripe_price_id_daily_elite',
			'unit_amount'    => 1500,
			'mode'           => 'subscription',
			'name'           => 'AVI — Radar Journalier Elite (100 mots-cles)',
			'description'    => 'Scan journalier domination (100 mots-cles)',
		],
	];

	if ( ! isset( $map[ $type ] ) ) {
		return null;
	}

	$cfg = $map[ $type ];
	$pid = '';
	if ( ! empty( $cfg['price_constant'] ) && defined( $cfg['price_constant'] ) && is_string( constant( $cfg['price_constant'] ) ) ) {
		$pid = trim( (string) constant( $cfg['price_constant'] ) );
	}
	if ( $pid === '' && ! empty( $cfg['price_option'] ) ) {
		$opt = get_option( $cfg['price_option'], '' );
		if ( is_string( $opt ) ) {
			$pid = trim( $opt );
		}
	}

	$cfg['price_id'] = ( strpos( $pid, 'price_' ) === 0 ) ? $pid : '';
	return $cfg;
}

/**
 * URL de succès par défaut : page cockpit avec pre + session_id Stripe (Stripe remplace {CHECKOUT_SESSION_ID}).
 * Redirection finale : cookie SaaS + ?pid= (UUID) après validation (voir avision_handle_stripe_checkout_landing).
 * Filtre : avision_stripe_checkout_success_url pour surcharger l’URL complète.
 *
 * @param string $pre_token Token pré-inscription (temp_token).
 * @return string URL avec placeholder {CHECKOUT_SESSION_ID} pour Stripe.
 */
function avision_stripe_build_default_success_url( $pre_token, $landing_slug = null ) {
	$pre_token = is_string( $pre_token ) ? trim( $pre_token ) : '';
	$slug      = $landing_slug !== null && is_string( $landing_slug ) && $landing_slug !== ''
		? trim( $landing_slug, '/' )
		: null;
	if ( $slug === null || $slug === '' ) {
		$slug = apply_filters( 'avision_stripe_success_landing_slug', 'premium-dashboard', $pre_token );
	}
	// Pont de succès : on atterrit directement sur le Dashboard Premium avec session_id Stripe,
	// sans passer par /connexion/ (auto-login géré via hook init).
	$slug = is_string( $slug ) && $slug !== '' ? trim( $slug, '/' ) : 'premium-dashboard';
	if ( $slug !== 'premium-dashboard' ) {
		// Par sécurité : pour l'instant, on force Premium (B2C) comme demandé.
		$slug = 'premium-dashboard';
	}
	$base = trailingslashit( home_url( '/' . $slug . '/' ) );
	$base = add_query_arg(
		array(
			'status'     => 'success',
			'session_id' => '{CHECKOUT_SESSION_ID}',
		),
		$base
	);
	return $base;
}

// Use WordPress function if available, otherwise use dirname
if (function_exists('plugin_dir_path')) {
    define('AVISION_PATH', plugin_dir_path(__FILE__));
    define('AVISION_PLUGIN_DIR', plugin_dir_path(__FILE__));
} else {
    define('AVISION_PATH', __DIR__ . '/');
    define('AVISION_PLUGIN_DIR', __DIR__ . '/');
}
// Chemin includes/ fiable (__DIR__ = racine du plugin ici)
if ( ! defined( 'AVISION_INCLUDES_DIR' ) ) {
    define( 'AVISION_INCLUDES_DIR', __DIR__ . DIRECTORY_SEPARATOR . 'includes' . DIRECTORY_SEPARATOR );
}

if (function_exists('plugin_dir_url')) {
    define('AVISION_URL', plugin_dir_url(__FILE__));
    define('AVISION_PLUGIN_URL', plugin_dir_url(__FILE__));
} else {
    define('AVISION_URL', '/wordpress/wp-content/plugins/avision-dashboard/');
    define('AVISION_PLUGIN_URL', '/wordpress/wp-content/plugins/avision-dashboard/');
}

// Autoloader Avision "blindé" — deux formats : class-*.php et *.php (ex. cron-task-manager.php)
spl_autoload_register( function ( $class ) {
	$class = ltrim( $class, '\\' );
	$prefix = 'Avision\\';
	$base_dir = plugin_dir_path( __FILE__ ) . 'includes/';
	$len = strlen( $prefix );
	if ( strncmp( $prefix, $class, $len ) !== 0 ) {
		return;
	}
	$relative_class = substr( $class, $len );
	// Format 1 : class-nom-de-la-classe.php
	$file1 = $base_dir . 'class-' . strtolower( str_replace( '_', '-', $relative_class ) ) . '.php';
	// Format 2 : nom-de-la-classe.php (pour cron-task-manager)
	$file2 = $base_dir . strtolower( str_replace( '_', '-', $relative_class ) ) . '.php';
	if ( file_exists( $file1 ) ) {
		require_once $file1;
	} elseif ( file_exists( $file2 ) ) {
		require_once $file2;
	}
} );

// Fichiers non autoloadés (scripts d’installation, routeur global, core legacy)
require_once AVISION_PATH . 'includes/class-ajax-router.php';
require_once AVISION_PATH . 'includes/class-avision-draft.php';
if ( file_exists( plugin_dir_path( __FILE__ ) . 'install-avi-core-tables.php' ) ) {
	require_once plugin_dir_path( __FILE__ ) . 'install-avi-core-tables.php';
}
if ( file_exists( plugin_dir_path( __FILE__ ) . 'includes/class-avision-stripe-webhook.php' ) ) {
	require_once plugin_dir_path( __FILE__ ) . 'includes/class-avision-stripe-webhook.php';
}
if ( file_exists( plugin_dir_path( __FILE__ ) . 'includes/avision-simulate-stripe-success.php' ) ) {
	error_log( '--- [DEBUG] Tentative d\'inclusion du simulateur... ---' );
	require_once plugin_dir_path( __FILE__ ) . 'includes/avision-simulate-stripe-success.php';
	error_log( '--- [DEBUG] Simulateur inclus avec succès. ---' );
}

/**
 * Lance immédiatement la Wave 3 (Mainteneurs) pour un profil dès que le statut passe à Premium.
 * À appeler après activation du Mode Gardien (REST ou paiement) pour ne pas attendre le cycle CRON.
 *
 * @param int $profile_id ID du profil (avi_saas_profiles.id). En contexte REST, audit_id peut être utilisé comme profile_id.
 */
function avision_trigger_immediate_wave3( $profile_id ) {
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 ) {
		return;
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return;
	}
	$profile = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $profile_id ), ARRAY_A );
	if ( ! is_array( $profile ) || empty( $profile['user_id'] ) ) {
		return;
	}
	$user_id = (int) $profile['user_id'];
	$wpdb->update(
		$table,
		[ 'guardian_active' => 1 ],
		[ 'id' => $profile_id ],
		[ '%d' ],
		[ '%d' ]
	);
	// Passer un profil avec guardian_active = 1 pour que run_wave3 déclenche le Cold Start
	// (premier post Présentation + image Unsplash si galerie vide). Sans cela, $profile chargé
	// avant l'update aurait guardian_active = 0 et le Cold Start ne s'exécuterait pas.
	$profile['guardian_active'] = 1;
	if ( class_exists( 'Avision\Avision_Guardian_Maintainers' ) ) {
		$maintainers = new Avision\Avision_Guardian_Maintainers();
		$maintainers->run_wave3( $user_id, $profile );
	}
}

function avision_init() {
    // Force clean : forcer PHP à relire les fichiers (invalidation OPcache).
    if ( function_exists( 'opcache_reset' ) ) {
        opcache_reset();
    }
    // Nettoyage forcé : aucun ancien réglage ne doit polluer la génération (exorcisme texte/image).
    delete_option( 'avi_default_post_text' );
    delete_transient( 'avision_cold_start_lock_1' );

    if (class_exists('Avision\\Avision_Main')) {
        $avision = new Avision\Avision_Main();
        $avision->run();
    }
    if (class_exists('Avision\\Avision_Cron')) {
        new Avision\Avision_Cron();
    }
    if (class_exists('Avision\\Cron_Task_Manager')) {
        new Avision\Cron_Task_Manager();
    }
    add_shortcode( 'avision_audit_london_test', 'avision_render_local_audit_form_shortcode' );
    add_shortcode( 'avision_onboarding', 'avision_render_onboarding_shortcode' );
    // Clé OpenRouter : priorité à la constante (chargée depuis .env) sur get_option
    add_filter( 'avision_openrouter_api_key', function ( $value ) {
        if ( defined( 'AVISION_OPENROUTER_API_KEY' ) && AVISION_OPENROUTER_API_KEY !== '' ) {
            return AVISION_OPENROUTER_API_KEY;
        }
        return $value;
    }, 1 );
}

// Callbacks REST autonomes pour pending-posts / pending-reviews (éviter 404 si chargement Guardian_REST_API différé)
function avision_rest_pending_posts( \WP_REST_Request $request ) {
	$audit_id = (int) $request->get_param( 'audit_id' );
	if ( $audit_id <= 0 ) {
		return new \WP_REST_Response( [ 'success' => true, 'pending' => [], 'first_media_url' => null ], 200 );
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_posts';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return new \WP_REST_Response( [ 'success' => true, 'pending' => [], 'first_media_url' => null ], 200 );
	}
	$rows = $wpdb->get_results( $wpdb->prepare(
		"SELECT id, title, content, status, created_at, post_type FROM `{$table}` WHERE profile_id = %d AND status IN ('draft', 'pending_review') ORDER BY created_at DESC LIMIT 20",
		$audit_id
	), ARRAY_A );
	if ( ! is_array( $rows ) ) {
		$rows = [];
	}
	$profile_city = '';
	$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t_profiles ) ) === $t_profiles ) {
		$profile_row = $wpdb->get_row( $wpdb->prepare( "SELECT city FROM `{$t_profiles}` WHERE id = %d LIMIT 1", $audit_id ), ARRAY_A );
		if ( ! empty( $profile_row['city'] ) ) {
			$profile_city = (string) $profile_row['city'];
		}
	}
	$t_media = $wpdb->prefix . 'avi_saas_media';
	$first_media_url = null;
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t_media ) ) === $t_media ) {
		$first_media = $wpdb->get_row( $wpdb->prepare(
			"SELECT file_path FROM `{$t_media}` WHERE profile_id = %d AND status = 'in_stock' ORDER BY created_at DESC LIMIT 1",
			$audit_id
		), ARRAY_A );
		if ( ! empty( $first_media['file_path'] ) ) {
			$first_media_url = (string) $first_media['file_path'];
		}
		// Fallback : si des brouillons existent mais aucun média pour ce profil, prendre le dernier in_stock (Cold Start possible avec profil unique)
		if ( $first_media_url === null && count( $rows ) > 0 ) {
			$fallback = $wpdb->get_row(
				"SELECT file_path FROM `{$t_media}` WHERE status = 'in_stock' ORDER BY created_at DESC LIMIT 1",
				ARRAY_A
			);
			if ( ! empty( $fallback['file_path'] ) ) {
				$first_media_url = (string) $fallback['file_path'];
			}
		}
	}
	$cold_start_title = 'Présentation — Bienvenue';
	$pending = [];
	foreach ( $rows as $i => $r ) {
		$title   = isset( $r['title'] ) ? (string) $r['title'] : 'Post proposé';
		$content = isset( $r['content'] ) ? (string) $r['content'] : '';
		$ptype   = isset( $r['post_type'] ) ? (string) $r['post_type'] : 'update';
		$item = [
			'post_id'       => (int) ( $r['id'] ?? 0 ),
			'id'            => (int) ( $r['id'] ?? 0 ),
			'title'         => $title,
			'content'       => $content,
			'summary'       => wp_trim_words( $content, 30 ),
			'status'        => isset( $r['status'] ) ? (string) $r['status'] : 'draft',
			'created_at'    => isset( $r['created_at'] ) ? (string) $r['created_at'] : null,
			'is_cold_start' => ( $title === $cold_start_title ),
			'city'          => $profile_city,
			'post_type'     => $ptype,
		];
		if ( $ptype === 'testimonial' || ( strpos( $title, 'Témoignage Client' ) !== false ) ) {
			$item['is_testimonial']   = true;
			$item['rating']          = 5;
			$item['review_text']     = '';
			$item['reviewer_name']   = 'Client';
			$item['response_text']   = '';
			if ( preg_match( '/[«"]([^»"]+)[»"]/u', $content, $m ) ) {
				$item['review_text'] = trim( $m[1] );
			}
			if ( preg_match( '/—\s*([^\n.]+)/u', $title, $mName ) ) {
				$item['reviewer_name'] = trim( $mName[1], " \t\n\r." );
			} elseif ( preg_match( '/—\s*([^\n]+)/u', $content, $mName ) ) {
				$item['reviewer_name'] = trim( $mName[1], " \t\n\r." );
			}
			$response_block = $content;
			if ( preg_match( '/—\s*[^\n]+\s*\n\s*\n(.*)/us', $content, $mResp ) ) {
				$response_block = trim( $mResp[1] );
			}
			$item['response_text'] = preg_replace( '/\s*#\w+(\s*#\w+)*\s*$/u', '', $response_block );
			$item['response_text'] = trim( $item['response_text'] );
		}
		if ( $i === 0 && $first_media_url !== null ) {
			$item['image_url'] = $first_media_url;
		}
		$pending[] = $item;
	}
	return new \WP_REST_Response( [ 'success' => true, 'pending' => $pending, 'first_media_url' => $first_media_url, 'profile_city' => $profile_city ], 200 );
}

function avision_rest_pending_reviews( \WP_REST_Request $request ) {
	$audit_id = (int) $request->get_param( 'audit_id' );
	if ( $audit_id <= 0 ) {
		return new \WP_REST_Response( [ 'success' => true, 'pending' => [] ], 200 );
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_review_responses';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT id, google_review_id, reviewer_name, rating, review_text, response_text, status, created_at FROM `{$table}` WHERE profile_id = %d AND status IN ('draft', 'pending_review', 'approved') ORDER BY created_at DESC LIMIT 50",
			$audit_id
		), ARRAY_A );
		if ( is_array( $rows ) && count( $rows ) > 0 ) {
			$pending = array_map( function ( $r ) {
				return [
					'review_id'      => isset( $r['google_review_id'] ) ? (string) $r['google_review_id'] : '',
					'reviewer_name'  => isset( $r['reviewer_name'] ) ? (string) $r['reviewer_name'] : '',
					'rating'         => (int) ( $r['rating'] ?? 0 ),
					'review_text'    => isset( $r['review_text'] ) ? (string) $r['review_text'] : '',
					'response_text'  => isset( $r['response_text'] ) ? (string) $r['response_text'] : '',
					'avi_justification' => '',
					'received_at'    => isset( $r['created_at'] ) ? (string) $r['created_at'] : null,
				];
			}, $rows );
			return new \WP_REST_Response( [ 'success' => true, 'pending' => $pending ], 200 );
		}
	}
	if ( class_exists( 'Avision\Guardian_REST_API' ) ) {
		$api = new Avision\Guardian_REST_API();
		return $api->get_pending_reviews( $request );
	}
	return new \WP_REST_Response( [ 'success' => true, 'pending' => [] ], 200 );
}

/** Archive un post (refus sans publication) : status → rejected. */
function avision_rest_post_archive( \WP_REST_Request $request ) {
	$audit_id  = (int) $request->get_param( 'audit_id' );
	$post_id   = (int) $request->get_param( 'post_id' );
	if ( $audit_id <= 0 || $post_id <= 0 ) {
		return new \WP_REST_Response( [ 'success' => false, 'message' => 'Invalid parameters' ], 400 );
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_posts';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return new \WP_REST_Response( [ 'success' => false ], 404 );
	}
	$updated = $wpdb->update(
		$table,
		[ 'status' => 'rejected' ],
		[ 'id' => $post_id, 'profile_id' => $audit_id ],
		[ '%s' ],
		[ '%d', '%d' ]
	);
	return new \WP_REST_Response( [ 'success' => $updated !== false ], 200 );
}

/** Publie un post via le handler Guardian (Google Business Profile). */
function avision_rest_publish_post( \WP_REST_Request $request ) {
	if ( class_exists( 'Avision\Guardian_REST_API' ) ) {
		$api = new Avision\Guardian_REST_API();
		return $api->publish_post( $request );
	}
	return new \WP_REST_Response( [ 'success' => false, 'error' => 'guardian_api_unavailable' ], 503 );
}

/**
 * Synchronise les avis Google vers avi_review_responses (lance la Wave2 pour un profil).
 * Utile pour remonter les avis après un dépôt sur la fiche GBP (cron ou appel manuel).
 *
 * @param \WP_REST_Request $request audit_id (profile_id)
 * @return \WP_REST_Response { success, synced, error? }
 */
function avision_rest_sync_reviews( \WP_REST_Request $request ) {
	$audit_id = (int) $request->get_param( 'audit_id' );
	if ( $audit_id <= 0 ) {
		return new \WP_REST_Response( [ 'success' => false, 'synced' => 0, 'error' => 'audit_id requis' ], 400 );
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_profiles';
	$profile = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $audit_id ), ARRAY_A );
	if ( ! $profile || empty( $profile['user_id'] ) ) {
		return new \WP_REST_Response( [ 'success' => false, 'synced' => 0, 'error' => 'Profil introuvable' ], 404 );
	}
	if ( ! class_exists( 'Avision\Avision_Guardian_Creators' ) ) {
		return new \WP_REST_Response( [ 'success' => false, 'synced' => 0, 'error' => 'Guardian_Creators indisponible' ], 500 );
	}
	$user_id = (int) $profile['user_id'];
	$creators = new Avision\Avision_Guardian_Creators();
	$wave2 = $creators->run_wave2( $user_id, $profile );
	$synced = (int) ( ( $wave2['proofs'] ?? 0 ) + ( $wave2['responses'] ?? 0 ) + ( $wave2['crises'] ?? 0 ) );
	return new \WP_REST_Response( [ 'success' => true, 'synced' => $synced, 'proofs' => $wave2['proofs'] ?? 0, 'responses' => $wave2['responses'] ?? 0, 'crises' => $wave2['crises'] ?? 0 ], 200 );
}

/**
 * Résout un profile_id depuis la requête REST (profile_id, audit_id ou pid UUID).
 *
 * @param \WP_REST_Request $request
 * @return int
 */
function avision_rest_extract_profile_id_from_request( \WP_REST_Request $request ) {
	$profile_id = (int) $request->get_param( 'profile_id' );
	if ( $profile_id <= 0 ) {
		$profile_id = (int) $request->get_param( 'audit_id' );
	}
	if ( $profile_id > 0 ) {
		return $profile_id;
	}

	$pid = $request->get_param( 'pid' );
	if ( is_string( $pid ) && preg_match( '/^[a-f0-9\-]{32,36}$/i', $pid ) && function_exists( 'avision_get_profile_id_by_uuid' ) ) {
		$resolved = (int) avision_get_profile_id_by_uuid( sanitize_text_field( $pid ) );
		if ( $resolved > 0 ) {
			return $resolved;
		}
	}
	return 0;
}

/**
 * Gate REST multi-tenant: autorise uniquement le propriétaire du profil (ou admin).
 * Si aucune cible profil n'est fournie, laisse passer (routes publiques onboarding/checkout, etc.).
 *
 *
 * @param \WP_REST_Request $request
 * @return bool|\WP_Error
 */
function avi_rest_check_profile_access( \WP_REST_Request $request ) {
	if ( current_user_can( 'manage_options' ) ) {
		return true;
	}

	$profile_id = avision_rest_extract_profile_id_from_request( $request );
	if ( $profile_id <= 0 ) {
		// Route publique ou sans cible profile explicite.
		return true;
	}

	// Vérification centralisée si disponible (source de vérité du projet).
	if ( function_exists( 'avi_current_user_can_access_profile' ) ) {
		if ( avi_current_user_can_access_profile( $profile_id ) ) {
			return true;
		}
		return new \WP_Error( 'forbidden_profile_access', 'Accès interdit à ce profil', [ 'status' => 403 ] );
	}

	// Fallback strict via session SaaS.
	$session = function_exists( 'avision_validate_session' ) ? avision_validate_session() : null;
	$session_user_id = ( is_array( $session ) && ! empty( $session['user_id'] ) ) ? (int) $session['user_id'] : 0;
	if ( $session_user_id <= 0 ) {
		return new \WP_Error( 'forbidden_profile_access', 'Session invalide', [ 'status' => 403 ] );
	}

	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return new \WP_Error( 'forbidden_profile_access', 'Table profils indisponible', [ 'status' => 503 ] );
	}
	$owner_user_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT user_id FROM `{$table}` WHERE id = %d LIMIT 1", $profile_id ) );
	if ( $owner_user_id > 0 && $owner_user_id === $session_user_id ) {
		return true;
	}

	return new \WP_Error( 'forbidden_profile_access', 'Accès interdit à ce profil', [ 'status' => 403 ] );
}

// Enregistrer les routes REST API du Mode Gardien + audit-register (liaison formulaire local-seo → SaaS)
function avision_register_rest_routes() {
	if ( ! class_exists( '\\Avision\\Guardian_REST_API' ) ) {
		return;
	}
	error_log( '[AVI INFO] Registering REST routes' );
	// Enregistrer en premier les routes Centre d'intervention (pending-posts / pending-reviews) pour éviter 404
	register_rest_route( 'avision/v1', '/guardian/pending-posts', [
		'methods'             => 'GET',
		'callback'            => 'avision_rest_pending_posts',
		'permission_callback' => 'avi_rest_check_profile_access',
		'args'                => [ 'audit_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ] ],
	] );
	register_rest_route( 'avision/v1', '/guardian/pending-reviews', [
		'methods'             => 'GET',
		'callback'            => 'avision_rest_pending_reviews',
		'permission_callback' => 'avi_rest_check_profile_access',
		'args'                => [ 'audit_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ] ],
	] );
	register_rest_route( 'avision/v1', '/guardian/post-archive', [
		'methods'             => 'POST',
		'callback'            => 'avision_rest_post_archive',
		'permission_callback' => 'avi_rest_check_profile_access',
		'args'                => [
			'audit_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
			'post_id'  => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
		],
	] );
	register_rest_route( 'avision/v1', '/guardian/publish-post', [
		'methods'             => 'POST',
		'callback'            => 'avision_rest_publish_post',
		'permission_callback' => 'avi_rest_check_profile_access',
		'args'                => [
			'audit_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
			'post_id'  => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
		],
	] );
	register_rest_route( 'avision/v1', '/guardian/sync-reviews', [
		'methods'             => 'POST',
		'callback'            => 'avision_rest_sync_reviews',
		'permission_callback' => 'avi_rest_check_profile_access',
		'args'                => [ 'audit_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ] ],
	] );

	$guardian_api = new Avision\Guardian_REST_API();
	$guardian_api->register_routes();

    register_rest_route('avision/v1', '/audit-register', [
        'methods' => 'POST',
        'callback' => 'avision_rest_audit_register',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args' => [
            'email' => ['required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_email'],
            'business_name' => ['required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field'],
            'city' => ['required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field'],
            'category' => ['required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field'],
            'neighborhood' => ['required' => false, 'type' => 'string', 'default' => '', 'sanitize_callback' => 'sanitize_text_field'],
            'country' => ['required' => false, 'type' => 'string', 'default' => 'FR', 'sanitize_callback' => 'sanitize_text_field'],
            'language' => ['required' => false, 'type' => 'string', 'default' => 'en', 'sanitize_callback' => 'sanitize_text_field'],
            'score' => ['required' => false, 'type' => 'integer', 'default' => null],
        ],
    ]);

    register_rest_route( 'avision/v1', '/onboarding-save', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_onboarding_save',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'type'          => [ 'required' => true, 'type' => 'string', 'enum' => [ 'b2c', 'b2b' ], 'sanitize_callback' => 'sanitize_text_field' ],
            'email'         => [ 'required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_email' ],
            'business_name' => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'city'          => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'country'       => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'neighborhood'  => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'category'      => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'agency_name'   => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'contact_name'  => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'password'         => [ 'required' => false, 'type' => 'string' ],
            'password_confirm' => [ 'required' => false, 'type' => 'string' ],
            'volume_fiches' => [ 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
        ],
    ] );

    register_rest_route( 'avision/v1', '/stripe-create-checkout-session', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_stripe_create_checkout_session',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'pre_token'        => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'email'            => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_email' ],
            'rid'              => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'report_id'        => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'addon_guardian'   => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'addon_radar'      => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'upgrade_type'     => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
            'profile_id'       => [ 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
        ],
    ] );

    register_rest_route( 'avision/v1', '/connexion-verify', [
        'methods'             => 'GET',
        'callback'            => 'avision_rest_connexion_verify',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'session_id' => [ 'required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
        ],
    ] );

    register_rest_route( 'avision/v1', '/first-sync', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_first_sync',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'profile_id'      => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
            'pid'             => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
        ],
    ] );

    // Libère le navigateur immédiatement après envoi de "sync_started" (évite 502 / timeout proxy).
    add_filter( 'rest_pre_serve_request', function ( $served, $response, $request, $server ) {
        if ( ! $request || strpos( $request->get_route(), 'first-sync' ) === false ) {
            return $served;
        }
        if ( ! $response instanceof \WP_REST_Response ) {
            return $served;
        }
        $data = $response->get_data();
        $code = $response->get_status();
        if ( $code !== 200 || empty( $data['status'] ) || $data['status'] !== 'sync_started' ) {
            return $served;
        }
        if ( ! headers_sent() ) {
            header( 'Content-Type: application/json; charset=' . get_option( 'blog_charset' ) );
            status_header( $code );
        }
        echo wp_json_encode( $data );
        if ( function_exists( 'fastcgi_finish_request' ) ) {
            fastcgi_finish_request();
        }
        $profile_id = (int) $request->get_param( 'profile_id' );
        if ( $profile_id > 0 ) {
            error_log( '[Avision First Sync] After fastcgi_finish_request: starting run_initial_sync for profile_id=' . $profile_id );
            $sync = avi_run_initial_sync( $profile_id );
            if ( $sync === false ) {
                error_log( '[Avision First Sync] run_initial_sync failed for profile_id=' . $profile_id );
            } else {
                $calc = avi_run_visibility_calculation( $profile_id );
                error_log( '[Avision First Sync] Completed for profile_id=' . $profile_id . ', visibility_calc=' . ( $calc !== false ? 'ok' : 'fail' ) );
            }
        }
        return true;
    }, 10, 4 );

    register_rest_route( 'avision/v1', '/radar-keywords', [
        'methods'             => 'GET',
        'callback'            => 'avision_rest_radar_keywords',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'profile_id'     => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
            'limit'          => [ 'required' => false, 'type' => 'integer', 'default' => 75, 'sanitize_callback' => 'absint' ],
            'force'          => [ 'required' => false, 'type' => 'boolean', 'default' => false ],
            'pid'            => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
        ],
    ] );

    register_rest_route( 'avision/v1', '/add-keywords-batch', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_add_keywords_batch',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'profile_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
            'keywords'   => [ 'required' => true, 'type' => 'array', 'items' => [ 'type' => 'string' ] ],
        ],
    ] );

    register_rest_route( 'avision/v1', '/delete-keyword', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_delete_keyword',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'profile_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
            'keyword'    => [ 'required' => true, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
        ],
    ] );

    // CRON : statut et dernier passage pour une fiche (admin ou propriétaire du profil)
    register_rest_route( 'avision/v1', '/cron-status', [
        'methods'             => 'GET',
        'callback'            => 'avision_rest_cron_status',
        'permission_callback' => 'avi_rest_check_profile_access',
        'args'                => [
            'profile_id' => [ 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
            'pid'        => [ 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ],
        ],
    ] );

    // CRON : lancer l'orchestrateur pour une fiche (admin uniquement)
    register_rest_route( 'avision/v1', '/cron-run-profile', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_cron_run_profile',
        'permission_callback' => function () { return current_user_can( 'manage_options' ); },
        'args'                => [
            'profile_id' => [ 'required' => true, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
        ],
    ] );

    // Scan de démo (même flux que admin.php?page=avi-install-core-tables&avi_demo_scan=1) — admin uniquement
    register_rest_route( 'avision/v1', '/demo-scan', [
        'methods'             => 'POST',
        'callback'            => 'avision_rest_demo_scan',
        'permission_callback' => function () { return current_user_can( 'manage_options' ); },
        'args'                => [
            'profile_id' => [ 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ],
        ],
    ] );
}

/**
 * Shortcode [avision_local_audit_form] : formulaire onboarding avec Quartier (pour pages en Snippet / shortcode).
 * Affiche le formulaire complet (business_name, city, neighborhood, category, email) et envoie vers audit-register.
 */
function avision_render_local_audit_form_shortcode() {
    error_log( '[AVI] TEST SHORTCODE ACTIVE' );
    ob_start();
    if ( file_exists( AVISION_PATH . 'templates/shortcode-local-audit-form.php' ) ) {
        echo "";
        error_log( '[AVI] Shortcode inclusion attempt in: ' . __FILE__ );
        include AVISION_PATH . 'templates/shortcode-local-audit-form.php';
    } else {
        echo '<p class="avision-error">Formulaire onboarding introuvable.</p>';
    }
    return ob_get_clean();
}

/**
 * Shortcode [avision_onboarding] : tunnels B2C et B2B (pré-inscription, liée au PID).
 * Affiche le template templates/onboarding.php.
 */
function avision_render_onboarding_shortcode() {
    ob_start();
    if ( file_exists( AVISION_PATH . 'templates/onboarding.php' ) ) {
        include AVISION_PATH . 'templates/onboarding.php';
    } else {
        echo '<p class="avision-error">Template onboarding introuvable.</p>';
    }
    return ob_get_clean();
}

add_action( 'wp_enqueue_scripts', 'avision_enqueue_local_audit_form_styles_when_shortcode' );
function avision_enqueue_local_audit_form_styles_when_shortcode() {
    if ( ! is_singular() ) {
        return;
    }
    $post = get_post();
    if ( ! $post || ( ! has_shortcode( $post->post_content, 'avision_local_audit_form' ) && ! has_shortcode( $post->post_content, 'avision_audit_london_test' ) ) ) {
        return;
    }
    $url = defined( 'AVISION_PLUGIN_URL' ) ? AVISION_PLUGIN_URL : plugin_dir_url( __FILE__ );
    wp_enqueue_style(
        'avision-local-audit-form',
        $url . 'assets/css/local-seo.css',
        array(),
        defined( 'AVISION_VERSION' ) ? AVISION_VERSION : '9.0.3'
    );
}

/**
 * Liaison formulaire audit local-seo → tables SaaS.
 * Crée ou récupère l'utilisateur (avi_saas_users), crée le profil (avi_saas_profiles),
 * crée une session (avi_saas_sessions) et définit le cookie avision_session_token.
 * Retourne sid pour redirection /demo-dashboard/?sid=token et promotion_expires_at.
 */
function avision_rest_audit_register(\WP_REST_Request $request) {
    if (!class_exists('Avision\Avision_Database')) {
        return new \WP_REST_Response(['success' => false, 'error' => 'Database unavailable'], 503);
    }
    $email = $request->get_param('email');
    $business_name = $request->get_param('business_name');
    $city = $request->get_param('city');
    $category = $request->get_param('category');
    $neighborhood = $request->get_param('neighborhood');
    $country = $request->get_param('country');
    $score = $request->get_param('score');
    if (empty($email) || !is_email($email)) {
        return new \WP_REST_Response(['success' => false, 'error' => 'invalid_email'], 400);
    }
    $db = new \Avision\Avision_Database();
    $db->init();
    $user_id = $db->get_or_create_saas_user($email);
    if ($user_id === null) {
        return new \WP_REST_Response(['success' => false, 'error' => 'user_create_failed'], 500);
    }
    $score_int = $score !== null && $score !== '' ? (int) $score : null;
    $neighborhood_str = $neighborhood !== null ? trim((string) $neighborhood) : '';
    $country_code = in_array(strtoupper(trim((string) $country)), ['FR', 'GB', 'US', 'ES'], true) ? strtoupper(trim((string) $country)) : 'FR';
    $profile = $db->create_saas_profile($user_id, $business_name, $city, $category, null, $score_int, $neighborhood_str, $country_code);
    if ($profile === null) {
        return new \WP_REST_Response(['success' => false, 'error' => 'profile_create_failed'], 500);
    }
    if (function_exists('avi_generate_initial_keywords')) {
        avi_generate_initial_keywords($profile['id']);
    }
    $session = $db->create_saas_session($user_id, $profile['id'], 604800); // 7 jours
    if (!$session) {
        return new \WP_REST_Response(['success' => false, 'error' => 'session_create_failed'], 500);
    }
    $promotion_expires_at = isset($profile['promotion_expires_at']) ? $profile['promotion_expires_at'] : gmdate('Y-m-d\TH:i:s\Z', strtotime('+24 hours'));
    if (strpos($promotion_expires_at, 'T') === false && strpos($promotion_expires_at, ' ') !== false) {
        $promotion_expires_at = date('c', strtotime($promotion_expires_at . ' UTC'));
        if ($promotion_expires_at === false) {
            $promotion_expires_at = gmdate('c', strtotime('+24 hours'));
        }
    }
    $response = new \WP_REST_Response([
        'success' => true,
        'user_id' => $user_id,
        'profile_id' => $profile['id'],
        'current_level' => $profile['current_level'],
        'created_at' => $profile['created_at'],
        'promotion_expires_at' => $promotion_expires_at,
        'sid' => $session['token'],
    ], 200);
    $cookie_value = $session['token'];
    $max_age = 604800; // 7 jours
    $secure = is_ssl();
    $response->header('Set-Cookie', sprintf(
        'avision_session_token=%s; path=/; max-age=%d; SameSite=Lax; HttpOnly%s',
        rawurlencode($cookie_value),
        $max_age,
        $secure ? '; Secure' : ''
    ));
    return $response;
}

/**
 * Message d'erreur onboarding (bandeau) : priorité à l'erreur SQL brute ($wpdb->last_error).
 *
 * @param string $fallback Si last_error est vide (ex. code interne).
 * @return string
 */
function avision_onboarding_wpdb_error_msg( $fallback = '' ) {
    global $wpdb;
    $e = isset( $wpdb->last_error ) ? trim( (string) $wpdb->last_error ) : '';
    if ( $e !== '' ) {
        return 'Erreur : ' . $e;
    }
    $fb = is_string( $fallback ) ? trim( $fallback ) : '';
    if ( $fb !== '' ) {
        return 'Erreur : ' . $fb;
    }
    return 'Erreur : (aucun détail SQL)';
}

/**
 * Réponse d'erreur JSON pour le endpoint REST onboarding (ne pas utiliser wp_send_json_error ici).
 *
 * @param string               $message Message affichable.
 * @param int                  $status  Code HTTP.
 * @param array<string, mixed> $extra   Clés additionnelles (ghost, etc.).
 * @return \WP_REST_Response
 */
function avision_rest_onboarding_error_response( $message, $status = 400, $extra = array() ) {
    $data = array_merge(
        array(
            'success' => false,
        ),
        is_array( $extra ) ? $extra : array()
    );
    $msg = is_string( $message ) ? $message : '';
    if ( $msg !== '' ) {
        $data['message'] = $msg;
        $data['error']   = $msg;
    }
    return new \WP_REST_Response( $data, (int) $status );
}

/**
 * Supprime un utilisateur WordPress si besoin (REST onboarding — rollback).
 *
 * @param int $user_id ID wp_users.
 */
function avision_rest_onboarding_maybe_delete_wp_user( $user_id ) {
    $uid = (int) $user_id;
    if ( $uid <= 0 ) {
        return;
    }
    // Janitor strike (B2B test) : aucune suppression de compte WP.
    // if ( ! function_exists( 'wp_delete_user' ) ) {
    // require_once ABSPATH . 'wp-admin/includes/user.php';
    // }
    // if ( function_exists( 'wp_delete_user' ) ) {
    // wp_delete_user( $uid );
    // }
}

/**
 * wp_create_user : échec car l'e-mail est déjà enregistré dans WordPress.
 *
 * @param \WP_Error $wp_error Erreur retournée par wp_create_user.
 * @return bool
 */
function avision_rest_onboarding_is_wp_duplicate_email_error( $wp_error ) {
    if ( ! is_wp_error( $wp_error ) ) {
        return false;
    }
    if ( $wp_error->get_error_code() === 'existing_user_email' ) {
        return true;
    }
    $msg = strtolower( (string) $wp_error->get_error_message() );
    foreach ( array( 'already been registered', 'email already', 'existe déjà', 'déjà utilisé', 'déjà enregistré', 'already exists' ) as $needle ) {
        if ( $needle !== '' && strpos( $msg, $needle ) !== false ) {
            return true;
        }
    }
    return false;
}

/**
 * Supprime les lignes pré-inscription pour cet e-mail (délègue au thème si disponible).
 * Utilisé par l’onboarding REST pour ne pas dépendre du prochain wp_login.
 *
 * @param string $email E-mail normalisé.
 * @return int Lignes supprimées (approximatif).
 */
function avision_pre_inscription_delete_rows_for_email( $email ) {
    if ( function_exists( 'avi_pre_inscription_delete_rows_for_email' ) ) {
        return (int) avi_pre_inscription_delete_rows_for_email( $email );
    }
    global $wpdb;
    $email = trim( (string) $email );
    if ( $email === '' || ! is_email( $email ) ) {
        return 0;
    }
    $candidates = array();
    if ( defined( 'AVISION_PRE_INSCRIPTION_TABLE' ) && is_string( AVISION_PRE_INSCRIPTION_TABLE ) ) {
        $candidates[] = AVISION_PRE_INSCRIPTION_TABLE;
    }
    $candidates[] = 'wor1515_avi_pre_inscription';
    $candidates[] = $wpdb->prefix . 'avi_pre_inscription';
    $deleted      = 0;
    foreach ( array_unique( array_filter( $candidates ) ) as $tbl ) {
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl ) ) !== $tbl ) {
            continue;
        }
        $col = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$tbl}` LIKE %s", 'email' ) );
        if ( $col !== 'email' ) {
            continue;
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table whitelisté.
        $n = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM `{$tbl}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s))",
                $email
            )
        );
        if ( false !== $n ) {
            $deleted += (int) $n;
        }
    }
    return $deleted;
}

/**
 * Bouclier anti-ghost-delete : bloque la suppression d'un profil fraîchement créé (< 5 minutes).
 *
 * @param string $profiles_table Table profils.
 * @param string $email          E-mail cible.
 * @return bool True si suppression bloquée.
 */
function avision_should_block_recent_profile_delete( $profiles_table, $email ) {
    global $wpdb;
    $profiles_table = is_string( $profiles_table ) ? trim( $profiles_table ) : '';
    $email          = trim( (string) $email );
    if ( $profiles_table === '' || $email === '' || ! is_email( $email ) ) {
        return false;
    }
    if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $profiles_table ) ) !== $profiles_table ) {
        return false;
    }
    if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'created_at' ) ) !== 'created_at' ) {
        return false;
    }
    $created_at = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT created_at FROM `{$profiles_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id DESC LIMIT 1",
            $email
        )
    );
    if ( ! empty( $created_at ) ) {
        $created_ts = strtotime( (string) $created_at );
        if ( $created_ts !== false && ( time() - $created_ts ) < 300 ) {
            if ( function_exists( 'error_log' ) ) {
                error_log( 'AVI SHIELD: Deletion blocked for ' . $email . ' (Created less than 5min ago)' );
            }
            return true;
        }
    }
    return false;
}

/**
 * Force la création/synchronisation d'un profil SaaS B2B pour un utilisateur WordPress.
 *
 * @param int    $wp_user_id    ID WordPress.
 * @param string $email         E-mail du compte.
 * @param string $business_name Nom affiché.
 * @param int    $volume_fiches Volume fiches.
 * @return bool
 */
function avision_force_b2b_saas_profile_row( $wp_user_id, $email, $business_name = '', $volume_fiches = 0 ) {
    global $wpdb;
    $wp_user_id = (int) $wp_user_id;
    $email      = sanitize_email( (string) $email );
    if ( $wp_user_id <= 0 || ! is_email( $email ) ) {
        return false;
    }
    $table = 'wor1515_avi_saas_profiles';
    if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
        return false;
    }
    $cols = $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`", 0 );
    $cols = is_array( $cols ) ? $cols : array();
    $now  = current_time( 'mysql' );
    $bn   = trim( (string) $business_name );
    if ( $bn === '' ) {
        $u  = get_userdata( $wp_user_id );
        $bn = ( $u && ! empty( $u->display_name ) ) ? (string) $u->display_name : current( explode( '@', $email ) );
    }
    $update = array();
    if ( in_array( 'user_id', $cols, true ) ) { $update['user_id'] = $wp_user_id; }
    if ( in_array( 'email', $cols, true ) ) { $update['email'] = $email; }
    if ( in_array( 'user_type', $cols, true ) ) { $update['user_type'] = 'agency'; }
    if ( in_array( 'radar_plan', $cols, true ) ) { $update['radar_plan'] = 'agency_tier1'; }
    if ( in_array( 'business_name', $cols, true ) ) { $update['business_name'] = $bn; }
    if ( in_array( 'category', $cols, true ) ) { $update['category'] = 'agency'; }
    if ( in_array( 'city', $cols, true ) ) { $update['city'] = 'N/A'; }
    if ( in_array( 'neighborhood', $cols, true ) ) { $update['neighborhood'] = 'N/A'; }
    if ( in_array( 'updated_at', $cols, true ) ) { $update['updated_at'] = $now; }
    if ( in_array( 'created_at', $cols, true ) ) { $update['created_at'] = $now; }
    if ( $volume_fiches > 0 && in_array( 'max_keywords', $cols, true ) ) { $update['max_keywords'] = (int) $volume_fiches; }
    if ( in_array( 'uuid', $cols, true ) && function_exists( 'wp_generate_uuid4' ) ) { $update['uuid'] = wp_generate_uuid4(); }

    $existing_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$table}` WHERE user_id = %d ORDER BY id ASC LIMIT 1", $wp_user_id ) );
    if ( $existing_id <= 0 ) {
        $existing_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id ASC LIMIT 1", $email ) );
    }
    if ( $existing_id > 0 ) {
        $wpdb->update( $table, $update, array( 'id' => $existing_id ) );
    } else {
        $fmt = array();
        foreach ( $update as $val ) {
            $fmt[] = is_int( $val ) ? '%d' : '%s';
        }
        $wpdb->insert( $table, $update, $fmt );
    }

    $final_id = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT id FROM `{$table}` WHERE user_id = %d OR LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id ASC LIMIT 1",
            $wp_user_id,
            $email
        )
    );
    return $final_id > 0;
}

/**
 * Complète $insert_data / $insert_format pour les colonnes mw_user NOT NULL sans défaut (scanner structure).
 *
 * @param array<string,mixed> $insert_data
 * @param array<int,string>  $insert_format
 */
function avision_onboarding_mw_user_fill_not_null_columns( $wpdb, $mw_table, &$insert_data, &$insert_format, $username, $email, $plain_password, $first_name, $last_name, $mw_display_name, $user_uid_max_len ) {
    $mw_meta = $wpdb->get_results( "SHOW FULL COLUMNS FROM `{$mw_table}`", ARRAY_A );
    if ( ! is_array( $mw_meta ) ) {
        return;
    }
    foreach ( $mw_meta as $col ) {
        $field = isset( $col['Field'] ) ? (string) $col['Field'] : '';
        if ( $field === '' || array_key_exists( $field, $insert_data ) ) {
            continue;
        }
        if ( strtoupper( (string) ( $col['Null'] ?? '' ) ) !== 'NO' ) {
            continue;
        }
        $def = $col['Default'] ?? null;
        if ( $def !== null && $def !== '' ) {
            continue;
        }
        if ( stripos( (string) ( $col['Extra'] ?? '' ), 'auto_increment' ) !== false ) {
            continue;
        }
        $type = strtolower( (string) ( $col['Type'] ?? '' ) );
        $val  = null;
        $fmt  = '%s';

        if ( in_array( $field, array( 'username', 'user_login' ), true ) ) {
            $val = $username;
        } elseif ( $field === 'email' ) {
            $val = $email;
        } elseif ( in_array( $field, array( 'password', 'user_pass', 'passwd' ), true ) ) {
            $val = wp_hash_password( $plain_password );
        } elseif ( $field === 'user_uid' ) {
            $val = uniqid( 'avi_' );
            if ( $user_uid_max_len > 0 && strlen( $val ) > $user_uid_max_len ) {
                $val = substr( 'avi_' . bin2hex( random_bytes( 16 ) ), 0, $user_uid_max_len );
            }
        } elseif ( in_array( $field, array( 'date_added', 'created_at', 'date_created', 'updated_at' ), true ) ) {
            $val = current_time( 'mysql' );
        } elseif ( $field === 'status' ) {
            $val = 'active';
        } elseif ( $field === 'language_id' ) {
            $val = 1;
            $fmt = '%d';
        } elseif ( in_array( $field, array( 'display_name', 'name', 'full_name' ), true ) ) {
            $val = $mw_display_name;
        } elseif ( $field === 'first_name' ) {
            $val = $first_name;
        } elseif ( $field === 'last_name' ) {
            $val = $last_name;
        } elseif ( strpos( $type, 'int' ) !== false || strpos( $type, 'decimal' ) !== false || strpos( $type, 'float' ) !== false || strpos( $type, 'double' ) !== false || strpos( $type, 'bigint' ) !== false ) {
            $val = 0;
            $fmt = '%d';
        } elseif ( strpos( $type, 'date' ) !== false || strpos( $type, 'time' ) !== false ) {
            $val = current_time( 'mysql' );
        } else {
            $val = '';
        }

        $insert_data[ $field ] = $val;
        $insert_format[]       = $fmt;
    }
}

/**
 * Enregistre une pré-inscription B2C ou B2B en table wor1515_avi_pre_inscription (AVISION_PRE_INSCRIPTION_TABLE).
 * Retourne un token temporaire pour liaison ultérieure au PID.
 */
function avision_rest_onboarding_save( \WP_REST_Request $request ) {
    $params = $request->get_json_params();
    if ( ! is_array( $params ) ) {
        $params = $request->get_params();
    }
    $type  = isset( $params['type'] ) ? sanitize_text_field( $params['type'] ) : $request->get_param( 'type' );
    $email = isset( $params['email'] ) ? sanitize_email( $params['email'] ) : $request->get_param( 'email' );
    if ( ! in_array( $type, [ 'b2c', 'b2b' ], true ) || empty( $email ) || ! is_email( $email ) ) {
        return new \WP_REST_Response( [ 'success' => false, 'error' => 'invalid_params' ], 400 );
    }
    $email = strtolower( trim( (string) $email ) );
    global $wpdb;

    /** Hash WP pour JSON `data` (tunnel B2C — pas de création de compte avant paiement). */
    $b2c_password_hash = null;

    // Tunnel B2C : mot de passe + anti-fantôme (mw_user / wor1515_avi_saas_profiles) — tout en pré-inscription uniquement.
    if ( $type === 'b2c' ) {
        $plain_password = isset( $params['password'] ) ? (string) $params['password'] : (string) $request->get_param( 'password' );
        $plain_confirm  = isset( $params['password_confirm'] ) ? (string) $params['password_confirm'] : (string) $request->get_param( 'password_confirm' );
        if ( $plain_password !== $plain_confirm ) {
            return avision_rest_onboarding_error_response(
                __( 'Les mots de passe ne correspondent pas.', 'avision-dashboard' ),
                400,
                array( 'code' => 'password_mismatch' )
            );
        }
        if ( strlen( $plain_password ) < 8 ) {
            return avision_rest_onboarding_error_response( 'password_too_short', 400, array( 'code' => 'password_too_short' ) );
        }
        $b2c_password_hash = wp_hash_password( $plain_password );

        $user_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT user_id FROM mw_user WHERE email = %s LIMIT 1',
                $email
            )
        );
        if ( $user_exists ) {
            $ghost_uid = (int) $user_exists;
            return new \WP_REST_Response(
                array(
                    'success' => false,
                    'message' => sprintf(
                        /* translators: %d: mw_user id */
                        __( 'Un compte existe déjà pour cet e-mail (mw_user ID: %d).', 'avision-dashboard' ),
                        $ghost_uid
                    ),
                    'ghost'    => 'mw_user',
                    'ghost_id' => $ghost_uid,
                ),
                409
            );
        }
        $saas_exists = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT id FROM wor1515_avi_saas_profiles WHERE email = %s LIMIT 1',
                $email
            )
        );
        if ( $saas_exists ) {
            $ghost_pid = (int) $saas_exists;
            return new \WP_REST_Response(
                array(
                    'success' => false,
                    'message' => sprintf(
                        /* translators: %d: profile id */
                        __( 'Un profil existe déjà pour cet e-mail (ID: %d).', 'avision-dashboard' ),
                        $ghost_pid
                    ),
                    'ghost'    => 'wor1515_avi_saas_profiles',
                    'ghost_id' => $ghost_pid,
                ),
                409
            );
        }
    }

    /** ID utilisateur WordPress pour auto-login après inscription B2B réussie (0 si création WP échouée). */
    $avi_b2b_wp_for_cookie = 0;
    /** Rollback si échec après création B2B (mw_user + wor1515_avi_saas_profiles). */
    $b2b_rollback_mw_user_id   = 0;
    $b2b_rollback_profile_id     = 0;
    /** True uniquement si wp_create_user vient de créer un nouveau compte WP (rollback = supprimer ce user). */
    $b2b_wp_user_created_here = false;
    /** Table mw_user pour rollback / requêtes B2B (surchargée dans le bloc B2B si constante définie). */
    $b2b_mw_user_table = 'mw_user';

    // Tunnel B2B unifié : création immédiate compte WordPress + meta agence + profil SaaS.
    if ( $type === 'b2b' ) {
        $b2b_saas_profile_ready = false;
        $b2b_mw_user_table = ( defined( 'AVISION_MW_USER_TABLE' ) && is_string( AVISION_MW_USER_TABLE ) && AVISION_MW_USER_TABLE !== '' )
            ? AVISION_MW_USER_TABLE
            : 'mw_user';
        // Table métier SaaS : constante AVISION_SAAS_PROFILES_TABLE si définie, sinon wor1515_avi_saas_profiles.
        $b2b_profiles_table = ( defined( 'AVISION_SAAS_PROFILES_TABLE' ) && is_string( AVISION_SAAS_PROFILES_TABLE ) && AVISION_SAAS_PROFILES_TABLE !== '' )
            ? AVISION_SAAS_PROFILES_TABLE
            : 'wor1515_avi_saas_profiles';
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $b2b_profiles_table ) ) !== $b2b_profiles_table ) {
            $b2b_profiles_table = 'wor1515_avi_saas_profiles';
            if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $b2b_profiles_table ) ) !== $b2b_profiles_table ) {
                $b2b_profiles_table = function_exists( 'avi_get_onboarding_profiles_table' ) ? avi_get_onboarding_profiles_table() : ( $wpdb->prefix . 'avi_saas_profiles' );
            }
        }
        $user_exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT user_id FROM `{$b2b_mw_user_table}` WHERE email = %s LIMIT 1",
                $email
            )
        );
        $saas_exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM `{$b2b_profiles_table}` WHERE email = %s LIMIT 1",
                $email
            )
        );
        if ( $user_exists ) {
            $found_user = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT user_id, email FROM `{$b2b_mw_user_table}` WHERE email = %s LIMIT 1",
                    $email
                )
            );
            $ghost_uid = ( $found_user && isset( $found_user->user_id ) ) ? (int) $found_user->user_id : (int) $user_exists;
            // Réponse REST 409 (wp_send_json_error coupe le cycle REST) — message exploitable en phpMyAdmin.
            return new \WP_REST_Response(
                [
                    'success' => false,
                    'message' => sprintf(
                        'Fantôme trouvé dans mw_user (ID: %d)',
                        $ghost_uid
                    ),
                    'ghost'    => 'mw_user',
                    'ghost_id' => $ghost_uid,
                ],
                409
            );
        }
        if ( $saas_exists ) {
            $found_saas = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT id, email FROM `{$b2b_profiles_table}` WHERE email = %s LIMIT 1",
                    $email
                )
            );
            $ghost_pid = ( $found_saas && isset( $found_saas->id ) ) ? (int) $found_saas->id : (int) $saas_exists;
            return new \WP_REST_Response(
                [
                    'success' => false,
                    'message' => sprintf(
                        'Fantôme trouvé dans SaaS_Profiles (ID: %d)',
                        $ghost_pid
                    ),
                    'ghost'    => 'wor1515_avi_saas_profiles',
                    'ghost_id' => $ghost_pid,
                ],
                409
            );
        }

        // Email absent de mw_user ET de wor1515_avi_saas_profiles : création chaînée sans faute —
        // 1) compte WordPress (si possible) + ligne mw_user (Identité), 2) wor1515_avi_saas_profiles (Métier) avec le même user_id que mw_user.

        $agency_name  = isset( $params['agency_name'] ) ? sanitize_text_field( (string) $params['agency_name'] ) : sanitize_text_field( (string) $request->get_param( 'agency_name' ) );
        $contact_name = isset( $params['contact_name'] ) ? sanitize_text_field( (string) $params['contact_name'] ) : sanitize_text_field( (string) $request->get_param( 'contact_name' ) );
        $agency_name  = trim( $agency_name );
        $contact_name = trim( $contact_name );
        $volume_fiches = isset( $params['volume_fiches'] ) ? absint( $params['volume_fiches'] ) : absint( $request->get_param( 'volume_fiches' ) );
        $plain_password = isset( $params['password'] ) ? (string) $params['password'] : (string) $request->get_param( 'password' );
        $plain_confirm  = isset( $params['password_confirm'] ) ? (string) $params['password_confirm'] : (string) $request->get_param( 'password_confirm' );
        if ( $plain_password !== $plain_confirm ) {
            return new \WP_REST_Response( [ 'success' => false, 'error' => 'Les mots de passe ne correspondent pas.' ], 400 );
        }
        $contact_parts = preg_split( '/\s+/', trim( $contact_name ) );
        $first_name = '';
        $last_name  = '';
        if ( is_array( $contact_parts ) && ! empty( $contact_parts ) ) {
            $first_name = (string) array_shift( $contact_parts );
            $last_name  = trim( implode( ' ', $contact_parts ) );
        }
        if ( strlen( $plain_password ) < 8 ) {
            return new \WP_REST_Response( [ 'success' => false, 'error' => 'password_too_short' ], 400 );
        }

        $username_base = sanitize_user( current( explode( '@', $email ) ), true );
        if ( $username_base === '' ) {
            $username_base = 'agency_user';
        }
        $username = $username_base;
        $suffix   = 1;
        while ( username_exists( $username ) ) {
            $suffix++;
            $username = $username_base . $suffix;
        }

        $mw_table    = $b2b_mw_user_table;
        $mw_columns  = $wpdb->get_col( "SHOW COLUMNS FROM `{$mw_table}`", 0 );
        $mw_columns  = is_array( $mw_columns ) ? $mw_columns : array();

        $user_uid_max_len = 0;
        if ( in_array( 'user_uid', $mw_columns, true ) ) {
            $uid_col_row = $wpdb->get_row( "SHOW FULL COLUMNS FROM `{$mw_table}` WHERE Field = 'user_uid'" );
            if ( $uid_col_row && ! empty( $uid_col_row->Type ) && preg_match( '/\((\d+)\)/', (string) $uid_col_row->Type, $uid_m ) ) {
                $user_uid_max_len = max( 1, (int) $uid_m[1] );
            }
        }

        /**
         * Insertion complète mw_user (connexion custom) : user_uid, email, hash WP, status, date, language_id, noms + colonnes NOT NULL détectées.
         *
         * @return array{user_id:int, sql_error:string}
         */
        $avi_insert_mw_user = function () use ( $wpdb, $mw_table, $mw_columns, $username, $email, $plain_password, $first_name, $last_name, $agency_name, $user_uid_max_len ) {
            $insert_data   = array();
            $insert_format = array();
            if ( in_array( 'username', $mw_columns, true ) ) {
                $insert_data['username'] = $username;
                $insert_format[]       = '%s';
            }
            if ( in_array( 'email', $mw_columns, true ) ) {
                $insert_data['email'] = $email;
                $insert_format[]      = '%s';
            }
            if ( in_array( 'password', $mw_columns, true ) ) {
                $insert_data['password'] = wp_hash_password( $plain_password );
                $insert_format[]         = '%s';
            }
            if ( in_array( 'user_uid', $mw_columns, true ) ) {
                $uid_val = uniqid( 'avi_' );
                if ( $user_uid_max_len > 0 && strlen( $uid_val ) > $user_uid_max_len ) {
                    $uid_val = substr( 'avi_' . bin2hex( random_bytes( 16 ) ), 0, $user_uid_max_len );
                }
                $insert_data['user_uid'] = $uid_val;
                $insert_format[]         = '%s';
            }
            if ( in_array( 'date_added', $mw_columns, true ) ) {
                $insert_data['date_added'] = current_time( 'mysql' );
                $insert_format[]           = '%s';
            }
            if ( in_array( 'status', $mw_columns, true ) ) {
                $insert_data['status'] = 'active';
                $insert_format[]       = '%s';
            }
            if ( in_array( 'language_id', $mw_columns, true ) ) {
                $insert_data['language_id'] = 1;
                $insert_format[]          = '%d';
            }
            if ( in_array( 'first_name', $mw_columns, true ) ) {
                $insert_data['first_name'] = $first_name;
                $insert_format[]           = '%s';
            }
            if ( in_array( 'last_name', $mw_columns, true ) ) {
                $insert_data['last_name'] = $last_name;
                $insert_format[]          = '%s';
            }
            $mw_display_name = $contact_name !== '' ? $contact_name : ( $agency_name !== '' ? $agency_name : $username );
            avision_onboarding_mw_user_fill_not_null_columns( $wpdb, $mw_table, $insert_data, $insert_format, $username, $email, $plain_password, $first_name, $last_name, $mw_display_name, $user_uid_max_len );
            if ( empty( $insert_data ) ) {
                return array( 'user_id' => 0, 'sql_error' => 'mw_user_no_insertable_columns' );
            }
            $ok = $wpdb->insert( $mw_table, $insert_data, $insert_format );
            if ( ! $ok ) {
                return array( 'user_id' => 0, 'sql_error' => $wpdb->last_error ? $wpdb->last_error : 'mw_user_insert_failed' );
            }
            if ( in_array( 'user_id', $mw_columns, true ) ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table contrôlé (AVISION_MW_USER_TABLE).
                $new_id = (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT user_id FROM `{$mw_table}` WHERE email = %s ORDER BY user_id DESC LIMIT 1",
                        $email
                    )
                );
                return array( 'user_id' => $new_id, 'sql_error' => '' );
            }
            return array( 'user_id' => (int) $wpdb->insert_id, 'sql_error' => '' );
        };

        $wp_user_id  = 0;
        $mw_user_id  = 0;
        // Purge immédiate de pré-inscription avant la naissance WP (source de vérité nettoyée en amont).
        avision_pre_inscription_delete_rows_for_email( $email );
        $wp_created  = wp_create_user( $username, $plain_password, $email );
        if ( ! is_wp_error( $wp_created ) ) {
            $wp_user_id = (int) $wp_created;
            $wp_user_obj = get_userdata( $wp_user_id );
            if ( $wp_user_obj instanceof \WP_User ) {
                $wp_user_obj->set_role( 'avi_agency' );
            }
            $avi_b2b_wp_for_cookie = $wp_user_id;
            $b2b_wp_user_created_here = true;
            // Diagnostic B2B : table cible forcée (élimine tout doute avant INSERT).
            $profiles_table = 'wor1515_avi_saas_profiles';
            $business_name_insert = $agency_name !== '' ? $agency_name : ( $contact_name !== '' ? $contact_name : $username );
            $business_name_insert = trim( (string) $business_name_insert );
            if ( $business_name_insert === '' ) {
                $business_name_insert = 'N/A';
            }
            $manual_insert_data = array(
                'user_id'        => $wp_user_id,
                'email'          => $email,
                'business_name'  => $business_name_insert,
                'neighborhood'   => 'N/A',
                'radar_plan'     => 'agency_tier1',
                'current_level'  => 1,
                'uuid'           => function_exists( 'wp_generate_uuid4' ) ? wp_generate_uuid4() : wp_generate_uuid(),
                'created_at'     => current_time( 'mysql' ),
                'updated_at'     => current_time( 'mysql' ),
            );
            $has_uuid_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'uuid' ) ) === 'uuid' );
            if ( ! $has_uuid_col ) {
                unset( $manual_insert_data['uuid'] );
            }
            $has_created_at_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'created_at' ) ) === 'created_at' );
            if ( ! $has_created_at_col ) {
                unset( $manual_insert_data['created_at'] );
            }
            $has_updated_at_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'updated_at' ) ) === 'updated_at' );
            if ( ! $has_updated_at_col ) {
                unset( $manual_insert_data['updated_at'] );
            }
            $has_neighborhood_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'neighborhood' ) ) === 'neighborhood' );
            if ( ! $has_neighborhood_col ) {
                unset( $manual_insert_data['neighborhood'] );
            }
            $has_current_level_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'current_level' ) ) === 'current_level' );
            if ( ! $has_current_level_col ) {
                unset( $manual_insert_data['current_level'] );
            }
            // Janitor : uniquement par email avant INSERT (pas par user_id — libère la ligne quel que soit l’ancien ID, évite duplicate entry).
            if ( ! avision_should_block_recent_profile_delete( $profiles_table, $email ) ) {
                $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM `{$profiles_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s))",
                        $email
                    )
                );
            }
            // Béton test : table + valeurs par défaut au moment critique.
            $profiles_table = 'wor1515_avi_saas_profiles';
            if ( isset( $manual_insert_data['business_name'] ) && trim( (string) $manual_insert_data['business_name'] ) === '' ) {
                $manual_insert_data['business_name'] = 'Agency Test';
            }
            if ( isset( $manual_insert_data['neighborhood'] ) && trim( (string) $manual_insert_data['neighborhood'] ) === '' ) {
                $manual_insert_data['neighborhood'] = 'Default';
            }
            $manual_insert_format = array();
            foreach ( $manual_insert_data as $key => $val ) {
                $manual_insert_format[] = ( is_int( $val ) ) ? '%d' : '%s';
            }
            $manual_profile_inserted = $wpdb->insert( $profiles_table, $manual_insert_data, $manual_insert_format );
            if ( ! $manual_profile_inserted ) {
                $err_msg = $wpdb->last_error ? (string) $wpdb->last_error : 'wpdb->insert returned false';
                update_user_meta( $wp_user_id, 'avi_last_sql_error', $err_msg );
                if ( function_exists( 'error_log' ) ) {
                    error_log( 'AVI DB INSERT ERROR: ' . $wpdb->last_error );
                }
                wp_die( 'SQL ERROR: ' . $err_msg );
            }
            update_user_meta( $wp_user_id, 'avi_last_sql_success', 'Profile created in ' . $profiles_table );
            $b2b_saas_profile_ready = true;
            update_user_meta( $wp_user_id, 'is_agency_master', '1' );
            if ( function_exists( 'wp_set_current_user' ) && function_exists( 'wp_set_auth_cookie' ) ) {
                wp_set_current_user( $wp_user_id );
                wp_set_auth_cookie( $wp_user_id, true, '' );
            }
            if ( function_exists( 'error_log' ) ) {
                error_log( 'AVI BIRTH: User created with ID ' . $wp_user_id . '. SaaS Profile created: ' . ( $b2b_saas_profile_ready ? 'YES' : 'NO' ) );
            }
            $mw_user_id = (int) $wpdb->get_var(
                $wpdb->prepare( "SELECT user_id FROM `{$mw_table}` WHERE email = %s LIMIT 1", $email )
            );
            if ( $mw_user_id <= 0 ) {
                $mw_ins = $avi_insert_mw_user();
                $mw_user_id = (int) $mw_ins['user_id'];
                if ( $mw_user_id <= 0 ) {
                    error_log( 'AVI ERROR: mw_user insert after wp_create_user failed. SQL: ' . $mw_ins['sql_error'] );
                    // avision_rest_onboarding_maybe_delete_wp_user( $wp_user_id );
                    return avision_rest_onboarding_error_response( avision_onboarding_wpdb_error_msg( $mw_ins['sql_error'] ), 400 );
                }
            }
        } elseif ( avision_rest_onboarding_is_wp_duplicate_email_error( $wp_created ) ) {
            // E-mail déjà présent dans wp_users : on ne bloque pas — connexion si le mot de passe est bon + mw_user manuel si besoin.
            $existing = get_user_by( 'email', $email );
            if ( $existing ) {
                if ( ! wp_check_password( $plain_password, $existing->user_pass, $existing->ID ) ) {
                    return avision_rest_onboarding_error_response(
                        __( 'Un compte existe déjà avec cet e-mail. Mot de passe incorrect.', 'avision-dashboard' ),
                        403
                    );
                }
                $wp_user_id = (int) $existing->ID;
                $avi_b2b_wp_for_cookie = $wp_user_id;
                update_user_meta( $wp_user_id, 'is_agency_master', '1' );
                $bn_for_force = $agency_name !== '' ? $agency_name : ( $contact_name !== '' ? $contact_name : $username );
                $b2b_saas_profile_ready = avision_force_b2b_saas_profile_row( $wp_user_id, $email, $bn_for_force, $volume_fiches );
                if ( function_exists( 'wp_set_current_user' ) && function_exists( 'wp_set_auth_cookie' ) ) {
                    wp_set_current_user( $wp_user_id );
                    wp_set_auth_cookie( $wp_user_id, true, '' );
                }
                if ( function_exists( 'error_log' ) ) {
                    error_log( 'AVI BIRTH: User created with ID ' . $wp_user_id . '. SaaS Profile created: ' . ( $b2b_saas_profile_ready ? 'YES' : 'NO' ) );
                }
                $mw_user_id = (int) $wpdb->get_var(
                    $wpdb->prepare( "SELECT user_id FROM `{$mw_table}` WHERE email = %s LIMIT 1", $email )
                );
                if ( $mw_user_id <= 0 ) {
                    $mw_ins = $avi_insert_mw_user();
                    $mw_user_id = (int) $mw_ins['user_id'];
                    if ( $mw_user_id <= 0 ) {
                        return avision_rest_onboarding_error_response( avision_onboarding_wpdb_error_msg( $mw_ins['sql_error'] ), 400 );
                    }
                }
            } else {
                $wp_error_message = $wp_created->get_error_message();
                error_log( 'AVI ERROR: duplicate email reported but get_user_by empty. ' . $wp_error_message );
                $mw_ins     = $avi_insert_mw_user();
                $mw_user_id = (int) $mw_ins['user_id'];
                if ( $mw_user_id <= 0 ) {
                    return avision_rest_onboarding_error_response( avision_onboarding_wpdb_error_msg( $mw_ins['sql_error'] ) . ' | WP : ' . $wp_error_message, 400 );
                }
            }
        } else {
            $wp_error_message = $wp_created->get_error_message();
            error_log( 'AVI ERROR: User creation failed. Message: ' . $wp_error_message );
            $mw_ins     = $avi_insert_mw_user();
            $mw_user_id = (int) $mw_ins['user_id'];
            if ( $mw_user_id <= 0 ) {
                return avision_rest_onboarding_error_response( avision_onboarding_wpdb_error_msg( $mw_ins['sql_error'] ) . ' | WP : ' . $wp_error_message, 400 );
            }
        }

        if ( $mw_user_id <= 0 ) {
            return avision_rest_onboarding_error_response( avision_onboarding_wpdb_error_msg( 'mw_user_id_missing' ), 400 );
        }

        $profiles_table = 'wor1515_avi_saas_profiles';
        $has_radar_plan = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'radar_plan' ) ) === 'radar_plan' );
        $has_uuid_col   = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'uuid' ) ) === 'uuid' );
        // Session WordPress : lier le profil SaaS à l’ID wp_users (sinon mw_user seul pour parcours sans compte WP).
        $saas_profile_user_id = ( $wp_user_id > 0 ) ? (int) $wp_user_id : (int) $mw_user_id;
        // Libellé affiché + e-mail : insérés ensemble pour le dashboard / AJAX (évite profil « sans nom »).
        $business_name_insert = $agency_name !== '' ? $agency_name : ( $contact_name !== '' ? $contact_name : $username );
        $business_name_insert = trim( (string) $business_name_insert );
        if ( $business_name_insert === '' ) {
            $business_name_insert = 'N/A';
        }
        $insert_data = [
            'user_id'       => $saas_profile_user_id,
            'business_name' => $business_name_insert,
            'city'          => 'N/A',
            'neighborhood'  => 'N/A',
            'category'      => 'agency',
            'email'         => $email,
        ];
        $insert_format = [ '%d', '%s', '%s', '%s', '%s', '%s' ];
        if ( $has_radar_plan ) {
            $insert_data['radar_plan'] = 'agency_tier1';
            $insert_format[] = '%s';
        }
        if ( $has_uuid_col && function_exists( 'wp_generate_uuid4' ) ) {
            $insert_data['uuid'] = wp_generate_uuid4();
            $insert_format[]   = '%s';
        }
        if ( $volume_fiches > 0 && $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'max_keywords' ) ) === 'max_keywords' ) {
            $insert_data['max_keywords'] = $volume_fiches;
            $insert_format[] = '%d';
        }

        if ( ! $b2b_saas_profile_ready ) {
            $profiles_table = 'wor1515_avi_saas_profiles';
            if ( isset( $insert_data['business_name'] ) && trim( (string) $insert_data['business_name'] ) === '' ) {
                $insert_data['business_name'] = 'Agency Test';
            }
            if ( isset( $insert_data['neighborhood'] ) && trim( (string) $insert_data['neighborhood'] ) === '' ) {
                $insert_data['neighborhood'] = 'Default';
            }
            // Même janitor email-only qu’au premier INSERT B2B (évite duplicate entry sur wor1515).
            if ( ! avision_should_block_recent_profile_delete( $profiles_table, $email ) ) {
                $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM `{$profiles_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s))",
                        $email
                    )
                );
            }
            $profile_inserted = $wpdb->insert( $profiles_table, $insert_data, $insert_format );
            if ( ! $profile_inserted ) {
                // TEMP diagnostic B2B : pas de suppression mw_user.
                // $wpdb->delete( $mw_table, array( 'user_id' => $mw_user_id ), array( '%d' ) );
                $ins_err = $wpdb->last_error ? (string) $wpdb->last_error : 'wpdb->insert returned false';
                if ( $wp_user_id > 0 ) {
                    update_user_meta( $wp_user_id, 'avi_last_sql_error', $ins_err );
                }
                wp_die( 'SQL ERROR: ' . $ins_err );
            }
            $b2b_rollback_profile_id = (int) $wpdb->insert_id;
        } else {
            $b2b_rollback_profile_id = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM `{$profiles_table}` WHERE user_id = %d OR LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id ASC LIMIT 1",
                    (int) $saas_profile_user_id,
                    $email
                )
            );
        }
        $b2b_rollback_mw_user_id = (int) $mw_user_id;

        // Janitor immédiat : plus de salle d’attente pour cet e-mail (évite d’attendre wp_login).
        avision_pre_inscription_delete_rows_for_email( $email );
        if ( function_exists( 'error_log' ) ) {
            error_log( 'AVI SUCCESS: User ' . $email . ' created and pre-inscription cleared.' );
        }
    }

    $table = AVISION_PRE_INSCRIPTION_TABLE;
    $get = function( $key ) use ( $request, $params ) {
        return isset( $params[ $key ] ) ? $params[ $key ] : $request->get_param( $key );
    };
    $temp_token   = bin2hex( random_bytes( 32 ) );
    $business_name = $type === 'b2c' ? $get( 'business_name' ) : null;
    $city         = $type === 'b2c' ? $get( 'city' ) : null;
    $country      = $type === 'b2c' ? $get( 'country' ) : null;
    $neighborhood = $type === 'b2c' ? $get( 'neighborhood' ) : null;
    $category     = $type === 'b2c' ? $get( 'category' ) : null;
    $agency_name  = $type === 'b2b' ? $get( 'agency_name' ) : null;
    $contact_name = $type === 'b2b' ? $get( 'contact_name' ) : null;
    $volume_fiches = $type === 'b2b' ? (int) $get( 'volume_fiches' ) : null;

    // Schéma wor1515 : id, email, token, data — tout le reste en JSON dans `data`.
    $data_to_store = array(
        'type' => $type,
    );
    if ( $type === 'b2c' ) {
        $data_to_store['business_name'] = $business_name ? sanitize_text_field( $business_name ) : '';
        $data_to_store['city']           = $city ? sanitize_text_field( $city ) : '';
        $data_to_store['country']        = $country ? sanitize_text_field( $country ) : '';
        $data_to_store['neighborhood']   = $neighborhood ? sanitize_text_field( $neighborhood ) : '';
        $data_to_store['category']       = $category ? sanitize_text_field( $category ) : '';
        $score_pre = isset( $params['score'] ) ? (int) $params['score'] : (int) $request->get_param( 'score' );
        if ( $score_pre > 0 ) {
            $data_to_store['score'] = $score_pre;
        }
        $lang_pre = isset( $params['language'] ) ? sanitize_text_field( (string) $params['language'] ) : sanitize_text_field( (string) $request->get_param( 'language' ) );
        if ( $lang_pre !== '' ) {
            $data_to_store['language'] = $lang_pre;
        }
        if ( $b2c_password_hash !== null ) {
            $data_to_store['password_hash'] = $b2c_password_hash;
        }
    } else {
        $data_to_store['agency_name']   = $agency_name ? sanitize_text_field( $agency_name ) : '';
        $data_to_store['contact_name']  = $contact_name ? sanitize_text_field( $contact_name ) : '';
        $data_to_store['volume_fiches'] = $volume_fiches > 0 ? $volume_fiches : null;
        $plain_for_json = isset( $params['password'] ) ? (string) $params['password'] : (string) $request->get_param( 'password' );
        if ( $plain_for_json !== '' ) {
            $data_to_store['password'] = $plain_for_json;
        }
    }
    $data_json = wp_json_encode( $data_to_store, JSON_UNESCAPED_UNICODE );

    $inserted = $wpdb->insert(
        $table,
        array(
            'email' => sanitize_email( $email ),
            'token' => $temp_token,
            'data'  => $data_json,
        ),
        array( '%s', '%s', '%s' )
    );
    if ( ! $inserted ) {
        if ( $type === 'b2b' && $b2b_rollback_profile_id > 0 ) {
                // TEMP diagnostic B2B : rollback profil désactivé.
                // $rollback_profiles_table = ( isset( $b2b_profiles_table ) && is_string( $b2b_profiles_table ) && $b2b_profiles_table !== '' ) ? $b2b_profiles_table : 'wor1515_avi_saas_profiles';
                // $wpdb->delete( $rollback_profiles_table, array( 'id' => $b2b_rollback_profile_id ), array( '%d' ) );
        }
        if ( $type === 'b2b' && $b2b_rollback_mw_user_id > 0 ) {
            // TEMP diagnostic B2B : rollback mw_user désactivé.
            // $wpdb->delete( $b2b_mw_user_table, array( 'user_id' => $b2b_rollback_mw_user_id ), array( '%d' ) );
        }
        if ( $type === 'b2b' && $avi_b2b_wp_for_cookie > 0 && $b2b_wp_user_created_here ) {
            // avision_rest_onboarding_maybe_delete_wp_user( $avi_b2b_wp_for_cookie );
        }
        $sql_err = isset( $wpdb->last_error ) ? trim( (string) $wpdb->last_error ) : '';
        $fail_msg = ( $sql_err !== '' ) ? $sql_err : avision_onboarding_wpdb_error_msg( 'avi_pre_inscription_insert_failed' );
        return avision_rest_onboarding_error_response(
            $fail_msg,
            400,
            array(
                'code'      => 'avi_pre_inscription_insert_failed',
                'sql_error' => $sql_err,
            )
        );
    }
    // B2B sans Stripe : supprimer la ligne pré-inscription tout juste insérée (l’auth repose sur le cookie WP ; pas besoin de salle d’attente).
    if ( $type === 'b2b' && $b2b_rollback_profile_id > 0 ) {
        $stripe_ok = function_exists( 'avision_stripe_secret_key_configured' ) && avision_stripe_secret_key_configured();
        if ( ! $stripe_ok ) {
            avision_pre_inscription_delete_rows_for_email( $email );
        }
    }
    if ( function_exists( 'error_log' ) ) {
        error_log( 'AVI SUCCESS: Pre-inscription stored for ' . $email );
    }

    // B2B : métas agence + type de compte AVANT cookie et URL (sinon le « Portier » thème peut traiter l’utilisateur comme premium).
    if ( $type === 'b2b' && $avi_b2b_wp_for_cookie > 0 ) {
        $uid_b2b = (int) $avi_b2b_wp_for_cookie;
        update_user_meta( $uid_b2b, 'is_agency_master', '1' );
        update_user_meta( $uid_b2b, 'avi_user_type', 'b2b' );
        if ( function_exists( 'avi_set_account_type' ) ) {
            avi_set_account_type( $uid_b2b, 'agency' );
        } else {
            update_user_meta( $uid_b2b, 'avi_account_type', 'agency' );
        }
        if ( function_exists( 'clean_user_cache' ) ) {
            clean_user_cache( $uid_b2b );
        }
    }

    // Auto-login B2B : l’ID est celui créé / retrouvé pour cet e-mail (pas un ID fixe).
    if ( $type === 'b2b' && $avi_b2b_wp_for_cookie > 0 && function_exists( 'wp_set_current_user' ) && function_exists( 'wp_set_auth_cookie' ) ) {
        wp_set_current_user( (int) $avi_b2b_wp_for_cookie );
        wp_set_auth_cookie( (int) $avi_b2b_wp_for_cookie, true, '' );
    }
    $response_payload = array(
        'success'             => true,
        'token'               => $temp_token,
        'pre_token'           => $temp_token,
        /** Après pré-inscription : le front appelle POST stripe-create-checkout-session puis redirige vers Stripe. */
        'stripe_checkout'     => true,
        'checkout_create_url' => function_exists( 'rest_url' ) ? rest_url( 'avision/v1/stripe-create-checkout-session' ) : home_url( '/wp-json/avision/v1/stripe-create-checkout-session' ),
    );
    // Sans clé Stripe : mode test / dev — redirection directe vers le dashboard (comportement précédent).
    if ( ! function_exists( 'avision_stripe_secret_key_configured' ) || ! avision_stripe_secret_key_configured() ) {
        if ( $type === 'b2b' ) {
            // URL absolue : onboarding + pre en une fois (évite ?…? ou perte de param selon home_url / slash final).
            $agency_dash = trailingslashit( home_url( '/agency-dashboard' ) );
            $response_payload['redirect'] = add_query_arg(
                array(
                    'onboarding' => 'success',
                    'pre'        => $temp_token,
                ),
                $agency_dash
            );
        } else {
            $premium_dash = trailingslashit( home_url( '/premium-dashboard' ) );
            $response_payload['redirect'] = add_query_arg(
                array(
                    'onboarding' => 'success',
                    'pre'        => $temp_token,
                ),
                $premium_dash
            );
        }
        $response_payload['stripe_skipped']       = true;
        $response_payload['stripe_skip_reason']    = 'stripe_not_configured';
    }
    if ( $type === 'b2b' && $avi_b2b_wp_for_cookie > 0 ) {
        $wp_user_id = (int) $avi_b2b_wp_for_cookie;
        $wor_table    = 'wor1515_avi_saas_profiles';
        $count        = 0;
        if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wor_table ) ) === $wor_table ) {
            $count = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(1) FROM `{$wor_table}` WHERE LOWER(TRIM(COALESCE(email,''))) = %s",
                    $email
                )
            );
        }
        update_user_meta( $wp_user_id, 'avi_final_presence_check', (string) $count );
        if ( function_exists( 'error_log' ) ) {
            error_log( 'AVI CHECK: Count for ' . $email . ' is ' . $count . ' just before redirect.' );
        }
        update_user_meta( $wp_user_id, 'avi_final_step_reached', 'yes' );
    }
    return new \WP_REST_Response( $response_payload, 200 );
}

/**
 * Fusionne une ligne wor1515 (email, token, data JSON) pour les consommateurs (Stripe, etc.).
 *
 * @param array<string,mixed> $row Ligne brute SQL.
 * @return array<string,mixed>
 */
function avision_pre_inscription_row_merged( array $row ) {
    $decoded = array();
    if ( isset( $row['data'] ) && $row['data'] !== '' && $row['data'] !== null ) {
        $decoded = json_decode( (string) $row['data'], true );
        if ( ! is_array( $decoded ) ) {
            $decoded = array();
        }
    }
    $tok = isset( $row['token'] ) ? (string) $row['token'] : ( isset( $row['temp_token'] ) ? (string) $row['temp_token'] : '' );
    $base = array(
        'email'      => isset( $row['email'] ) ? (string) $row['email'] : '',
        'temp_token' => $tok,
    );
    return array_merge( $base, $decoded );
}

/**
 * Enregistre profile_id dans le JSON `data` pour la dernière pré-inscription de cet e-mail (webhook Stripe).
 *
 * @param string $email      E-mail.
 * @param int    $profile_id ID profil SaaS.
 * @return bool
 */
function avision_merge_pre_inscription_profile_id_by_email( $email, $profile_id ) {
    if ( ! is_email( $email ) || (int) $profile_id <= 0 ) {
        return false;
    }
    global $wpdb;
    $table = AVISION_PRE_INSCRIPTION_TABLE;
    $row   = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT id, data FROM `{$table}` WHERE email = %s ORDER BY id DESC LIMIT 1",
            sanitize_email( $email )
        ),
        ARRAY_A
    );
    if ( ! is_array( $row ) || empty( $row['id'] ) ) {
        return false;
    }
    $data = json_decode( isset( $row['data'] ) ? (string) $row['data'] : '{}', true );
    if ( ! is_array( $data ) ) {
        $data = array();
    }
    $data['profile_id'] = (int) $profile_id;
    $json               = wp_json_encode( $data, JSON_UNESCAPED_UNICODE );
    $updated            = $wpdb->update(
        $table,
        array( 'data' => $json ),
        array( 'id' => (int) $row['id'] ),
        array( '%s' ),
        array( '%d' )
    );
    return $updated !== false && $updated > 0;
}

/**
 * Lie une pré-inscription (temp_token) au profile_id (PID) après création du profil.
 * À appeler après create_saas_profile quand l'inscription vient du tunnel onboarding (param pre=).
 *
 * @param string $temp_token Token retourné par onboarding-save.
 * @param int    $profile_id ID du profil (avi_saas_profiles.id).
 * @return bool True si une ligne a été mise à jour.
 */
function avision_link_pre_inscription_to_profile( $temp_token, $profile_id ) {
    if ( empty( $temp_token ) || (int) $profile_id <= 0 ) {
        return false;
    }
    global $wpdb;
    $table = AVISION_PRE_INSCRIPTION_TABLE;
    $row   = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT id, data FROM `{$table}` WHERE token = %s LIMIT 1",
            sanitize_text_field( $temp_token )
        ),
        ARRAY_A
    );
    if ( ! is_array( $row ) ) {
        return false;
    }
    $data = json_decode( isset( $row['data'] ) ? (string) $row['data'] : '{}', true );
    if ( ! is_array( $data ) ) {
        $data = array();
    }
    $data['profile_id'] = (int) $profile_id;
    $json               = wp_json_encode( $data, JSON_UNESCAPED_UNICODE );
    $updated            = $wpdb->update(
        $table,
        array( 'data' => $json ),
        array( 'token' => sanitize_text_field( $temp_token ) ),
        array( '%s' ),
        array( '%s' )
    );
    return $updated !== false && $updated > 0;
}

/**
 * Récupère une ligne de pré-inscription par temp_token.
 *
 * @param string $temp_token Token retourné par onboarding-save.
 * @return array|null Ligne (type, email, business_name, ...) ou null.
 */
function avision_get_pre_inscription_by_token( $temp_token ) {
    if ( empty( $temp_token ) ) {
        return null;
    }
    global $wpdb;
    $table = AVISION_PRE_INSCRIPTION_TABLE;
    $row   = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT email, token, data FROM `{$table}` WHERE token = %s LIMIT 1",
            sanitize_text_field( $temp_token )
        ),
        ARRAY_A
    );
    if ( ! is_array( $row ) ) {
        return null;
    }
    return avision_pre_inscription_row_merged( $row );
}

/**
 * Dernière pré-inscription pour un e-mail (ORDER BY id DESC).
 *
 * @param string $email E-mail.
 * @return array<string,mixed>|null
 */
function avision_get_latest_pre_inscription_by_email( $email ) {
    if ( ! is_email( $email ) ) {
        return null;
    }
    global $wpdb;
    $table = AVISION_PRE_INSCRIPTION_TABLE;
    $row   = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT email, token, data FROM `{$table}` WHERE email = %s ORDER BY id DESC LIMIT 1",
            sanitize_email( $email )
        ),
        ARRAY_A
    );
    if ( ! is_array( $row ) ) {
        return null;
    }
    return avision_pre_inscription_row_merged( $row );
}

/**
 * Tunnel Demo (sans session SaaS) : crée ou réutilise une ligne pré-inscription « guest »
 * à partir du transient d’audit `avision_{rid}` pour porter email + métadonnées jusqu’à Stripe.
 *
 * @param string $rid Identifiant rapport (URL).
 * @param string $override_email E-mail fourni par le client (URL / localStorage), prioritaire sur le transient.
 * @return string Token pré-inscription ou chaîne vide si impossible.
 */
function avision_ensure_demo_guest_pre_token_from_rid( $rid, $override_email = '' ) {
    $rid = is_string( $rid ) ? trim( $rid ) : '';
    if ( $rid === '' ) {
        return '';
    }
    $session = get_transient( 'avision_' . $rid );
    if ( ! is_array( $session ) ) {
        return '';
    }
    $meta = ( isset( $session['meta'] ) && is_array( $session['meta'] ) ) ? $session['meta'] : array();
    $data = ( isset( $session['data'] ) && is_array( $session['data'] ) ) ? $session['data'] : array();

    $email_in = is_string( $override_email ) ? trim( $override_email ) : '';
    if ( $email_in !== '' && is_email( $email_in ) ) {
        $email = sanitize_email( $email_in );
    } elseif ( ! empty( $meta['email'] ) && is_email( $meta['email'] ) ) {
        $email = sanitize_email( $meta['email'] );
    } else {
        $host = parse_url( home_url(), PHP_URL_HOST );
        $host = is_string( $host ) && $host !== '' ? $host : 'localhost';
        $email = 'guest+' . preg_replace( '/[^a-z0-9_-]/i', '', $rid ) . '@' . $host;
        if ( ! is_email( $email ) ) {
            $email = 'guest+' . sanitize_key( $rid ) . '@example.com';
        }
    }

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

    $needle_fragment = '"report_id":"' . $rid . '"';
    $existing_token  = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT token FROM `{$table}` WHERE data LIKE %s ORDER BY id DESC LIMIT 1",
            '%' . $wpdb->esc_like( $needle_fragment ) . '%'
        )
    );
    if ( is_string( $existing_token ) && $existing_token !== '' ) {
        return $existing_token;
    }

    $business_name = isset( $meta['name'] ) ? sanitize_text_field( (string) $meta['name'] ) : '';
    $city          = isset( $meta['city'] ) ? sanitize_text_field( (string) $meta['city'] ) : '';
    $country       = isset( $meta['country'] ) ? sanitize_text_field( (string) $meta['country'] ) : '';
    $neighborhood  = isset( $meta['neighborhood'] ) ? sanitize_text_field( (string) $meta['neighborhood'] ) : '';
    $category      = isset( $meta['category'] ) ? sanitize_text_field( (string) $meta['category'] ) : '';
    if ( $category === '' && isset( $meta['cat'] ) ) {
        $category = sanitize_text_field( (string) $meta['cat'] );
    }
    $score_pre = isset( $data['score_global'] ) ? (int) $data['score_global'] : 0;
    $lang_pre  = '';
    if ( function_exists( 'avision_detect_language_by_ip' ) ) {
        $lang_pre = (string) avision_detect_language_by_ip();
    }

    $data_to_store = array(
        'type'          => 'b2c',
        'business_name' => $business_name,
        'city'          => $city,
        'country'       => $country,
        'neighborhood'  => $neighborhood,
        'category'      => $category,
        'report_id'     => $rid,
        'guest_demo'    => true,
    );
    if ( $score_pre > 0 ) {
        $data_to_store['score'] = $score_pre;
    }
    if ( $lang_pre !== '' ) {
        $data_to_store['language'] = $lang_pre;
    }

    $temp_token = bin2hex( random_bytes( 32 ) );
    $data_json  = wp_json_encode( $data_to_store, JSON_UNESCAPED_UNICODE );

    $inserted = $wpdb->insert(
        $table,
        array(
            'email' => $email,
            'token' => $temp_token,
            'data'  => $data_json,
        ),
        array( '%s', '%s', '%s' )
    );

    return $inserted ? $temp_token : '';
}

/**
 * Insère une ligne mw_user avec mot de passe déjà hashé (après paiement B2C).
 *
 * @param \wpdb  $wpdb          Instance wpdb.
 * @param string $mw_table      Nom table (mw_user).
 * @param string $username      Login SaaS.
 * @param string $email         E-mail.
 * @param string $password_hash Hash WP (wp_hash_password).
 * @param string $display_name  Nom affiché.
 * @return int user_id mw_user ou 0.
 */
function avision_mw_user_insert_with_password_hash( $wpdb, $mw_table, $username, $email, $password_hash, $display_name ) {
    $mw_columns = $wpdb->get_col( "SHOW COLUMNS FROM `{$mw_table}`", 0 );
    $mw_columns = is_array( $mw_columns ) ? $mw_columns : array();
    $user_uid_max_len = 0;
    if ( in_array( 'user_uid', $mw_columns, true ) ) {
        $uid_col_row = $wpdb->get_row( "SHOW FULL COLUMNS FROM `{$mw_table}` WHERE Field = 'user_uid'" );
        if ( $uid_col_row && ! empty( $uid_col_row->Type ) && preg_match( '/\((\d+)\)/', (string) $uid_col_row->Type, $uid_m ) ) {
            $user_uid_max_len = max( 1, (int) $uid_m[1] );
        }
    }
    $contact_parts = preg_split( '/\s+/', trim( $display_name ) );
    $first_name    = '';
    $last_name     = '';
    if ( is_array( $contact_parts ) && ! empty( $contact_parts ) ) {
        $first_name = (string) array_shift( $contact_parts );
        $last_name  = trim( implode( ' ', $contact_parts ) );
    }
    $insert_data   = array();
    $insert_format = array();
    if ( in_array( 'username', $mw_columns, true ) ) {
        $insert_data['username'] = $username;
        $insert_format[]       = '%s';
    }
    if ( in_array( 'email', $mw_columns, true ) ) {
        $insert_data['email'] = $email;
        $insert_format[]     = '%s';
    }
    if ( in_array( 'password', $mw_columns, true ) ) {
        $insert_data['password'] = $password_hash;
        $insert_format[]         = '%s';
    }
    if ( in_array( 'user_uid', $mw_columns, true ) ) {
        $uid_val = uniqid( 'avi_' );
        if ( $user_uid_max_len > 0 && strlen( $uid_val ) > $user_uid_max_len ) {
            $uid_val = substr( 'avi_' . bin2hex( random_bytes( 16 ) ), 0, $user_uid_max_len );
        }
        $insert_data['user_uid'] = $uid_val;
        $insert_format[]         = '%s';
    }
    if ( in_array( 'date_added', $mw_columns, true ) ) {
        $insert_data['date_added'] = current_time( 'mysql' );
        $insert_format[]           = '%s';
    }
    if ( in_array( 'status', $mw_columns, true ) ) {
        $insert_data['status'] = 'active';
        $insert_format[]       = '%s';
    }
    if ( in_array( 'language_id', $mw_columns, true ) ) {
        $insert_data['language_id'] = 1;
        $insert_format[]          = '%d';
    }
    if ( in_array( 'first_name', $mw_columns, true ) ) {
        $insert_data['first_name'] = $first_name;
        $insert_format[]           = '%s';
    }
    if ( in_array( 'last_name', $mw_columns, true ) ) {
        $insert_data['last_name'] = $last_name;
        $insert_format[]          = '%s';
    }
    avision_onboarding_mw_user_fill_not_null_columns( $wpdb, $mw_table, $insert_data, $insert_format, $username, $email, '', $first_name, $last_name, $display_name, $user_uid_max_len );
    if ( empty( $insert_data ) ) {
        return 0;
    }
    $ok = $wpdb->insert( $mw_table, $insert_data, $insert_format );
    if ( ! $ok ) {
        return 0;
    }
    if ( in_array( 'user_id', $mw_columns, true ) ) {
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                'SELECT user_id FROM mw_user WHERE email = %s ORDER BY user_id DESC LIMIT 1',
                $email
            )
        );
    }
    return (int) $wpdb->insert_id;
}

/**
 * Compte Solo (B2C) : avi_account_type = premium, avi_user_type = b2c, suppression du flag agence.
 *
 * @param int $wp_user_id ID wp_users.
 */
function avision_ensure_b2c_solo_account_meta( $wp_user_id ) {
    $uid = (int) $wp_user_id;
    if ( $uid <= 0 ) {
        return;
    }
    delete_user_meta( $uid, 'is_agency_master' );
    update_user_meta( $uid, 'avi_user_type', 'b2c' );
    if ( function_exists( 'avi_set_account_type' ) ) {
        avi_set_account_type( $uid, 'premium' );
    } else {
        update_user_meta( $uid, 'avi_account_type', 'premium' );
    }
    if ( function_exists( 'clean_user_cache' ) ) {
        clean_user_cache( $uid );
    }
}

/**
 * Webhook Stripe B2C : crée mw_user + wor1515_avi_saas_profiles (pas de rôle agence) si pré-inscription avec password_hash.
 *
 * @param string $email         E-mail client.
 * @param string $company_name  Metadata Stripe.
 * @param string $city          Ville.
 * @param string $country       Pays.
 * @param string $neighborhood  Quartier.
 * @param string $category      Métier.
 * @return array{profile_id:int,mw_user_id:int,wp_user_id:int}|null Null = laisser le fallback SaaS (legacy).
 */
function avision_stripe_provision_b2c_from_pre_inscription( $email, $company_name, $city, $country, $neighborhood, $category ) {
    if ( ! is_email( $email ) ) {
        return null;
    }
    $pre = avision_get_latest_pre_inscription_by_email( $email );
    if ( ! is_array( $pre ) || ( isset( $pre['type'] ) && $pre['type'] !== 'b2c' ) ) {
        return null;
    }
    $password_hash = isset( $pre['password_hash'] ) ? (string) $pre['password_hash'] : '';
    if ( $password_hash === '' && ! empty( $pre['password'] ) ) {
        $password_hash = wp_hash_password( (string) $pre['password'] );
    }
    if ( $password_hash === '' ) {
        return null;
    }

    global $wpdb;
    $profiles_table = 'wor1515_avi_saas_profiles';
    $mw_table       = 'mw_user';

    $existing_profile_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$profiles_table}` WHERE email = %s LIMIT 1", $email ) );
    if ( $existing_profile_id > 0 ) {
        $mw_uid = (int) $wpdb->get_var( $wpdb->prepare( "SELECT user_id FROM `{$mw_table}` WHERE email = %s LIMIT 1", $email ) );
        $wp_u   = get_user_by( 'email', $email );
        if ( $wp_u && function_exists( 'avision_ensure_b2c_solo_account_meta' ) ) {
            avision_ensure_b2c_solo_account_meta( (int) $wp_u->ID );
        }
        return array(
            'profile_id' => $existing_profile_id,
            'mw_user_id' => $mw_uid,
            'wp_user_id' => $wp_u ? (int) $wp_u->ID : 0,
        );
    }

    $business_name    = $company_name !== '' ? $company_name : ( isset( $pre['business_name'] ) ? (string) $pre['business_name'] : __( 'Profil', 'avision-dashboard' ) );
    $city_use         = $city !== '' ? $city : ( isset( $pre['city'] ) ? (string) $pre['city'] : 'N/A' );
    $country_use      = $country !== '' ? strtoupper( (string) $country ) : ( isset( $pre['country'] ) ? strtoupper( (string) $pre['country'] ) : 'FR' );
    if ( ! in_array( $country_use, array( 'FR', 'GB', 'US', 'ES' ), true ) ) {
        $country_use = 'FR';
    }
    $neighborhood_use = $neighborhood !== '' ? $neighborhood : ( isset( $pre['neighborhood'] ) ? (string) $pre['neighborhood'] : '' );
    $category_use     = $category !== '' ? $category : ( isset( $pre['category'] ) ? (string) $pre['category'] : 'Client' );

    $username_base = sanitize_user( current( explode( '@', $email ) ), true );
    if ( $username_base === '' ) {
        $username_base = 'user';
    }
    $username = $username_base;
    $suffix   = 1;
    while ( username_exists( $username ) ) {
        $suffix++;
        $username = $username_base . $suffix;
    }

    $wp_user_id = wp_create_user( $username, wp_generate_password( 32, true, true ), $email );
    if ( is_wp_error( $wp_user_id ) ) {
        if ( ! avision_rest_onboarding_is_wp_duplicate_email_error( $wp_user_id ) ) {
            return null;
        }
        $existing_wp = get_user_by( 'email', $email );
        if ( ! $existing_wp ) {
            return null;
        }
        $wp_user_id = (int) $existing_wp->ID;
    } else {
        $wp_user_id = (int) $wp_user_id;
    }

    $wpdb->update( $wpdb->users, array( 'user_pass' => $password_hash ), array( 'ID' => $wp_user_id ), array( '%s' ), array( '%d' ) );
    wp_cache_delete( $wp_user_id, 'users' );
    if ( function_exists( 'avision_ensure_b2c_solo_account_meta' ) ) {
        avision_ensure_b2c_solo_account_meta( $wp_user_id );
    } else {
        update_user_meta( $wp_user_id, 'avi_user_type', 'b2c' );
    }

    $mw_user_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT user_id FROM `{$mw_table}` WHERE email = %s LIMIT 1", $email ) );
    if ( $mw_user_id <= 0 ) {
        $mw_user_id = avision_mw_user_insert_with_password_hash( $wpdb, $mw_table, $username, $email, $password_hash, $business_name );
    }
    if ( $mw_user_id <= 0 ) {
        return null;
    }

    $has_radar        = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'radar_plan' ) ) === 'radar_plan' );
    $has_uuid         = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'uuid' ) ) === 'uuid' );
    $has_user_type_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'user_type' ) ) === 'user_type' );
    $has_country_col   = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'country' ) ) === 'country' );

    $insert_data   = array(
        'user_id'       => $mw_user_id,
        'business_name' => $business_name,
        'city'          => $city_use,
        'neighborhood'  => $neighborhood_use,
        'category'      => $category_use,
        'email'         => $email,
    );
    $insert_format = array( '%d', '%s', '%s', '%s', '%s', '%s' );
    if ( $has_radar ) {
        $insert_data['radar_plan'] = 'offert';
        $insert_format[]           = '%s';
    }
    if ( $has_uuid && function_exists( 'wp_generate_uuid4' ) ) {
        $insert_data['uuid'] = wp_generate_uuid4();
        $insert_format[]     = '%s';
    }
    if ( $has_user_type_col ) {
        $insert_data['user_type'] = 'b2c';
        $insert_format[]          = '%s';
    }
    if ( $has_country_col ) {
        $insert_data['country'] = $country_use;
        $insert_format[]        = '%s';
    }

    $ok = $wpdb->insert( $profiles_table, $insert_data, $insert_format );
    if ( ! $ok ) {
        return null;
    }

    return array(
        'profile_id' => (int) $wpdb->insert_id,
        'mw_user_id' => $mw_user_id,
        'wp_user_id' => $wp_user_id,
    );
}

/**
 * Envoie l'email Brevo WELCOME_B2C (identifiant, mot de passe généré, liens dashboard / connexion).
 *
 * @param string $email           Email du compte WordPress.
 * @param string $business_name   Nom affiché.
 * @param string $pid             UUID profil (paramètre pid du dashboard).
 * @param string $plain_password  Mot de passe en clair (canal email uniquement).
 * @param int    $profile_row_id  ID ligne avi_saas_profiles : si > 0, vérifie que la ligne existe avant send_avi_email (template 41).
 * @return bool
 */
function avision_send_welcome_b2c_email_after_convert( $email, $business_name, $pid, $plain_password, $profile_row_id = 0 ) {
	if ( ! class_exists( '\Avision\Avision_Email_Manager' ) ) {
		return false;
	}
	$email = sanitize_email( (string) $email );
	if ( ! is_email( $email ) || $plain_password === '' ) {
		return false;
	}
	$profile_row_id = (int) $profile_row_id;
	if ( $profile_row_id > 0 && isset( $GLOBALS['wpdb'] ) ) {
		global $wpdb;
		$pt_chk = 'wor1515_avi_saas_profiles';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pt_chk ) ) !== $pt_chk ) {
			$pt_chk = $wpdb->prefix . 'avi_saas_profiles';
		}
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pt_chk ) ) === $pt_chk ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table validé par SHOW TABLES.
			$row_ok = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$pt_chk}` WHERE id = %d LIMIT 1", $profile_row_id ) );
			if ( $row_ok !== $profile_row_id ) {
				if ( function_exists( 'error_log' ) ) {
					error_log( '[BREVO] Envoi WELCOME_B2C (41) annulé : profil id=' . $profile_row_id . ' introuvable en base.' );
				}
				return false;
			}
		}
	}
	if ( function_exists( 'error_log' ) ) {
		error_log( '[BREVO] Tentative d\'envoi pour l\'email : ' . $email . ' avec le pass généré.' );
	}
	$saas_user_id = 0;
	if ( class_exists( '\Avision\Avision_Database' ) ) {
		$db = new \Avision\Avision_Database();
		$db->init();
		if ( method_exists( $db, 'get_or_create_saas_user' ) ) {
			$uid = $db->get_or_create_saas_user( $email );
			if ( $uid !== null && (int) $uid > 0 ) {
				$saas_user_id = (int) $uid;
			}
		}
	}
	global $wpdb;
	$saas_users_table = 'wor1515_avi_saas_users';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saas_users_table ) ) !== $saas_users_table ) {
		$saas_users_table = $wpdb->prefix . 'avi_saas_users';
	}
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saas_users_table ) ) !== $saas_users_table ) {
		$saas_users_table = '';
	}
	if ( $saas_user_id <= 0 && $saas_users_table !== '' ) {
		$existing = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id DESC LIMIT 1",
				$email
			)
		);
		if ( $existing > 0 ) {
			$saas_user_id = $existing;
		} else {
			$ok_ins = $wpdb->insert( $saas_users_table, [ 'email' => $email ], [ '%s' ] );
			if ( $ok_ins ) {
				$saas_user_id = (int) $wpdb->insert_id;
			}
		}
	}
	if ( $saas_users_table !== '' && ( $saas_user_id <= 0 || 1 === $saas_user_id ) ) {
		$resolved = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id DESC LIMIT 1",
				$email
			)
		);
		if ( $resolved > 0 ) {
			$saas_user_id = $resolved;
		}
	}
	$bn              = $business_name !== '' ? $business_name : 'Profil ' . $email;
	$dashboard_base  = home_url( '/premium-dashboard/' );
	$pid_str         = is_string( $pid ) ? trim( $pid ) : '';
	$dashboard_url   = ( $pid_str !== '' ) ? add_query_arg( 'pid', rawurlencode( $pid_str ), $dashboard_base ) : $dashboard_base;
	$login_url       = wp_login_url( $dashboard_base );

	$b2c_template_id = 41;
	if ( defined( '\Avision\Avision_Email_Manager::TEMPLATE_WELCOME_B2C' ) ) {
		$t_const = constant( '\Avision\Avision_Email_Manager::TEMPLATE_WELCOME_B2C' );
		if ( is_numeric( $t_const ) && (int) $t_const > 0 ) {
			$b2c_template_id = (int) $t_const;
		}
	}

	return \Avision\Avision_Email_Manager::send_avi_email(
		$saas_user_id > 0 ? $saas_user_id : 0,
		$b2c_template_id,
		[
			'business_name'   => $bn,
			'dashboard_url'   => $dashboard_url,
			'login_url'       => $login_url,
			'user_pass'       => $plain_password,
			'recipient_email' => $email,
		]
	);
}

/**
 * Liste les fiches Google disponibles et met à jour pending_google_locations (retour OAuth sans lieu choisi).
 *
 * @param string $access_token Jeton OAuth.
 * @param int    $profile_id   ID ligne profil.
 * @param string $profiles_table Nom table.
 * @param string $google_location_name Si non vide, ne fait rien.
 * @return void
 */
function avision_convert_profile_sync_pending_google_locations( $access_token, $profile_id, $profiles_table, $google_location_name = '' ) {
	global $wpdb;
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 || trim( (string) $google_location_name ) !== '' ) {
		return;
	}
	$access_token = is_string( $access_token ) ? trim( $access_token ) : '';
	if ( $access_token === '' || ! is_string( $profiles_table ) || $profiles_table === '' ) {
		return;
	}
	if ( function_exists( 'avi_ensure_saas_pending_google_locations_column' ) ) {
		avi_ensure_saas_pending_google_locations_column( $profiles_table );
	}
	if ( function_exists( 'avi_oauth_agency_store_pending_google_locations' ) ) {
		avi_oauth_agency_store_pending_google_locations( $access_token, $profile_id, $profiles_table );
		return;
	}
	if ( ! function_exists( 'avi_google_api_list_business_locations_flat' ) ) {
		return;
	}
	$col = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'pending_google_locations' ) );
	if ( $col !== 'pending_google_locations' ) {
		return;
	}
	$locations = avi_google_api_list_business_locations_flat( $access_token );
	$payload   = wp_json_encode(
		array(
			'locations'  => $locations,
			'fetched_at' => gmdate( 'c' ),
		),
		JSON_UNESCAPED_UNICODE
	);
	$wpdb->update(
		$profiles_table,
		array( 'pending_google_locations' => $payload ),
		array( 'id' => $profile_id ),
		array( '%s' ),
		array( '%d' )
	);
}

/**
 * Email WordPress dérivé lorsque l'email du rapport est déjà celui du compte administrateur (ID 1),
 * pour ne jamais lier un profil B2C onboarding à l'admin.
 *
 * @param string $email Email du rapport (déjà sanitizé).
 * @param string $rid   Identifiant rapport.
 * @return string Email dérivé unique ou chaîne vide.
 */
function avision_b2c_derived_wp_email_for_admin_collision( $email, $rid ) {
	$email = sanitize_email( (string) $email );
	$rid   = sanitize_text_field( (string) $rid );
	if ( ! is_email( $email ) || $rid === '' ) {
		return '';
	}
	$hash = substr( md5( $rid . '|' . strtolower( $email ) ), 0, 12 );
	$parts = explode( '@', $email, 2 );
	if ( count( $parts ) === 2 && $parts[0] !== '' && $parts[1] !== '' ) {
		$derived = $parts[0] . '+avi_b2c_' . $hash . '@' . $parts[1];
		if ( is_email( $derived ) ) {
			return $derived;
		}
	}
	$host = wp_parse_url( home_url(), PHP_URL_HOST );
	if ( ! is_string( $host ) || $host === '' ) {
		$host = 'localhost';
	}
	$derived = 'avi_b2c_' . $hash . '@' . $host;
	return is_email( $derived ) ? $derived : '';
}

/**
 * Convertit un rapport temporaire (transient avision_{rid}) en profil SaaS persistant.
 *
 * @param string $report_id Identifiant rapport (rid).
 * @return array{profile_id:int,wp_user_id:int,pid:string,email:string}|null
 */
function avision_convert_report_to_profile( $report_id ) {
	$rid = sanitize_text_field( (string) $report_id );
	$can_convert = current_user_can( 'manage_options' );
	if ( ! $can_convert ) {
		return null;
	}
	if ( $rid === '' ) {
		return null;
	}

	$report_data = get_transient( 'avision_' . $rid );
	if ( ! is_array( $report_data ) || empty( $report_data['meta'] ) || ! is_array( $report_data['meta'] ) ) {
		return null;
	}

	$meta = $report_data['meta'];
	$email = isset( $meta['email'] ) ? sanitize_email( (string) $meta['email'] ) : '';
	if ( ! is_email( $email ) ) {
		return null;
	}

	$business_name = isset( $meta['name'] ) ? sanitize_text_field( (string) $meta['name'] ) : '';
	$city          = isset( $meta['city'] ) ? sanitize_text_field( (string) $meta['city'] ) : '';
	$phone         = isset( $meta['phone'] ) ? sanitize_text_field( (string) $meta['phone'] ) : '';
	$category      = isset( $meta['category'] ) ? sanitize_text_field( (string) $meta['category'] ) : '';
	if ( $category === '' && isset( $meta['cat'] ) ) {
		$category = sanitize_text_field( (string) $meta['cat'] );
	}
	$neighborhood = isset( $meta['neighborhood'] ) ? sanitize_text_field( (string) $meta['neighborhood'] ) : '';
	$country      = isset( $meta['country'] ) ? strtoupper( sanitize_text_field( (string) $meta['country'] ) ) : 'FR';
	if ( ! in_array( $country, array( 'FR', 'GB', 'US', 'ES' ), true ) ) {
		$country = 'FR';
	}

	$meta_google_access   = isset( $meta['google_access_token'] ) ? trim( (string) $meta['google_access_token'] ) : '';
	$meta_google_refresh  = isset( $meta['google_refresh_token'] ) ? trim( (string) $meta['google_refresh_token'] ) : '';
	$meta_google_location = isset( $meta['google_location_name'] ) ? trim( (string) $meta['google_location_name'] ) : '';
	if ( $meta_google_location === '' && isset( $meta['google_resource_name'] ) ) {
		$meta_google_location = trim( (string) $meta['google_resource_name'] );
	}
	$meta_expires_in = isset( $meta['expires_in'] ) ? (int) $meta['expires_in'] : 0;
	if ( $meta_expires_in <= 0 && isset( $meta['google_expires_in'] ) ) {
		$meta_expires_in = (int) $meta['google_expires_in'];
	}

	$is_new_wp_user             = false;
	$plain_password_for_welcome = '';
	// Jamais get_current_user_id() : rattachement uniquement à l'email du rapport (ou compte dérivé si collision admin).
	$wp_account_email = $email;
	$wp_user          = get_user_by( 'email', $email );
	if ( $wp_user && (int) $wp_user->ID === 1 ) {
		$derived = avision_b2c_derived_wp_email_for_admin_collision( $email, $rid );
		if ( $derived === '' ) {
			return null;
		}
		$wp_account_email = $derived;
		$wp_user          = get_user_by( 'email', $derived );
	}
	if ( $wp_user && (int) $wp_user->ID === 1 ) {
		return null;
	}
	if ( $wp_user ) {
		$wp_user_id = (int) $wp_user->ID;
	} else {
		$username_base = sanitize_user( current( explode( '@', $wp_account_email ) ), true );
		if ( $username_base === '' ) {
			$username_base = 'user';
		}
		$username = $username_base;
		$suffix   = 1;
		while ( username_exists( $username ) ) {
			$suffix++;
			$username = $username_base . $suffix;
		}
		$plain_password_for_welcome = wp_generate_password( 12, false );
		$created_user               = wp_create_user( $username, $plain_password_for_welcome, $wp_account_email );
		if ( is_wp_error( $created_user ) ) {
			if ( ! function_exists( 'avision_rest_onboarding_is_wp_duplicate_email_error' ) || ! avision_rest_onboarding_is_wp_duplicate_email_error( $created_user ) ) {
				return null;
			}
			$existing_wp = get_user_by( 'email', $wp_account_email );
			if ( ! $existing_wp ) {
				error_log( '[AVI convert_report] E-mail WP déjà utilisé mais get_user_by vide : ' . $wp_account_email );
				return null;
			}
			$wp_user_id = (int) $existing_wp->ID;
			$is_new_wp_user = false;
		} else {
			$wp_user_id     = (int) $created_user;
			$is_new_wp_user = true;
		}
	}
	if ( (int) $wp_user_id === 1 ) {
		$admin_email_opt = sanitize_email( (string) get_option( 'admin_email', '' ) );
		$acct_norm       = strtolower( trim( $wp_account_email ) );
		$admin_norm      = strtolower( trim( (string) $admin_email_opt ) );
		if ( $acct_norm !== $admin_norm ) {
			error_log( '[AVI convert_report] ALERTE : ID WordPress 1 avec email session ≠ admin_email — arrêt.' );
			return null;
		}
		error_log( '[AVI convert_report] ALERTE : parcours B2C refusé sur l\'utilisateur ID 1.' );
		return null;
	}
	if ( (int) $wp_user_id <= 0 ) {
		return null;
	}

	if ( function_exists( 'avision_ensure_b2c_solo_account_meta' ) ) {
		avision_ensure_b2c_solo_account_meta( $wp_user_id );
	}

	global $wpdb;
	$profiles_table = 'wor1515_avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $profiles_table ) ) !== $profiles_table ) {
		$profiles_table = $wpdb->prefix . 'avi_saas_profiles';
	}
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $profiles_table ) ) !== $profiles_table ) {
		return null;
	}

	// avi_saas_profiles.user_id = ID avi_saas_users en MySQL (même base que la ligne profil), pas l’ID WordPress.
	// Ne pas appeler Avision_Database::get_or_create_saas_user ici : avec DATABASE_URL il cible PostgreSQL et renvoie
	// un id sans correspondance dans wor1515_/prefix_avi_saas_users → effet « tout le monde en user_id 1 » côté MySQL.
	$saas_users_table = 'wor1515_avi_saas_users';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saas_users_table ) ) !== $saas_users_table ) {
		$saas_users_table = $wpdb->prefix . 'avi_saas_users';
	}
	$saas_user_id_for_profile = 0;
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saas_users_table ) ) === $saas_users_table ) {
		// Dernière ligne SaaS pour cet e-mail (évite de rattacher au id=1 erroné si doublons / données legacy).
		$ex_su = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id DESC LIMIT 1",
				$email
			)
		);
		if ( $ex_su > 0 ) {
			$saas_user_id_for_profile = $ex_su;
		} else {
			$ins_ok = $wpdb->insert( $saas_users_table, array( 'email' => $email ), array( '%s' ) );
			if ( $ins_ok && (int) $wpdb->insert_id > 0 ) {
				$saas_user_id_for_profile = (int) $wpdb->insert_id;
			}
		}
		if ( $saas_user_id_for_profile > 0 ) {
			$owner_email = (string) $wpdb->get_var(
				$wpdb->prepare( "SELECT email FROM `{$saas_users_table}` WHERE id = %d LIMIT 1", $saas_user_id_for_profile )
			);
			if ( strtolower( trim( $owner_email ) ) !== strtolower( trim( $email ) ) ) {
				error_log( '[AVI convert_report] ALERTE : user_id SaaS ' . (int) $saas_user_id_for_profile . ' incohérent (email BDD ≠ rapport) — recalcul par email.' );
				$saas_user_id_for_profile = (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) ORDER BY id DESC LIMIT 1",
						$email
					)
				);
				if ( $saas_user_id_for_profile <= 0 ) {
					$ins_ok2 = $wpdb->insert( $saas_users_table, array( 'email' => $email ), array( '%s' ) );
					if ( $ins_ok2 && (int) $wpdb->insert_id > 0 ) {
						$saas_user_id_for_profile = (int) $wpdb->insert_id;
					}
				}
			}
		}
		if ( (int) $saas_user_id_for_profile === 1 ) {
			$row1_email = (string) $wpdb->get_var( "SELECT email FROM `{$saas_users_table}` WHERE id = 1 LIMIT 1" );
			if ( strtolower( trim( $row1_email ) ) !== strtolower( trim( $email ) ) ) {
				error_log( '[AVI convert_report] ALERTE : Tentative d\'attribution user_id SaaS 1 bloquée (ligne 1 ≠ email rapport).' );
				$saas_user_id_for_profile = (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) AND id <> 1 ORDER BY id DESC LIMIT 1",
						$email
					)
				);
				if ( $saas_user_id_for_profile <= 0 ) {
					$ins_ok3 = $wpdb->insert( $saas_users_table, array( 'email' => $email ), array( '%s' ) );
					if ( $ins_ok3 && (int) $wpdb->insert_id > 0 ) {
						$saas_user_id_for_profile = (int) $wpdb->insert_id;
					} else {
						$saas_user_id_for_profile = 0;
					}
				}
			}
		}
	}
	if ( (int) $saas_user_id_for_profile === 1 && isset( $saas_users_table ) && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saas_users_table ) ) === $saas_users_table ) {
		$final_row1_email = (string) $wpdb->get_var( "SELECT email FROM `{$saas_users_table}` WHERE id = 1 LIMIT 1" );
		if ( strtolower( trim( $final_row1_email ) ) !== strtolower( trim( $email ) ) ) {
			error_log( '[AVI convert_report] ALERTE : user_id SaaS 1 final incohérent (ligne 1 ≠ email rapport) — correction id SaaS ou INSERT.' );
			$fix_saas_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$saas_users_table}` WHERE LOWER(TRIM(email)) = LOWER(TRIM(%s)) AND id > 1 ORDER BY id DESC LIMIT 1",
					$email
				)
			);
			if ( $fix_saas_id > 0 ) {
				$saas_user_id_for_profile = $fix_saas_id;
			} else {
				$ins_fix_saas = $wpdb->insert( $saas_users_table, array( 'email' => $email ), array( '%s' ) );
				if ( $ins_fix_saas && (int) $wpdb->insert_id > 0 ) {
					$saas_user_id_for_profile = (int) $wpdb->insert_id;
				} else {
					return null;
				}
			}
		}
	}

	// Flux convert rapport → profil : langue toujours 'en' pour ce flux ; user_id profil = avi_saas_users.id (SaaS) ; wp_user_id = wp_users.ID (WordPress).
	$convert_b2c_profile_lang = 'en';
	$has_user_id_col_global   = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'user_id' ) ) === 'user_id' );
	if ( $has_user_id_col_global && $saas_user_id_for_profile <= 0 ) {
		return null;
	}

	$existing_profile_id = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$profiles_table}` WHERE email = %s LIMIT 1", $email ) );
	if ( $existing_profile_id > 0 ) {
		$has_wp_user_id_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'wp_user_id' ) ) === 'wp_user_id' );
		$has_user_id_col    = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'user_id' ) ) === 'user_id' );
		$has_uuid_col       = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'uuid' ) ) === 'uuid' );
		$has_phone_col      = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'phone' ) ) === 'phone' );
		$has_type_col       = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'type' ) ) === 'type' );
		$has_country_col    = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'country' ) ) === 'country' );
		$has_neigh_col      = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'neighborhood' ) ) === 'neighborhood' );
		$has_lang_col_ex    = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'lang' ) ) === 'lang' );
		$has_gtok_ex        = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_access_token' ) ) === 'google_access_token' );
		$has_gref_ex        = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_refresh_token' ) ) === 'google_refresh_token' );
		$has_gloc_ex        = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_location_name' ) ) === 'google_location_name' );
		$has_gexp_ex        = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_token_expires_at' ) ) === 'google_token_expires_at' );
		$has_radar_plan_ex  = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'radar_plan' ) ) === 'radar_plan' );
		$has_max_kw_ex      = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'max_keywords' ) ) === 'max_keywords' );

		$update_data = [
			'business_name' => $business_name !== '' ? $business_name : 'Profil ' . $email,
			'city'          => $city !== '' ? $city : '-',
			'category'      => $category !== '' ? $category : 'Client',
			'email'         => $email,
		];
		$update_format = [ '%s', '%s', '%s', '%s' ];
		if ( $has_wp_user_id_col ) {
			$update_data['wp_user_id'] = $wp_user_id;
			$update_format[] = '%d';
		}
		if ( $has_user_id_col ) {
			$update_data['user_id'] = $saas_user_id_for_profile;
			$update_format[]        = '%d';
		}
		if ( $has_phone_col ) {
			$update_data['phone'] = $phone;
			$update_format[] = '%s';
		}
		if ( $has_type_col ) {
			$update_data['type'] = 'premium';
			$update_format[] = '%s';
		}
		if ( $has_country_col ) {
			$update_data['country'] = $country;
			$update_format[] = '%s';
		}
		if ( $has_neigh_col ) {
			$update_data['neighborhood'] = $neighborhood;
			$update_format[] = '%s';
		}
		if ( $has_lang_col_ex ) {
			$update_data['lang'] = $convert_b2c_profile_lang;
			$update_format[]     = '%s';
		}
		if ( $has_radar_plan_ex ) {
			$update_data['radar_plan'] = 'offert';
			$update_format[]           = '%s';
		}
		if ( $has_max_kw_ex ) {
			$update_data['max_keywords'] = 20;
			$update_format[]             = '%d';
		}
		$has_created_at_ex = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'created_at' ) ) === 'created_at' );
		if ( $has_created_at_ex ) {
			$update_data['created_at'] = current_time( 'mysql' );
			$update_format[]           = '%s';
		}
		$has_updated_at_ex_up = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'updated_at' ) ) === 'updated_at' );
		if ( $has_updated_at_ex_up ) {
			$update_data['updated_at'] = current_time( 'mysql' );
			$update_format[]           = '%s';
		}
		if ( $has_gtok_ex ) {
			$update_data['google_access_token'] = null;
			$update_format[]                    = '%s';
		}
		if ( $has_gref_ex ) {
			$update_data['google_refresh_token'] = null;
			$update_format[]                     = '%s';
		}
		if ( $has_gexp_ex ) {
			$update_data['google_token_expires_at'] = null;
			$update_format[]                      = '%s';
		}
		if ( $has_gloc_ex ) {
			$update_data['google_location_name'] = null;
			$update_format[]                     = '%s';
		}
		$wpdb->update(
			$profiles_table,
			$update_data,
			[ 'id' => $existing_profile_id ],
			$update_format,
			[ '%d' ]
		);

		$row_gl = '';
		if ( $has_gloc_ex ) {
			$row_gl = (string) $wpdb->get_var( $wpdb->prepare( "SELECT google_location_name FROM `{$profiles_table}` WHERE id = %d LIMIT 1", $existing_profile_id ) );
		}
		$row_tok = '';
		avision_convert_profile_sync_pending_google_locations( $row_tok, $existing_profile_id, $profiles_table, $meta_google_location !== '' ? $meta_google_location : $row_gl );

		$existing_pid = '';
		if ( $has_uuid_col ) {
			$existing_pid = (string) $wpdb->get_var( $wpdb->prepare( "SELECT uuid FROM `{$profiles_table}` WHERE id = %d LIMIT 1", $existing_profile_id ) );
			if ( $existing_pid === '' ) {
				$existing_pid = function_exists( 'wp_generate_uuid4' ) ? wp_generate_uuid4() : uniqid( 'pid_', true );
				$wpdb->update(
					$profiles_table,
					[ 'uuid' => $existing_pid ],
					[ 'id' => $existing_profile_id ],
					[ '%s' ],
					[ '%d' ]
				);
			}
		}
		if ( $existing_pid === '' && function_exists( 'avi_get_or_create_profile_uuid' ) ) {
			$existing_pid = (string) avi_get_or_create_profile_uuid( $existing_profile_id );
		}
		if ( function_exists( 'avision_force_immediate_scan' ) ) {
			avision_force_immediate_scan( $existing_profile_id );
		}
		if ( function_exists( 'wp_set_current_user' ) && function_exists( 'wp_set_auth_cookie' ) ) {
			wp_set_current_user( (int) $wp_user_id );
			wp_set_auth_cookie( (int) $wp_user_id, true, '' );
		}
		if ( function_exists( 'error_log' ) && ( ! $is_new_wp_user || $plain_password_for_welcome === '' ) ) {
			error_log(
				'[BREVO] Skip welcome (existing profile branch): is_new_wp_user=' . ( $is_new_wp_user ? '1' : '0' )
				. ' plain_pw=' . ( $plain_password_for_welcome !== '' ? 'set' : 'empty' )
				. ' rid=' . $rid
				. ' — avision_send_welcome_b2c_email_after_convert exige compte WP nouveau + mot de passe généré.'
			);
		}
		if ( $is_new_wp_user && $plain_password_for_welcome !== '' ) {
			if ( function_exists( 'error_log' ) ) {
				error_log( '[BREVO] Pass Length: ' . strlen( $plain_password_for_welcome ) );
			}
			// Confirmer que la liaison wp_user_id est réellement écrite en base avant l'envoi.
			if ( $has_wp_user_id_col ) {
				$hard = $wpdb->update(
					$profiles_table,
					[ 'wp_user_id' => $wp_user_id ],
					[ 'id' => $existing_profile_id ],
					[ '%d' ],
					[ '%d' ]
				);
				$confirmed = (int) $wpdb->get_var( $wpdb->prepare( "SELECT wp_user_id FROM `{$profiles_table}` WHERE id = %d LIMIT 1", $existing_profile_id ) );
				if ( $confirmed !== (int) $wp_user_id ) {
					error_log( '[BREVO] Annulé : wp_user_id non confirmé sur avi_saas_profiles (id=' . (int) $existing_profile_id . '). hard_update=' . ( is_int( $hard ) ? $hard : -1 ) );
					return null;
				}
			}
			avision_send_welcome_b2c_email_after_convert( $wp_account_email, $business_name, $existing_pid, $plain_password_for_welcome, $existing_profile_id );
		}
		return array(
			'profile_id'             => $existing_profile_id,
			'wp_user_id'             => $wp_user_id,
			'pid'                    => $existing_pid,
			'email'                  => $email,
			'brevo_welcome_pass_len' => ( $is_new_wp_user && $plain_password_for_welcome !== '' ) ? strlen( $plain_password_for_welcome ) : 0,
		);
	}

	$has_wp_user_id_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'wp_user_id' ) ) === 'wp_user_id' );
	$has_user_id_col    = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'user_id' ) ) === 'user_id' );
	$has_uuid_col       = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'uuid' ) ) === 'uuid' );
	$has_phone_col      = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'phone' ) ) === 'phone' );
	$has_type_col       = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'type' ) ) === 'type' );
	$has_country_col    = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'country' ) ) === 'country' );
	$has_neigh_col      = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'neighborhood' ) ) === 'neighborhood' );
	$has_radar_plan_col           = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'radar_plan' ) ) === 'radar_plan' );
	$has_max_keywords_col         = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'max_keywords' ) ) === 'max_keywords' );
	$has_google_access_token_col  = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_access_token' ) ) === 'google_access_token' );
	$has_google_refresh_token_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_refresh_token' ) ) === 'google_refresh_token' );
	$has_google_location_name_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_location_name' ) ) === 'google_location_name' );
	$has_lang_col                 = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'lang' ) ) === 'lang' );
	$has_google_token_expires_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'google_token_expires_at' ) ) === 'google_token_expires_at' );
	$has_created_at_col           = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'created_at' ) ) === 'created_at' );
	$has_updated_at_col           = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'updated_at' ) ) === 'updated_at' );

	$pid = function_exists( 'wp_generate_uuid4' ) ? wp_generate_uuid4() : uniqid( 'pid_', true );

	$insert_data = array(
		'email'         => $email,
		'business_name' => $business_name !== '' ? $business_name : 'Profil ' . $email,
		'city'          => $city !== '' ? $city : '-',
		'category'      => $category !== '' ? $category : 'Client',
	);
	$insert_format = array( '%s', '%s', '%s', '%s' );

	if ( $has_wp_user_id_col ) {
		$insert_data['wp_user_id'] = $wp_user_id;
		$insert_format[]           = '%d';
	}
	if ( $has_user_id_col ) {
		$insert_data['user_id'] = $saas_user_id_for_profile;
		$insert_format[]        = '%d';
	}
	if ( $has_uuid_col ) {
		$insert_data['uuid'] = $pid;
		$insert_format[]     = '%s';
	}
	if ( $has_phone_col ) {
		$insert_data['phone'] = $phone;
		$insert_format[]      = '%s';
	}
	if ( $has_type_col ) {
		$insert_data['type'] = 'premium';
		$insert_format[]     = '%s';
	}
	if ( $has_country_col ) {
		$insert_data['country'] = $country;
		$insert_format[]        = '%s';
	}
	if ( $has_neigh_col ) {
		$insert_data['neighborhood'] = $neighborhood;
		$insert_format[]             = '%s';
	}
	// Profil B2C vierge : plan offert (20 mots-clés), sans liaison Google préexistante.
	if ( $has_radar_plan_col ) {
		$insert_data['radar_plan'] = 'offert';
		$insert_format[]         = '%s';
	}
	if ( $has_max_keywords_col ) {
		$insert_data['max_keywords'] = 20;
		$insert_format[]            = '%d';
	}
	if ( $has_google_access_token_col ) {
		$insert_data['google_access_token'] = null;
		$insert_format[]                    = '%s';
	}
	if ( $has_google_refresh_token_col ) {
		$insert_data['google_refresh_token'] = null;
		$insert_format[]                     = '%s';
	}
	if ( $has_google_location_name_col ) {
		$insert_data['google_location_name'] = null;
		$insert_format[]                     = '%s';
	}
	if ( $has_google_token_expires_col ) {
		$insert_data['google_token_expires_at'] = null;
		$insert_format[]                        = '%s';
	}
	if ( $has_lang_col ) {
		$insert_data['lang'] = $convert_b2c_profile_lang;
		$insert_format[]     = '%s';
	}
	$insert_now_mysql = current_time( 'mysql' );
	if ( $has_created_at_col ) {
		$insert_data['created_at'] = $insert_now_mysql;
		$insert_format[]           = '%s';
	}
	if ( $has_updated_at_col ) {
		$insert_data['updated_at'] = $insert_now_mysql;
		$insert_format[]           = '%s';
	}

	$has_guardian_active_col     = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'guardian_active' ) ) === 'guardian_active' );
	$has_pilot_guardian_mode_col = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'pilot_guardian_mode' ) ) === 'pilot_guardian_mode' );
	$has_is_sealed_col           = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'is_sealed' ) ) === 'is_sealed' );
	$has_current_level_col       = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles_table}` LIKE %s", 'current_level' ) ) === 'current_level' );
	if ( $has_guardian_active_col ) {
		$insert_data['guardian_active'] = 0;
		$insert_format[]              = '%d';
	}
	if ( $has_pilot_guardian_mode_col ) {
		$insert_data['pilot_guardian_mode'] = 0;
		$insert_format[]                  = '%d';
	}
	if ( $has_is_sealed_col ) {
		$insert_data['is_sealed'] = 0;
		$insert_format[]        = '%d';
	}
	if ( $has_current_level_col ) {
		$insert_data['current_level'] = 1;
		$insert_format[]            = '%d';
	}

	$ok = $wpdb->insert( $profiles_table, $insert_data, $insert_format );
	if ( ! $ok ) {
		return null;
	}

	$new_profile_id = (int) $wpdb->insert_id;
	if ( $new_profile_id > 0 && function_exists( 'avision_force_immediate_scan' ) ) {
		avision_force_immediate_scan( $new_profile_id );
	}
	if ( $new_profile_id > 0 ) {
		$ins_tok = $has_google_access_token_col ? ( isset( $insert_data['google_access_token'] ) ? (string) $insert_data['google_access_token'] : '' ) : '';
		$ins_loc = $has_google_location_name_col ? ( isset( $insert_data['google_location_name'] ) ? (string) $insert_data['google_location_name'] : '' ) : '';
		avision_convert_profile_sync_pending_google_locations( $ins_tok, $new_profile_id, $profiles_table, $ins_loc );
	}
	if ( $new_profile_id > 0 && class_exists( '\Avision\Avision_Database' ) ) {
		$db_radar = new \Avision\Avision_Database();
		$db_radar->init();
		if ( method_exists( $db_radar, 'insert_initial_keywords_for_profile' ) ) {
			$db_radar->insert_initial_keywords_for_profile( $new_profile_id );
		}
	}
	if ( $new_profile_id > 0 && function_exists( 'avision_force_immediate_scan' ) ) {
		avision_force_immediate_scan( $new_profile_id );
	}

	if ( function_exists( 'error_log' ) && ( ! $is_new_wp_user || $plain_password_for_welcome === '' ) ) {
		error_log(
			'[BREVO] Skip welcome (new insert branch): is_new_wp_user=' . ( $is_new_wp_user ? '1' : '0' )
			. ' plain_pw=' . ( $plain_password_for_welcome !== '' ? 'set' : 'empty' )
			. ' rid=' . $rid
			. ' — envoi Brevo uniquement si wp_create_user vient d\'être exécuté avec mot de passe en clair.'
		);
	}
	if ( function_exists( 'wp_set_current_user' ) && function_exists( 'wp_set_auth_cookie' ) ) {
		wp_set_current_user( (int) $wp_user_id );
		wp_set_auth_cookie( (int) $wp_user_id, true, '' );
	}
	if ( $is_new_wp_user && $plain_password_for_welcome !== '' ) {
		if ( function_exists( 'error_log' ) ) {
			error_log( '[BREVO] Pass Length: ' . strlen( $plain_password_for_welcome ) );
		}
		// Confirmer que la liaison wp_user_id est réellement écrite en base avant l'envoi.
		if ( $has_wp_user_id_col ) {
			$hard2 = $wpdb->update(
				$profiles_table,
				[ 'wp_user_id' => $wp_user_id ],
				[ 'id' => $new_profile_id ],
				[ '%d' ],
				[ '%d' ]
			);
			$confirmed2 = (int) $wpdb->get_var( $wpdb->prepare( "SELECT wp_user_id FROM `{$profiles_table}` WHERE id = %d LIMIT 1", $new_profile_id ) );
			if ( $confirmed2 !== (int) $wp_user_id ) {
				error_log( '[BREVO] Annulé : wp_user_id non confirmé sur avi_saas_profiles (id=' . (int) $new_profile_id . '). hard_update=' . ( is_int( $hard2 ) ? $hard2 : -1 ) );
				return null;
			}
		}
		avision_send_welcome_b2c_email_after_convert( $wp_account_email, $business_name, $pid, $plain_password_for_welcome, $new_profile_id );
	}

	return array(
		'profile_id'             => $new_profile_id,
		'wp_user_id'             => $wp_user_id,
		'pid'                    => $pid,
		'email'                  => $email,
		'brevo_welcome_pass_len' => ( $is_new_wp_user && $plain_password_for_welcome !== '' ) ? strlen( $plain_password_for_welcome ) : 0,
	);
}

/**
 * Force le scan DataForSEO des mots-clés seeds (pending) pour un profil, sans attendre le worker CRON.
 * À utiliser après bypass admin : le fallback thème insère des lignes en pending sans J0 ; la file globale
 * traitait autrefois par id ASC, ce qui pouvait repousser indéfiniment les nouveaux PID.
 *
 * @param int $profile_id avi_saas_profiles.id.
 * @return int Nombre de mots-clés passés dans run_single_keyword_scan (hors insert_initial qui fait déjà un J0 si table vide).
 */
function avision_force_immediate_scan( $profile_id ) {
	$profile_id = (int) $profile_id;
	error_log( '[AVI force_immediate_scan] start profile_id=' . $profile_id );
	if ( $profile_id <= 0 || ! class_exists( '\Avision\Avision_Database' ) ) {
		error_log( '[AVI force_immediate_scan] abort: invalid profile_id or Avision_Database missing' );
		return 0;
	}
	$db = new \Avision\Avision_Database();
	$db->init();
	if ( ! method_exists( $db, 'get_saas_profile_for_sync' ) || ! method_exists( $db, 'run_single_keyword_scan' ) ) {
		error_log( '[AVI force_immediate_scan] abort: get_saas_profile_for_sync or run_single_keyword_scan missing' );
		return 0;
	}
	$profile = $db->get_saas_profile_for_sync( $profile_id );
	if ( empty( $profile ) || ! is_array( $profile ) ) {
		error_log( '[AVI force_immediate_scan] abort: get_saas_profile_for_sync empty for profile_id=' . $profile_id );
		return 0;
	}
	$country_log = isset( $profile['country'] ) ? (string) $profile['country'] : '';
	$biz_log     = isset( $profile['business_name'] ) ? (string) $profile['business_name'] : '';
	error_log( '[AVI force_immediate_scan] profile loaded country=' . $country_log . ' business_name=' . substr( $biz_log, 0, 80 ) );
	// Table vide : insert des 3 seeds + J0 synchrone (DataForSEO) dans la classe.
	if ( method_exists( $db, 'insert_initial_keywords_for_profile' ) ) {
		$insert_ran = $db->insert_initial_keywords_for_profile( $profile_id );
		error_log( '[AVI force_immediate_scan] insert_initial_keywords_for_profile returned ' . ( $insert_ran ? 'true (insert+J0)' : 'false (keywords already exist or skip)' ) . ' profile_id=' . $profile_id );
	}
	$limit = (int) apply_filters( 'avision_force_immediate_scan_keyword_limit', 3 );
	$limit = max( 1, min( 20, $limit ) );
	if ( ! method_exists( $db, 'get_pending_keywords_for_profile_scan' ) ) {
		error_log( '[AVI force_immediate_scan] abort: get_pending_keywords_for_profile_scan missing' );
		return 0;
	}
	$keywords = $db->get_pending_keywords_for_profile_scan( $profile_id, $limit );
	error_log( '[AVI force_immediate_scan] pending keywords count=' . count( $keywords ) . ' limit=' . $limit . ' profile_id=' . $profile_id );
	if ( empty( $keywords ) && isset( $GLOBALS['wpdb'] ) && is_object( $GLOBALS['wpdb'] ) ) {
		global $wpdb;
		$tbl_kw = $wpdb->prefix . 'avi_saas_keywords';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl_kw ) ) === $tbl_kw ) {
			$snap = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, keyword, scan_status, current_rank, rank_init FROM `{$tbl_kw}` WHERE profile_id = %d ORDER BY id ASC LIMIT 15",
					$profile_id
				),
				ARRAY_A
			);
			error_log( '[AVI force_immediate_scan] diagnostic (no pending): keyword rows snapshot=' . wp_json_encode( is_array( $snap ) ? $snap : array() ) );
		}
	}
	$scanned = 0;
	foreach ( $keywords as $kw ) {
		if ( ! is_array( $kw ) ) {
			continue;
		}
		$kid = isset( $kw['id'] ) ? (int) $kw['id'] : 0;
		$kws = isset( $kw['keyword'] ) ? (string) $kw['keyword'] : '';
		$tc  = isset( $kw['target_city'] ) ? (string) $kw['target_city'] : '';
		error_log( '[AVI force_immediate_scan] DataForSEO → run_single_keyword_scan profile_id=' . $profile_id . ' kw_id=' . $kid . ' keyword="' . $kws . '" target_city="' . $tc . '"' );
		$db->run_single_keyword_scan( $kw, $profile );
		error_log( '[AVI force_immediate_scan] run_single_keyword_scan finished kw_id=' . $kid );
		$scanned++;
	}
	error_log( '[AVI force_immediate_scan] end profile_id=' . $profile_id . ' scanned=' . $scanned );
	return $scanned;
}

/**
 * Crée une session Stripe Checkout avec metadata (données onboarding + add-ons) et success_url vers connexion.
 * Clé secrète : get_option( 'avision_stripe_secret_key' ) ou constante AVISION_STRIPE_SECRET_KEY.
 */
function avision_rest_stripe_create_checkout_session( \WP_REST_Request $request ) {
    $params = $request->get_json_params();
    if ( ! is_array( $params ) ) {
        $params = $request->get_params();
    }
    $pre_token = isset( $params['pre_token'] ) ? sanitize_text_field( $params['pre_token'] ) : $request->get_param( 'pre_token' );
    $rid = isset( $params['rid'] ) ? sanitize_text_field( $params['rid'] ) : $request->get_param( 'rid' );
    if ( empty( $rid ) ) {
        $rid = isset( $params['report_id'] ) ? sanitize_text_field( $params['report_id'] ) : $request->get_param( 'report_id' );
    }
    $client_email_param = isset( $params['email'] ) ? sanitize_email( (string) $params['email'] ) : sanitize_email( (string) $request->get_param( 'email' ) );
    if ( $pre_token === '' && $rid !== '' && function_exists( 'avision_ensure_demo_guest_pre_token_from_rid' ) ) {
        $guest_tok = avision_ensure_demo_guest_pre_token_from_rid( $rid, is_email( $client_email_param ) ? $client_email_param : '' );
        if ( is_string( $guest_tok ) && $guest_tok !== '' ) {
            $pre_token = $guest_tok;
        } else {
            return new \WP_REST_Response(
                [ 'success' => false, 'error' => 'audit_session_expired', 'message' => __( 'Session d’audit expirée ou introuvable. Relancez une analyse.', 'avision-dashboard' ) ],
                400
            );
        }
    }
    $addon_guardian = isset( $params['addon_guardian'] ) ? $params['addon_guardian'] : $request->get_param( 'addon_guardian' );
    $addon_radar    = isset( $params['addon_radar'] ) ? $params['addon_radar'] : $request->get_param( 'addon_radar' );
    $upgrade_type   = isset( $params['upgrade_type'] ) ? sanitize_text_field( $params['upgrade_type'] ) : sanitize_text_field( (string) $request->get_param( 'upgrade_type' ) );
    $profile_id     = isset( $params['profile_id'] ) ? absint( $params['profile_id'] ) : absint( $request->get_param( 'profile_id' ) );
    $upgrade_cfg    = function_exists( 'avision_get_upgrade_checkout_config' ) ? avision_get_upgrade_checkout_config( $upgrade_type ) : null;

    $metadata = [];
    if ( ! empty( $pre_token ) && function_exists( 'avision_get_pre_inscription_by_token' ) ) {
        $pre = avision_get_pre_inscription_by_token( $pre_token );
        if ( $pre ) {
            $metadata['user_type'] = strtoupper( $pre['type'] );
            $metadata['email']      = isset( $pre['email'] ) ? $pre['email'] : '';
            if ( $pre['type'] === 'b2c' ) {
                $metadata['company_name'] = isset( $pre['business_name'] ) ? $pre['business_name'] : '';
                $metadata['city']         = isset( $pre['city'] ) ? $pre['city'] : '';
                $metadata['country']      = isset( $pre['country'] ) ? $pre['country'] : '';
                $metadata['neighborhood'] = isset( $pre['neighborhood'] ) ? $pre['neighborhood'] : '';
                $metadata['category']     = isset( $pre['category'] ) ? $pre['category'] : '';
            } else {
                $metadata['company_name'] = isset( $pre['agency_name'] ) ? $pre['agency_name'] : '';
                $metadata['contact_name'] = isset( $pre['contact_name'] ) ? $pre['contact_name'] : '';
                $metadata['volume_fiches'] = isset( $pre['volume_fiches'] ) ? (string) $pre['volume_fiches'] : '';
            }
        }
    }
    $metadata['addon_guardian'] = ( $addon_guardian === true || $addon_guardian === 'true' || $addon_guardian === '1' ) ? 'true' : 'false';
    $metadata['addon_radar']   = ( $addon_radar === true || $addon_radar === 'true' || $addon_radar === '1' ) ? 'true' : 'false';
    if ( ! empty( $upgrade_type ) ) {
        $metadata['upgrade_type'] = $upgrade_type;
    }
    if ( $profile_id > 0 ) {
        $metadata['profile_id'] = (string) $profile_id;
    }
    if ( ! empty( $pre_token ) ) {
        $metadata['pre_token'] = $pre_token;
    }
    if ( ! empty( $rid ) ) {
        $metadata['report_id'] = $rid;
        $metadata['type']      = 'b2c_onboarding';
    }

    $metadata = apply_filters( 'avision_stripe_checkout_metadata', $metadata, $request );

    if ( function_exists( 'avision_stripe_secret_key_configured' ) && ! avision_stripe_secret_key_configured() ) {
        return new \WP_REST_Response( [ 'success' => false, 'error' => 'stripe_not_configured' ], 503 );
    }
	$secret_key = defined( 'AVISION_STRIPE_SECRET_KEY' ) && is_string( AVISION_STRIPE_SECRET_KEY ) && trim( AVISION_STRIPE_SECRET_KEY ) !== ''
		? trim( AVISION_STRIPE_SECRET_KEY )
		: ( is_string( get_option( 'avision_stripe_secret_key', '' ) ) ? trim( (string) get_option( 'avision_stripe_secret_key', '' ) ) : '' );

	$landing_slug = 'premium-dashboard';
	if ( ! empty( $metadata['user_type'] ) && strtoupper( trim( (string) $metadata['user_type'] ) ) === 'B2B' ) {
		$landing_slug = 'agency-dashboard';
	}
    $default_success = function_exists( 'avision_stripe_build_default_success_url' )
        ? avision_stripe_build_default_success_url( $pre_token, $landing_slug )
        : home_url( '/premium-dashboard/?status=success&onboarding=success&session_id={CHECKOUT_SESSION_ID}' );
    $success_url = apply_filters( 'avision_stripe_checkout_success_url', $default_success, $request, $pre_token );
    $cancel_url  = apply_filters( 'avision_stripe_checkout_cancel_url', home_url( '/' ), $request );

    $unit_amount = (int) apply_filters( 'avision_stripe_unit_amount', 9900, $request );
    $product_name = apply_filters( 'avision_stripe_line_product_name', 'AVI — Accès Plateforme', $request );
    $product_desc = apply_filters( 'avision_stripe_line_description', 'Visibilité locale & Mode Gardien', $request );
    $checkout_mode = 'payment';
    if ( is_array( $upgrade_cfg ) ) {
        $unit_amount = (int) ( $upgrade_cfg['unit_amount'] ?? $unit_amount );
        $product_name = isset( $upgrade_cfg['name'] ) ? (string) $upgrade_cfg['name'] : $product_name;
        $product_desc = isset( $upgrade_cfg['description'] ) ? (string) $upgrade_cfg['description'] : $product_desc;
        $checkout_mode = isset( $upgrade_cfg['mode'] ) ? (string) $upgrade_cfg['mode'] : 'subscription';
    }

    $price_id = '';
    if ( is_array( $upgrade_cfg ) && ! empty( $upgrade_cfg['price_id'] ) ) {
        $price_id = (string) $upgrade_cfg['price_id'];
    } elseif ( function_exists( 'avision_stripe_get_price_id' ) ) {
        $price_id = avision_stripe_get_price_id();
    }
    $line_items = array();
    if ( is_string( $price_id ) && $price_id !== '' && strpos( $price_id, 'price_' ) === 0 ) {
        $line_items = array(
            array(
                'price'    => $price_id,
                'quantity' => 1,
            ),
        );
    } else {
        $line_items = array(
            array(
                'price_data' => array(
                    'currency'     => 'eur',
                    'product_data' => array(
                        'name'        => $product_name,
                        'description' => $product_desc,
                    ),
                    'unit_amount' => $unit_amount,
                ),
                'quantity' => 1,
            ),
        );
        if ( $checkout_mode === 'subscription' ) {
            $line_items[0]['price_data']['recurring'] = array( 'interval' => 'month' );
        }
    }

    $body = array(
        'mode'                 => $checkout_mode,
        'success_url'          => $success_url,
        'cancel_url'           => $cancel_url,
        'payment_method_types' => array( 'card' ),
        'line_items'           => $line_items,
        'metadata'             => $metadata,
    );
    if ( ! empty( $pre_token ) ) {
        $body['client_reference_id'] = substr( $pre_token, 0, 255 );
    }
    if ( ! empty( $metadata['email'] ) && is_email( $metadata['email'] ) ) {
        $body['customer_email'] = $metadata['email'];
    }

    $response = wp_remote_post(
        'https://api.stripe.com/v1/checkout/sessions',
        [
            'headers' => [
                'Authorization' => 'Bearer ' . $secret_key,
                'Content-Type'  => 'application/x-www-form-urlencoded',
            ],
            'body'    => _avision_stripe_checkout_body( $body ),
            'timeout' => 15,
        ]
    );

    $code = wp_remote_retrieve_response_code( $response );
    $body_res = wp_remote_retrieve_body( $response );
    $data     = json_decode( $body_res, true );

    if ( is_wp_error( $response ) || $code < 200 || $code >= 300 ) {
        $err = ( is_array( $data ) && isset( $data['error']['message'] ) ) ? $data['error']['message'] : 'stripe_error';
        return new \WP_REST_Response( [ 'success' => false, 'error' => $err ], $code >= 400 ? $code : 502 );
    }
    if ( empty( $data['url'] ) ) {
        return new \WP_REST_Response( [ 'success' => false, 'error' => 'no_checkout_url' ], 502 );
    }
	if ( function_exists( 'avision_stripe_is_test_mode' ) && avision_stripe_is_test_mode() && function_exists( 'error_log' ) ) {
		error_log( '[AVI Stripe] Session Checkout créée (mode test sk_test_).' );
	}
    return new \WP_REST_Response( [ 'success' => true, 'url' => $data['url'] ], 200 );
}

/**
 * Encode le body pour Stripe Checkout Session (format attendu par l'API Stripe).
 * Stripe accepte application/json pour certaines versions ; on utilise form-encoded pour compatibilité.
 *
 * @param array $body Tableau associatif (metadata, line_items, success_url, etc.).
 * @return string Body encodé pour wp_remote_post.
 */
function _avision_stripe_checkout_body( $body ) {
    $flat = [];
    $flat['success_url'] = isset( $body['success_url'] ) ? $body['success_url'] : '';
    $flat['cancel_url']  = isset( $body['cancel_url'] ) ? $body['cancel_url'] : '';
    $flat['mode']        = isset( $body['mode'] ) ? $body['mode'] : 'payment';
    $flat['payment_method_types[0]'] = 'card';
    if ( ! empty( $body['customer_email'] ) ) {
        $flat['customer_email'] = $body['customer_email'];
    }
    if ( ! empty( $body['client_reference_id'] ) ) {
        $flat['client_reference_id'] = $body['client_reference_id'];
    }
    $li0 = isset( $body['line_items'][0] ) ? $body['line_items'][0] : array();
    if ( ! empty( $li0['price'] ) && is_string( $li0['price'] ) && strpos( $li0['price'], 'price_' ) === 0 ) {
        $flat['line_items[0][price]']    = $li0['price'];
        $flat['line_items[0][quantity]'] = isset( $li0['quantity'] ) ? (int) $li0['quantity'] : 1;
    } else {
        $flat['line_items[0][price_data][currency]'] = 'eur';
        $flat['line_items[0][price_data][product_data][name]'] = isset( $body['line_items'][0]['price_data']['product_data']['name'] ) ? $body['line_items'][0]['price_data']['product_data']['name'] : 'AVI — Accès Plateforme';
        $flat['line_items[0][price_data][product_data][description]'] = isset( $body['line_items'][0]['price_data']['product_data']['description'] ) ? $body['line_items'][0]['price_data']['product_data']['description'] : '';
        $flat['line_items[0][price_data][unit_amount]'] = isset( $body['line_items'][0]['price_data']['unit_amount'] ) ? $body['line_items'][0]['price_data']['unit_amount'] : 9900;
        if ( ! empty( $body['line_items'][0]['price_data']['recurring']['interval'] ) ) {
            $flat['line_items[0][price_data][recurring][interval]'] = $body['line_items'][0]['price_data']['recurring']['interval'];
        }
        $flat['line_items[0][quantity]'] = isset( $body['line_items'][0]['quantity'] ) ? (int) $body['line_items'][0]['quantity'] : 1;
    }
    foreach ( isset( $body['metadata'] ) ? $body['metadata'] : [] as $k => $v ) {
        $flat[ 'metadata[' . $k . ']' ] = is_string( $v ) ? $v : (string) $v;
    }
    return http_build_query( $flat );
}

/**
 * Résout l’UUID profil (pid URL) pour une ligne avi_saas_profiles.id.
 *
 * @param int $profile_id ID interne profil.
 * @return string UUID normalisé ou chaîne vide.
 */
function avision_resolve_profile_uuid_for_checkout( $profile_id ) {
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 ) {
		return '';
	}
	global $wpdb;
	if ( ! isset( $wpdb ) || ! $wpdb ) {
		return '';
	}
	$candidates = array( 'wor1515_avi_saas_profiles', $wpdb->prefix . 'avi_saas_profiles' );
	foreach ( array_unique( $candidates ) as $tbl ) {
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl ) ) !== $tbl ) {
			continue;
		}
		if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$tbl}` LIKE %s", 'uuid' ) ) !== 'uuid' ) {
			continue;
		}
		$u = (string) $wpdb->get_var( $wpdb->prepare( "SELECT uuid FROM `{$tbl}` WHERE id = %d LIMIT 1", $profile_id ) );
		if ( $u !== '' && preg_match( '/^[a-f0-9\-]{32,36}$/i', $u ) ) {
			return strtolower( trim( $u ) );
		}
	}
	if ( function_exists( 'avi_get_or_create_profile_uuid' ) ) {
		$u = (string) avi_get_or_create_profile_uuid( $profile_id );
		if ( $u !== '' && preg_match( '/^[a-f0-9\-]{32,36}$/i', $u ) ) {
			return strtolower( trim( $u ) );
		}
	}
	return '';
}

/**
 * Valide une session Checkout Stripe payée, crée la session SaaS, construit l’URL cockpit avec ?pid=.
 *
 * @param string $session_id ID session cs_….
 * @return array{success:bool,error?:string,redirect?:string,sid?:string|null,pid?:string,user_type?:string}
 */
function avision_complete_stripe_checkout_return( $session_id ) {
	$session_id = is_string( $session_id ) ? trim( sanitize_text_field( $session_id ) ) : '';
	if ( $session_id === '' || strpos( $session_id, 'cs_' ) !== 0 ) {
		return array( 'success' => false, 'error' => 'invalid_session_id' );
	}
	$secret_key = defined( 'AVISION_STRIPE_SECRET_KEY' ) && is_string( AVISION_STRIPE_SECRET_KEY ) && trim( AVISION_STRIPE_SECRET_KEY ) !== ''
		? trim( AVISION_STRIPE_SECRET_KEY )
		: ( is_string( get_option( 'avision_stripe_secret_key', '' ) ) ? trim( (string) get_option( 'avision_stripe_secret_key', '' ) ) : '' );
	if ( empty( $secret_key ) ) {
		return array( 'success' => false, 'error' => 'stripe_not_configured' );
	}
	$res = wp_remote_get(
		'https://api.stripe.com/v1/checkout/sessions/' . rawurlencode( $session_id ),
		array(
			'headers' => array( 'Authorization' => 'Bearer ' . $secret_key ),
			'timeout' => 15,
		)
	);
	$code = wp_remote_retrieve_response_code( $res );
	$body = wp_remote_retrieve_body( $res );
	$data = json_decode( $body, true );
	if ( is_wp_error( $res ) || $code !== 200 || ! is_array( $data ) ) {
		return array( 'success' => false, 'error' => 'stripe_session_invalid' );
	}
	$payment_status = isset( $data['payment_status'] ) ? $data['payment_status'] : '';
	if ( $payment_status !== 'paid' ) {
		return array( 'success' => false, 'error' => 'payment_not_completed' );
	}
	$meta = isset( $data['metadata'] ) && is_array( $data['metadata'] ) ? $data['metadata'] : array();
	$email = '';
	if ( isset( $data['customer_details'] ) && is_array( $data['customer_details'] ) && ! empty( $data['customer_details']['email'] ) ) {
		$email = (string) $data['customer_details']['email'];
	} elseif ( isset( $data['customer_email'] ) ) {
		$email = (string) $data['customer_email'];
	} elseif ( isset( $meta['email'] ) ) {
		$email = (string) $meta['email'];
	}
	$email = is_email( $email ) ? $email : '';
	if ( $email === '' ) {
		return array( 'success' => false, 'error' => 'no_email' );
	}
	$user_type = isset( $meta['user_type'] ) ? strtoupper( trim( (string) $meta['user_type'] ) ) : 'B2C';
	$redirect_path = ( $user_type === 'B2B' ) ? '/agency-dashboard' : '/premium-dashboard';

	$sid = null;
	$profile_row_id = 0;
	if ( class_exists( 'Avision\Avision_Database' ) ) {
		$db = new \Avision\Avision_Database();
		$db->init();
		$saas_user_id = $db->get_or_create_saas_user( $email );
		if ( $saas_user_id ) {
			$first = $db->get_first_saas_profile_by_user_id( $saas_user_id );
			if ( $first && ! empty( $first['id'] ) ) {
				$profile_row_id = (int) $first['id'];
			} elseif ( method_exists( $db, 'create_saas_profile' ) ) {
				$bn = isset( $meta['company_name'] ) ? (string) $meta['company_name'] : '';
				$ct = isset( $meta['city'] ) ? (string) $meta['city'] : '';
				$cat = isset( $meta['category'] ) ? (string) $meta['category'] : '';
				$nbh = isset( $meta['neighborhood'] ) ? (string) $meta['neighborhood'] : '';
				$country = isset( $meta['country'] ) ? (string) $meta['country'] : '';
				$country = strtoupper( trim( $country ) );
				$country = in_array( $country, array( 'FR', 'GB', 'US', 'ES' ), true ) ? $country : 'FR';
				$profile = $db->create_saas_profile(
					$saas_user_id,
					$bn !== '' ? $bn : __( 'Profil', 'avision-dashboard' ),
					$ct,
					$cat !== '' ? $cat : __( 'Client', 'avision-dashboard' ),
					null,
					null,
					$nbh,
					$country
				);
				if ( is_array( $profile ) && ! empty( $profile['id'] ) ) {
					$profile_row_id = (int) $profile['id'];
				}
			}
			if ( $profile_row_id > 0 ) {
				$session_row = $db->create_saas_session( $saas_user_id, $profile_row_id, 604800 );
				if ( $session_row && ! empty( $session_row['token'] ) ) {
					$sid = $session_row['token'];
				}
			}
		}
	}

	// Premium B2C : seeds Radar + scan immédiat pour ne pas arriver sur un cockpit vide.
	if ( $profile_row_id > 0 && $redirect_path === '/premium-dashboard' && function_exists( 'avision_force_immediate_scan' ) ) {
		avision_force_immediate_scan( $profile_row_id );
	}

	$pid_uuid = $profile_row_id > 0 ? avision_resolve_profile_uuid_for_checkout( $profile_row_id ) : '';

	$redirect_url = trailingslashit( home_url( $redirect_path ) );
	$redirect_url = add_query_arg( 'status', 'success', $redirect_url );
	if ( $pid_uuid !== '' ) {
		$redirect_url = add_query_arg( 'pid', $pid_uuid, $redirect_url );
	}
	if ( $sid ) {
		$redirect_url = add_query_arg( 'sid', $sid, $redirect_url );
	}

	return array(
		'success'   => true,
		'redirect'  => $redirect_url,
		'sid'       => $sid,
		'pid'       => $pid_uuid,
		'user_type' => ( $user_type === 'B2B' ) ? 'b2b' : 'b2c',
		'email'     => $email,
		'profile_id'=> $profile_row_id,
	);
}

/**
 * Point d’entrée page : ?session_id=cs_… après Hosted Checkout — cookie SaaS + redirect propre.
 *
 * @return bool True si une redirection a été envoyée.
 */
function avision_handle_stripe_checkout_landing() {
	if ( empty( $_GET['session_id'] ) || ! is_string( $_GET['session_id'] ) ) {
		return false;
	}
	$session_id = sanitize_text_field( wp_unslash( $_GET['session_id'] ) );
	if ( strpos( $session_id, 'cs_' ) !== 0 ) {
		return false;
	}
	$out = avision_complete_stripe_checkout_return( $session_id );
	if ( empty( $out['success'] ) || empty( $out['redirect'] ) ) {
		return false;
	}
	if ( ! empty( $out['sid'] ) && ! headers_sent() ) {
		$secure = function_exists( 'is_ssl' ) && is_ssl();
		setcookie( 'avision_session_token', $out['sid'], time() + 604800, '/', '', $secure, true );
		$_COOKIE['avision_session_token'] = $out['sid'];
	}
	if ( function_exists( 'wp_safe_redirect' ) ) {
		wp_safe_redirect( $out['redirect'] );
		return true;
	}
	return false;
}

/**
 * Pont de succès Stripe → WordPress (auto-login) + liaison profil.
 *
 * - Détecte ?session_id=cs_… (retour Stripe Hosted Checkout)
 * - Vérifie via Stripe API (via avision_complete_stripe_checkout_return)
 * - Connecte (ou crée) l’utilisateur WP par email
 * - Lie wp_user_id au profil SaaS si possible
 * - Nettoie l’URL : redirige vers /premium-dashboard/?status=success&pid=... (sans session_id)
 */

/**
 * Vérifie la session Stripe (retour après paiement) et retourne l'URL de redirection + sid pour connexion.
 * Utilisé par la page connexion (login-logic.js) si success_url pointe encore vers /connexion/.
 */
function avision_rest_connexion_verify( \WP_REST_Request $request ) {
	$session_id = $request->get_param( 'session_id' );
	if ( empty( $session_id ) || strpos( (string) $session_id, 'cs_' ) !== 0 ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'invalid_session_id' ], 400 );
	}
	$out = avision_complete_stripe_checkout_return( (string) $session_id );
	if ( empty( $out['success'] ) ) {
		$err = isset( $out['error'] ) ? (string) $out['error'] : 'stripe_error';
		$map = array(
			'stripe_not_configured'   => 503,
			'invalid_session_id'      => 400,
			'payment_not_completed'   => 400,
			'no_email'                => 400,
			'stripe_session_invalid'  => 502,
		);
		$status = isset( $map[ $err ] ) ? $map[ $err ] : 400;
		return new \WP_REST_Response( [ 'success' => false, 'error' => $err ], $status );
	}
	return new \WP_REST_Response( [
		'success'   => true,
		'redirect'  => $out['redirect'],
		'user_type' => isset( $out['user_type'] ) ? $out['user_type'] : 'b2c',
		'sid'       => isset( $out['sid'] ) ? $out['sid'] : null,
		'pid'       => isset( $out['pid'] ) ? $out['pid'] : '',
	], 200 );
}

/**
 * Retourne l'ID profil (avi_saas_profiles.id) à partir de l'UUID (paramètre pid en URL).
 *
 * @param string $uuid UUID du profil (36 caractères).
 * @return int 0 si non trouvé.
 */
function avision_get_profile_id_by_uuid( $uuid ) {
	$uuid = is_string( $uuid ) ? trim( $uuid ) : '';
	if ( $uuid === '' || ! preg_match( '/^[a-f0-9\-]{32,36}$/i', $uuid ) ) {
		return 0;
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_profiles';
	$id    = (int) $wpdb->get_var( $wpdb->prepare(
		"SELECT id FROM `{$table}` WHERE uuid = %s LIMIT 1",
		$uuid
	) );
	return $id;
}

/**
 * REST : statut CRON pour une fiche (dernier scan, stats Gardien, derniers logs, derniers posts).
 * GET /wp-json/avision/v1/cron-status?profile_id=123 ou ?pid=uuid
 */
function avision_rest_cron_status( \WP_REST_Request $request ) {
	$profile_id = (int) $request->get_param( 'profile_id' );
	if ( $profile_id <= 0 ) {
		$pid = $request->get_param( 'pid' );
		if ( $pid && function_exists( 'avision_get_profile_id_by_uuid' ) ) {
			$profile_id = avision_get_profile_id_by_uuid( $pid );
		}
	}
	if ( $profile_id <= 0 ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'profile_id ou pid invalide' ], 400 );
	}
	global $wpdb;
	$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
	$t_posts    = $wpdb->prefix . 'avi_saas_posts';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t_profiles ) ) !== $t_profiles ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'Table profils manquante. Installez les tables cœeur (AVI → Tables cœur).' ], 503 );
	}
	$schema = defined( 'DB_NAME' ) ? DB_NAME : '';
	if ( $schema !== '' ) {
		$col = $wpdb->get_results( $wpdb->prepare(
			"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'guardian_active'",
			$schema,
			$t_profiles
		) );
		if ( empty( $col ) ) {
			$wpdb->query( "ALTER TABLE `{$t_profiles}` ADD COLUMN guardian_active SMALLINT DEFAULT 0" );
		}
	}
	$profile = $wpdb->get_row( $wpdb->prepare(
		"SELECT id, user_id, business_name, last_radar_scan, radar_plan, COALESCE(guardian_active, 0) AS guardian_active FROM `{$t_profiles}` WHERE id = %d LIMIT 1",
		$profile_id
	), ARRAY_A );
	if ( ! $profile ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'Profil introuvable (id=' . $profile_id . ').' ], 404 );
	}
	if ( ! current_user_can( 'manage_options' ) && function_exists( 'avi_current_user_can_access_profile' ) && ! avi_current_user_can_access_profile( $profile_id ) ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'accès non autorisé à cette fiche' ], 403 );
	}
	$user_id = (int) $profile['user_id'];
	$guardian_active = ( (int) ( $profile['guardian_active'] ?? 0 ) ) === 1;
	$guardian_stats = [];
	$logs = [];
	if ( class_exists( 'Avision\Avision_Guardian_Watchers' ) ) {
		$guardian_stats = \Avision\Avision_Guardian_Watchers::get_guardian_stats( $user_id );
		$logs           = \Avision\Avision_Guardian_Watchers::get_guardian_logs( $user_id, 20 );
	}
	$posts = [];
	if ( $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $t_posts ) ) === $t_posts ) {
		$posts = $wpdb->get_results( $wpdb->prepare(
			"SELECT id, title, status, created_at FROM `{$t_posts}` WHERE profile_id = %d ORDER BY created_at DESC LIMIT 10",
			$profile_id
		), ARRAY_A );
		if ( ! is_array( $posts ) ) {
			$posts = [];
		}
	}
	return new \WP_REST_Response( [
		'success'          => true,
		'profile_id'       => $profile_id,
		'user_id'          => $user_id,
		'business_name'    => isset( $profile['business_name'] ) ? $profile['business_name'] : '',
		'last_radar_scan'  => isset( $profile['last_radar_scan'] ) ? $profile['last_radar_scan'] : null,
		'radar_plan'       => isset( $profile['radar_plan'] ) ? $profile['radar_plan'] : 'offert',
		'guardian_active'  => $guardian_active,
		'guardian_stats'   => $guardian_stats,
		'logs'             => $logs,
		'posts'            => $posts,
	], 200 );
}

/**
 * REST : lancer l'orchestrateur CRON pour une fiche (admin uniquement).
 * POST /wp-json/avision/v1/cron-run-profile avec body { "profile_id": 123 }.
 * Vérification manage_options AVANT tout chargement ; erreur exacte dans wp-content/debug.log.
 */
function avision_rest_cron_run_profile( \WP_REST_Request $request ) {
	// Dépendances média WP — chemins absolus (ABSPATH)
	if ( defined( 'ABSPATH' ) ) {
		require_once ABSPATH . 'wp-admin/includes/file.php';
		require_once ABSPATH . 'wp-admin/includes/image.php';
		require_once ABSPATH . 'wp-admin/includes/media.php';
	}
	if ( ! function_exists( 'current_user_can' ) || ! current_user_can( 'manage_options' ) ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'Non autorisé (manage_options requis)' ], 403 );
	}

	try {
		$json_params = method_exists( $request, 'get_json_params' ) ? $request->get_json_params() : null;
		$data        = is_array( $json_params ) ? $json_params : [];
		$profile_id  = isset( $data['profile_id'] ) ? (int) $data['profile_id'] : (int) $request->get_param( 'profile_id' );
		if ( $profile_id <= 0 && ! empty( $data['pid'] ) && is_string( $data['pid'] ) && function_exists( 'avision_get_profile_id_by_uuid' ) ) {
			$profile_id = (int) avision_get_profile_id_by_uuid( trim( $data['pid'] ) );
		}
		if ( $profile_id <= 0 ) {
			return new \WP_REST_Response( [ 'success' => false, 'error' => 'profile_id ou pid invalide/manquant (body JSON: profile_id ou pid UUID)' ], 400 );
		}
		$includes_dir = defined( 'AVISION_INCLUDES_DIR' ) ? AVISION_INCLUDES_DIR : ( defined( 'AVISION_PATH' ) ? AVISION_PATH . 'includes/' : '' );
		if ( $includes_dir === '' ) {
			return new \WP_REST_Response( [ 'success' => false, 'error' => 'AVISION_INCLUDES_DIR/AVISION_PATH non définis' ], 500 );
		}
		$cron_file = $includes_dir . 'cron-task-manager.php';
		if ( ! file_exists( $cron_file ) ) {
			return new \WP_REST_Response( [ 'success' => false, 'error' => 'Fichier cron-task-manager.php introuvable.', 'debug_hint' => 'Chemin testé: ' . $cron_file ], 500 );
		}
		require_once $cron_file;
		$scan_engine = $includes_dir . 'class-avision-scan-engine.php';
		if ( file_exists( $scan_engine ) ) {
			require_once $scan_engine;
		}
		$manager = new \Avision\Cron_Task_Manager();
		if ( ! method_exists( $manager, 'run_for_profile' ) ) {
			return new \WP_REST_Response( [ 'success' => false, 'error' => 'run_for_profile non disponible' ], 503 );
		}
		$result = $manager->run_for_profile( $profile_id );
		return new \WP_REST_Response( [
			'success' => true,
			'message' => 'CRON exécuté pour cette fiche.',
			'result'  => $result,
		], 200 );
	} catch ( \Throwable $e ) {
		$msg  = $e->getMessage();
		$file = $e->getFile();
		$line = $e->getLine();
		error_log( 'AVI ERROR: ' . $msg . ' in ' . $file . ':' . $line );
		$log_line  = sprintf( "[%s] AVI cron-run-profile ERROR: %s in %s:%d\n", gmdate( 'Y-m-d H:i:s' ), $msg, $file, $line );
		$debug_log = ( defined( 'WP_CONTENT_DIR' ) ? WP_CONTENT_DIR : ( defined( 'ABSPATH' ) ? rtrim( ABSPATH, '/\\' ) . 'wp-content' : '' ) ) . '/debug.log';
		if ( $debug_log !== '/debug.log' && ( is_writable( $debug_log ) || ( ! file_exists( $debug_log ) && is_writable( dirname( $debug_log ) ) ) ) ) {
			@file_put_contents( $debug_log, $log_line, FILE_APPEND | LOCK_EX );
		}
		return new \WP_REST_Response( [
			'success'    => false,
			'error'     => 'Erreur lors de l\'exécution du CRON.',
			'message'    => ( defined( 'WP_DEBUG' ) && WP_DEBUG ) ? $msg : null,
			'debug_hint' => ( defined( 'WP_DEBUG' ) && WP_DEBUG ) ? ( $msg . ' in ' . basename( $file ) . ':' . $line ) : 'Consultez wp-content/debug.log ou logs/cron-task.log. Activez WP_DEBUG pour le détail ici.',
		], 500 );
	}
}

/**
 * REST : lance le scan de démonstration (même flux que admin.php?page=avi-install-core-tables&avi_demo_scan=1).
 * Injecte des logs dans avi_security_logs pour l'utilisateur courant et, si profile_id est fourni, un post de test (image + texte) dans "Intervention en cours".
 * Admin uniquement.
 *
 * @param \WP_REST_Request $request
 * @return \WP_REST_Response
 */
function avision_rest_demo_scan( \WP_REST_Request $request ) {
	if ( ! current_user_can( 'manage_options' ) ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'Non autorisé (manage_options requis)' ], 403 );
	}
	if ( ! class_exists( 'Avision\AVI_Tester' ) || ! \Avision\AVI_Tester::can_run_demo_scan() ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'Scan de démo non disponible' ], 503 );
	}
	$user_id = get_current_user_id();
	$json    = method_exists( $request, 'get_json_params' ) ? $request->get_json_params() : null;
	$data    = is_array( $json ) ? $json : [];
	$profile_id = isset( $data['profile_id'] ) ? (int) $data['profile_id'] : (int) $request->get_param( 'profile_id' );

	try {
		$tester = new \Avision\AVI_Tester();
		$res    = $tester->simulate_full_protection( $user_id, $profile_id );
		return new \WP_REST_Response( [
			'success'    => true,
			'message'    => isset( $res['message'] ) ? $res['message'] : 'Scan de démo exécuté.',
			'user_id'    => $user_id,
			'demo_post'  => ! empty( $res['demo_post'] ),
		], 200 );
	} catch ( \Throwable $e ) {
		if ( defined( 'WP_DEBUG' ) && WP_DEBUG && function_exists( 'error_log' ) ) {
			error_log( '[AVI demo-scan] ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine() );
		}
		return new \WP_REST_Response( [
			'success' => false,
			'error'   => 'Erreur lors du scan de démo.',
			'message' => ( defined( 'WP_DEBUG' ) && WP_DEBUG ) ? $e->getMessage() : null,
		], 200 );
	}
}

/**
 * REST : lance le First Sync (Google + scan 3 mots-clés Radar + calcul score + rank_history).
 * POST /wp-json/avision/v1/first-sync avec body { "profile_id": 123 }.
 * Répond immédiatement avec {"status": "sync_started"} pour éviter le 502 (timeout).
 * La sync réelle s'exécute après envoi de la réponse (shutdown), en arrière-plan.
 *
 * @param \WP_REST_Request $request
 * @return \WP_REST_Response
 */
function avision_rest_first_sync( \WP_REST_Request $request ) {
    if ( ! session_id() ) {
        @session_start();
    }

    $profile_id = (int) $request->get_param( 'profile_id' );
    if ( $profile_id <= 0 ) {
        return new \WP_REST_Response( [ 'success' => false, 'error' => 'profile_id invalide' ], 400 );
    }

    ignore_user_abort( true );
    error_log( '[Avision First Sync] REST: returning sync_started immediately for profile_id=' . $profile_id );

    return new \WP_REST_Response( [
        'status'  => 'sync_started',
        'success' => true,
    ], 200 );
}

/**
 * Réponse REST radar-keywords vide (table absente, erreur SQL douce, ou aucune ligne).
 *
 * @param int         $profile_id
 * @param string|null $error      Message optionnel si WP_DEBUG.
 * @return \WP_REST_Response
 */
function avision_radar_keywords_empty_response( $profile_id, $error = null ) {
	$body = [
		'keywords' => [],
		'total'    => 0,
	];
	if ( defined( 'WP_DEBUG' ) && WP_DEBUG && $error !== null && $error !== '' ) {
		$body['error']            = $error;
		$body['debug_profile_id'] = (int) $profile_id;
	} else {
		$body['debug_profile_id'] = (int) $profile_id;
	}
	return new \WP_REST_Response( $body, 200 );
}

/**
 * REST : mots-clés du Radar (même format que le backend Python).
 * GET /wp-json/avision/v1/radar-keywords?profile_id=X&limit=75
 * Utilise $wpdb pour lire/écrire avi_saas_keywords (BDD du site). Si vide et profil valide (category+city), crée les 3 mots-clés initiaux puis re-lit.
 * Zéro fallback statique : résultat vide = JSON { keywords: [], total: 0 } via WP_REST_Response (pas de die / exit).
 *
 * @param \WP_REST_Request $request
 * @return \WP_REST_Response
 */
function avision_rest_radar_keywords( \WP_REST_Request $request ) {
	global $wpdb;
	$profile_id = (int) $request->get_param( 'profile_id' );
	$limit      = max( 1, min( 75, (int) $request->get_param( 'limit' ) ) );
	$force      = (bool) $request->get_param( 'force' );
	if ( $profile_id <= 0 || ! isset( $wpdb ) || ! $wpdb ) {
		return avision_radar_keywords_empty_response( $profile_id > 0 ? $profile_id : 0, null );
	}
	$pre     = ! empty( $wpdb->prefix ) ? $wpdb->prefix : '';
	$tbl_kw  = $pre . 'avi_saas_keywords';
	$tbl_pro = $pre . 'avi_saas_profiles';

	// Table absente ou inaccessible : JSON vide (évite HTML d’erreur SQL).
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl_kw ) ) !== $tbl_kw ) {
		return avision_radar_keywords_empty_response( $profile_id, 'Table mots-clés indisponible' );
	}

	$get_rows = function() use ( $wpdb, $profile_id, $limit, $tbl_kw ) {
		$sql = $wpdb->prepare(
			"SELECT id, keyword, COALESCE(rank_init, previous_rank, 0) AS rank_init, current_rank, scan_status FROM `" . $tbl_kw . "` WHERE profile_id = %d ORDER BY is_strategic DESC, id ASC LIMIT %d",
			$profile_id,
			$limit
		);
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) || ( ! empty( $wpdb->last_error ) && strpos( $wpdb->last_error, 'rank_init' ) !== false ) ) {
			$sql = $wpdb->prepare(
				"SELECT id, keyword, COALESCE(previous_rank, 0) AS rank_init, current_rank, scan_status FROM `" . $tbl_kw . "` WHERE profile_id = %d ORDER BY is_strategic DESC, id ASC LIMIT %d",
				$profile_id,
				$limit
			);
			$rows = $wpdb->get_results( $sql, ARRAY_A );
		}
		return is_array( $rows ) ? $rows : [];
	};

	$rows = $get_rows();
	if ( ! empty( $wpdb->last_error ) ) {
		return avision_radar_keywords_empty_response( $profile_id, 'Erreur lecture mots-clés' );
	}
	if ( empty( $rows ) || $force ) {
		$profile = $wpdb->get_row( $wpdb->prepare( "SELECT category, city, neighborhood FROM `" . $tbl_pro . "` WHERE id = %d LIMIT 1", $profile_id ), ARRAY_A );
		if ( ! empty( $wpdb->last_error ) ) {
			return avision_radar_keywords_empty_response( $profile_id, 'Erreur lecture profil' );
		}
		$cat   = $profile ? trim( (string) ( isset( $profile['category'] ) ? $profile['category'] : '' ) ) : '';
		$city  = $profile ? trim( (string) ( isset( $profile['city'] ) ? $profile['city'] : '' ) ) : '';
		$neigh = $profile ? trim( (string) ( isset( $profile['neighborhood'] ) ? $profile['neighborhood'] : '' ) ) : '';
		if ( $cat === '-' ) {
			$cat = '';
		}
		if ( $city === '-' ) {
			$city = '';
		}
		if ( $neigh === '-' ) {
			$neigh = '';
		}
		$can_generate = $profile && $cat !== '' && $city !== '';
		if ( $can_generate && function_exists( 'avi_generate_initial_keywords' ) ) {
			avi_generate_initial_keywords( $profile_id );
			$rows = $get_rows();
			if ( ! empty( $wpdb->last_error ) ) {
				return avision_radar_keywords_empty_response( $profile_id, 'Erreur après génération initiale' );
			}
		}
	}

	$result = [];
	foreach ( $rows as $r ) {
		$rank_init = isset( $r['rank_init'] ) ? (int) $r['rank_init'] : 0;
		$rank_curr = isset( $r['current_rank'] ) ? $r['current_rank'] : null;
		if ( $rank_curr !== null && in_array( (int) $rank_curr, [ 1, 2, 3 ], true ) ) {
			$rank_curr = 'TOP 3';
		}
		$keyword_str = isset( $r['keyword'] ) ? trim( (string) $r['keyword'] ) : '';
		if ( function_exists( 'error_log' ) ) {
			error_log( 'API Radar (plugin): Sending keyword ' . $keyword_str . ' with rank_init: ' . $rank_init . ', scan_status: ' . ( isset( $r['scan_status'] ) ? $r['scan_status'] : 'N/A' ) );
		}
		$result[] = [
			'keyword'   => $keyword_str,
			'rankInit'  => $rank_init,
			'rank_init' => $rank_init,
			'rankCurr'  => $rank_curr,
			'volume'    => 0,
		];
	}

	return new \WP_REST_Response( [
		'keywords'         => $result,
		'total'            => count( $result ),
		'debug_profile_id' => $profile_id,
	], 200 );
}

/**
 * REST : ajout en lot des mots-clés choisis par l'utilisateur (Queue Manager).
 * POST /wp-json/avision/v1/add-keywords-batch
 * Body: { profile_id: number, keywords: string[] }
 * Respecte la limite du plan (offert 20, hebdo 75, elite 100) et marque les nouveaux en pending_scan.
 *
 * @param \WP_REST_Request $request
 * @return \WP_REST_Response
 */
function avision_rest_add_keywords_batch( \WP_REST_Request $request ) {
	$profile_id = (int) $request->get_param( 'profile_id' );
	$keywords   = $request->get_param( 'keywords' );
	if ( ! is_array( $keywords ) ) {
		$keywords = [];
	}
	$keywords = array_map( 'sanitize_text_field', $keywords );

	if ( $profile_id <= 0 ) {
		return new \WP_REST_Response( [ 'error' => 'profile_id requis', 'inserted' => 0, 'limit' => 20, 'current_total' => 0 ], 400 );
	}

	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return new \WP_REST_Response( [ 'error' => 'Database unavailable', 'inserted' => 0, 'limit' => 20, 'current_total' => 0 ], 503 );
	}

	$db     = new \Avision\Avision_Database();
	$db->init();
	$result = $db->add_suggested_keywords_to_profile( $profile_id, $keywords );
	if ( empty( $result ) ) {
		$result = [ 'inserted' => 0, 'limit' => 20, 'current_total' => 0 ];
	}
	return new \WP_REST_Response( array_merge( [ 'success' => (int) ( $result['inserted'] ?? 0 ) > 0 ], $result ), 200 );
}

/**
 * REST : suppression d'un mot-clé du Radar.
 * POST /wp-json/avision/v1/delete-keyword
 * Body: { profile_id: number, keyword: string }
 */
function avision_rest_delete_keyword( \WP_REST_Request $request ) {
	$profile_id = (int) $request->get_param( 'profile_id' );
	$keyword    = trim( (string) $request->get_param( 'keyword' ) );

	if ( $profile_id <= 0 || $keyword === '' ) {
		return new \WP_REST_Response( [ 'success' => false, 'error' => 'profile_id et keyword requis' ], 400 );
	}

	global $wpdb;
	$pre   = ! empty( $wpdb->prefix ) ? $wpdb->prefix : '';
	$table = $pre . 'avi_saas_keywords';
	$deleted = $wpdb->delete( $table, [
		'profile_id' => $profile_id,
		'keyword'    => $keyword,
	], [ '%d', '%s' ] );

	if ( function_exists( 'error_log' ) ) {
		error_log( '[AVI] delete-keyword: profile=' . $profile_id . ' keyword="' . $keyword . '" deleted=' . ( $deleted ? $deleted : '0' ) );
	}

	return new \WP_REST_Response( [ 'success' => $deleted > 0, 'deleted' => (int) $deleted ], 200 );
}

/**
 * Génère et insère les 3 mots-clés initiaux du Radar (First Sync) pour un profil fraîchement créé.
 * Kw1 : category + city ; Kw2 : category + neighborhood (si présent) ; Kw3 : category + proximité locale (near me / à proximité / cerca de mi).
 * À appeler juste après l'insertion du profil (onboarding).
 *
 * @param int $profile_id ID du profil (avi_saas_profiles.id)
 * @return bool True si au moins un mot-clé a été inséré
 */
function avi_generate_initial_keywords($profile_id) {
    $profile_id = (int) $profile_id;
    if ($profile_id <= 0) return false;
    if (!class_exists('Avision\Avision_Database')) return false;
    $db = new \Avision\Avision_Database();
    $db->init();
    if (!method_exists($db, 'insert_initial_keywords_for_profile')) return false;
    return $db->insert_initial_keywords_for_profile($profile_id);
}

/**
 * Récupère les positions des 15 mots-clés pour plusieurs audits (une seule requête SQL).
 * Pour le dashboard agence (Ranking Heatmap).
 *
 * @param int[] $audit_ids
 * @return array [ audit_id => ['has_top3' => bool, 'keywords' => [ 1..15 => ['keyword'=>'...', 'position'=>int|null] ] ] ]
 */
function avision_get_keyword_ranks_for_audits(array $audit_ids) {
	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return array();
	}
	$db = new \Avision\Avision_Database();
	$db->init();
	if ( ! method_exists( $db, 'get_keyword_ranks_for_audits' ) ) {
		return array();
	}
	return $db->get_keyword_ranks_for_audits( $audit_ids );
}

/**
 * Crée ou récupère le profil "ADMIN TEST STORE" et une session réelle pour un administrator.
 * Permet de sauvegarder la progression des niveaux. Force current_level à 1 pour commencer le parcours.
 *
 * @return array|null ['user_id' => int, 'profile_id' => int, 'profile' => array, 'is_premium' => true] ou null si erreur
 */
function avision_get_or_create_admin_test_session() {
	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return null;
	}
	$wp_user_id = function_exists( 'get_current_user_id' ) ? (int) get_current_user_id() : 0;
	$admin_email = 'admin-test-store@avision.local';
	if ( $wp_user_id && function_exists( 'get_userdata' ) ) {
		$user_data = get_userdata( $wp_user_id );
		if ( $user_data && ! empty( $user_data->user_email ) ) {
			$admin_email = trim( $user_data->user_email );
		}
	}
	if ( $admin_email === '' ) {
		$admin_email = 'admin-test-store@avision.local';
	}

	$db = new \Avision\Avision_Database();
	$db->init();
	if ( ! $db->get_connection() ) {
		return null;
	}

	$user_id = $db->get_or_create_saas_user( $admin_email );
	if ( ! $user_id ) {
		return null;
	}

	$profile = $db->get_saas_profile_by_user_and_business_name( $user_id, 'ADMIN TEST STORE' );
	if ( ! $profile ) {
		$created = $db->create_saas_profile( $user_id, 'ADMIN TEST STORE', '', '', null, 0, '' );
		if ( ! $created ) {
			return null;
		}
		if ( function_exists( 'avi_generate_initial_keywords' ) ) {
			avi_generate_initial_keywords( $created['id'] );
		}
		$profile = $db->get_saas_profile_by_id( $created['id'] );
		if ( ! $profile ) {
			return null;
		}
	} else {
		$db->update_saas_profile_current_level( $profile['id'], 1 );
		$profile = $db->get_saas_profile_by_id( $profile['id'] );
		if ( $profile ) {
			$profile['current_level'] = 1;
		}
	}

	$session_data = $db->create_saas_session( $user_id, $profile['id'], 604800 );
	if ( ! $session_data || empty( $session_data['token'] ) ) {
		return null;
	}

	if ( function_exists( 'setcookie' ) ) {
		@setcookie( 'avision_session_token', $session_data['token'], time() + 604800, '/', '', function_exists( 'is_ssl' ) && is_ssl(), true );
	}

	return array(
		'user_id'    => (int) $user_id,
		'profile_id' => (int) $profile['id'],
		'profile'    => $profile,
		'is_premium' => true,
	);
}

/**
 * Retourne une session virtuelle pour administrateur (profile_id = 0) quand la BDD est indisponible.
 * Évite toute redirection vers la home : le dashboard s'affiche avec des données de test.
 *
 * @return array
 */
function avision_admin_virtual_session() {
	$wp_user_id = function_exists( 'get_current_user_id' ) ? (int) get_current_user_id() : 0;
	return array(
		'user_id'    => $wp_user_id,
		'profile_id' => 0,
		'profile'    => array(
			'id'            => 0,
			'business_name' => 'ADMIN TEST STORE',
			'city'          => '',
			'category'      => '',
			'current_level' => 1,
			'score'         => 0,
		),
		'is_premium' => true,
	);
}

/**
 * Valide la session dashboard (cookie avision_session_token ou paramètre sid).
 * À appeler au début des pages premium-dashboard et demo-dashboard.
 * Règle admin prioritaire : si current_user_can('administrator'), on ne renvoie jamais null.
 * On tente d'abord de créer/récupérer le profil "ADMIN TEST STORE" en BDD ; si échec, on renvoie une session virtuelle (profile_id = 0).
 *
 * @return array|null ['user_id' => int, 'profile_id' => int, 'profile' => array, 'is_premium' => bool] ou null si non authentifié
 */
function avision_validate_session() {
	$is_administrator = function_exists( 'current_user_can' ) && current_user_can( 'administrator' );

	if ( $is_administrator ) {
		$admin_session = avision_get_or_create_admin_test_session();
		if ( $admin_session !== null ) {
			return $admin_session;
		}
		return avision_admin_virtual_session();
	}

	$token = '';
	if ( ! empty( $_COOKIE['avision_session_token'] ) ) {
		$token = sanitize_text_field( wp_unslash( $_COOKIE['avision_session_token'] ) );
	} elseif ( ! empty( $_GET['sid'] ) ) {
		$token = sanitize_text_field( wp_unslash( $_GET['sid'] ) );
	}
	if ( $token === '' && ! empty( $_GET['pre'] ) && function_exists( 'avision_get_pre_inscription_by_token' ) ) {
		$pre_tok = sanitize_text_field( wp_unslash( $_GET['pre'] ) );
		$pre_row = avision_get_pre_inscription_by_token( $pre_tok );
		if ( is_array( $pre_row ) && isset( $pre_row['type'] ) && $pre_row['type'] === 'b2c' ) {
			return array(
				'user_id'              => 0,
				'profile_id'           => 0,
				'profile'              => array(
					'id'            => 0,
					'business_name' => isset( $pre_row['business_name'] ) ? $pre_row['business_name'] : '',
					'city'          => isset( $pre_row['city'] ) ? $pre_row['city'] : '',
					'neighborhood'  => isset( $pre_row['neighborhood'] ) ? $pre_row['neighborhood'] : '',
					'category'      => isset( $pre_row['category'] ) ? $pre_row['category'] : '',
					'country'       => isset( $pre_row['country'] ) ? $pre_row['country'] : 'FR',
					'current_level' => 1,
					'score'         => isset( $pre_row['score'] ) ? (int) $pre_row['score'] : 0,
				),
				'is_premium'           => false,
				'pre_inscription_only' => true,
				'pre_token'            => $pre_tok,
			);
		}
	}

	if ( $token === '' ) {
		return null;
	}

	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return null;
	}
	$db = new \Avision\Avision_Database();
	$db->init();
	$session = $db->get_saas_session_by_token( $token );
	if ( ! $session ) {
		return null;
	}
	if ( ! empty( $_GET['sid'] ) && function_exists( 'setcookie' ) ) {
		@setcookie( 'avision_session_token', $token, time() + 604800, '/', '', is_ssl(), true );
	}
	$profile = $db->get_saas_profile_by_id( $session['profile_id'] );
	if ( ! $profile ) {
		return null;
	}
	$result = array(
		'user_id'    => $session['user_id'],
		'profile_id' => $session['profile_id'],
		'profile'    => $profile,
	);
	$result['is_premium'] = ! empty( $profile['is_premium'] );
	return $result;
}

/**
 * Enregistre le script bridge des 21 niveaux (pour le thème premium-dashboard).
 * La config est exposée via avision_enqueue_levels_config() et le filtre avision_levels_config.
 */
function avision_register_levels_config_script() {
    if (!function_exists('wp_register_script')) {
        return;
    }
    wp_register_script(
        'avision-levels-config',
        plugin_dir_url(__FILE__) . 'assets/js/levels-config-bridge.js',
        array(),
        defined('AVISION_VERSION') ? AVISION_VERSION : '9.0.3',
        true
    );
}

/**
 * Enqueue + localize la config des 21 niveaux pour le front (premium-dashboard).
 * À appeler depuis le thème avant d’enqueuer premium-dashboard-js.
 */
function avision_enqueue_levels_config() {
    if (!class_exists('Avision\Avision_Levels_Config') || !function_exists('wp_enqueue_script')) {
        return;
    }
    $config = apply_filters('avision_levels_config', \Avision\Avision_Levels_Config::get_js_config());
    wp_enqueue_script('avision-levels-config');
    wp_localize_script('avision-levels-config', 'avisionLevelsConfig', $config);
}

/**
 * Charge une ligne profil par UUID (wor1515 + table préfixée).
 *
 * @param string $uuid UUID normalisé (minuscules).
 * @return array<string,mixed>|null ARRAY_A ou null.
 */
function avision_premium_dashboard_get_saas_profile_row_by_uuid( $uuid ) {
	$uuid = is_string( $uuid ) ? strtolower( trim( $uuid ) ) : '';
	if ( $uuid === '' || ! preg_match( '/^[a-f0-9\-]{32,36}$/i', $uuid ) ) {
		return null;
	}
	global $wpdb;
	if ( ! isset( $wpdb ) || ! $wpdb ) {
		return null;
	}
	$tables = array();
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', 'wor1515_avi_saas_profiles' ) ) === 'wor1515_avi_saas_profiles' ) {
		$tables[] = 'wor1515_avi_saas_profiles';
	}
	$pt = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pt ) ) === $pt ) {
		$tables[] = $pt;
	}
	$tables = array_values( array_unique( $tables ) );
	foreach ( $tables as $tbl ) {
		if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$tbl}` LIKE %s", 'uuid' ) ) !== 'uuid' ) {
			continue;
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$tbl}` WHERE uuid = %s LIMIT 1", $uuid ), ARRAY_A );
		if ( is_array( $row ) && ! empty( $row['id'] ) ) {
			return $row;
		}
	}
	return null;
}

/**
 * Extrait google_email / email d’une ligne profil pour le JS (pill GBP).
 *
 * @param array<string,mixed> $row
 * @return string
 */
function avision_premium_dashboard_email_from_profile_row( array $row ) {
	if ( ! function_exists( 'is_email' ) ) {
		return '';
	}
	$g = isset( $row['google_email'] ) ? trim( (string) $row['google_email'] ) : '';
	if ( $g !== '' && is_email( $g ) ) {
		return sanitize_email( $g );
	}
	$e = isset( $row['email'] ) ? trim( (string) $row['email'] ) : '';
	if ( $e !== '' && is_email( $e ) ) {
		return sanitize_email( $e );
	}
	if ( $g !== '' && function_exists( 'sanitize_text_field' ) ) {
		return sanitize_text_field( $g );
	}
	if ( $e !== '' && function_exists( 'sanitize_text_field' ) ) {
		return sanitize_text_field( $e );
	}
	return '';
}

/**
 * Email cockpit / pill (JS) : priorité google_email puis colonne email sur avi_saas_profiles — pas l’email du compte WordPress.
 * @param int $profile_id avi_saas_profiles.id
 * @return string Chaîne vide ou email sanitizé
 */
function avision_premium_dashboard_profile_email_for_js( $profile_id ) {
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 || ! function_exists( 'is_email' ) ) {
		return '';
	}
	global $wpdb;
	if ( ! isset( $wpdb ) || ! $wpdb ) {
		return '';
	}
	$table = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return '';
	}
	$row = $wpdb->get_row( $wpdb->prepare( "SELECT google_email, email FROM `{$table}` WHERE id = %d LIMIT 1", $profile_id ), ARRAY_A );
	if ( ! is_array( $row ) ) {
		return '';
	}
	return avision_premium_dashboard_email_from_profile_row( $row );
}

/**
 * Calcule le Score de Visibilité (30–42) et l'estimation Net Worth à partir des données profil + snapshot.
 * Base 30 + 3 (site) + 3 (téléphone) + 3 (≥10 avis) + 3 (catégorie précise). Plus de bonus horaires.
 *
 * @param array $profile_data Clés : has_website (bool), has_phone (bool), category_precise (bool),
 *                            review_count (int), rating (float), position_rank (int|null).
 * @return array ['score' => int 30–42, 'net_worth' => float]
 */
function avi_calculate_visibility_score( $profile_data ) {
	$base = 30;
	$score = $base;
	if ( ! empty( $profile_data['has_website'] ) ) {
		$score += 3;
	}
	if ( ! empty( $profile_data['has_phone'] ) ) {
		$score += 3;
	}
	$review_count = isset( $profile_data['review_count'] ) ? (int) $profile_data['review_count'] : 0;
	if ( $review_count >= 10 ) {
		$score += 3;
	}
	if ( ! empty( $profile_data['category_precise'] ) ) {
		$score += 3;
	}
	$score = min( 42, max( 30, $score ) );

	$rating       = isset( $profile_data['rating'] ) ? (float) $profile_data['rating'] : 0.0;
	$position     = isset( $profile_data['position_rank'] ) ? $profile_data['position_rank'] : null;
	if ( $position === 'Hors Radar' || $position === '' ) {
		$position = null;
	}
	$position = is_numeric( $position ) ? (int) $position : null;

	// Facteur de visibilité selon la position (1–3 = 1.0, 4–10 = 0.7, >10 ou Hors Radar = 0.2)
	if ( $position === null || $position > 10 ) {
		$visibility_factor = 0.2;
	} elseif ( $position <= 3 ) {
		$visibility_factor = 1.0;
	} else {
		$visibility_factor = 0.7;
	}

	$raw_value = ( $review_count * $rating ) * $visibility_factor;
	$net_worth = max( 50.0, round( $raw_value, 2 ) );

	return array(
		'score'     => $score,
		'net_worth' => $net_worth,
	);
}

/**
 * Lance le calcul Score + 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 avi_run_initial_sync().
 *
 * @param int $profile_id ID du profil (avi_saas_profiles.id).
 * @return array|false ['score' => int, 'net_worth' => float, 'saved' => true] ou false.
 */
function avi_run_visibility_calculation( $profile_id ) {
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 ) {
		return false;
	}
	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return false;
	}
	$db = new \Avision\Avision_Database();
	$db->init();
	return $db->run_visibility_calculation_and_save( $profile_id );
}

/**
 * Lance la synchronisation initiale (First Sync) pour un profil : Google + DataForSEO Secteur,
 * enregistrement des 3 leaders et mise à jour de last_metrics_snapshot.
 * À appeler lorsque is_first_sync = 1 (ex. après connexion OAuth ou depuis le backend).
 *
 * @param int $profile_id ID du profil (avi_saas_profiles.id).
 * @return array|false ['success' => true, 'position' => int|string, 'leaders' => array, 'google' => array] ou false.
 */
function avi_run_initial_sync( $profile_id ) {
	$profile_id = (int) $profile_id;
	if ( $profile_id <= 0 ) {
		return false;
	}
	if ( ! class_exists( 'Avision\Avision_Database' ) ) {
		return false;
	}
	$db = new \Avision\Avision_Database();
	$db->init();
	return $db->run_initial_sync( $profile_id );
}

// Administrateur WordPress : accès premium et Mode Gardien sans transaction (pour tester "Connecter ma fiche")
add_filter( 'avision_has_guardian_mode', function( $default ) {
	if ( function_exists( 'current_user_can' ) && current_user_can( 'administrator' ) ) {
		return true;
	}
	return $default;
} );
add_filter( 'avision_has_keyword_top3_premium', function( $default ) {
	if ( function_exists( 'current_user_can' ) && current_user_can( 'administrator' ) ) {
		return true;
	}
	return $default;
} );

// Initialize in WordPress or standalone
if (function_exists('add_action')) {
    add_action('plugins_loaded', 'avision_init');
    add_action('rest_api_init', 'avision_register_rest_routes');
    add_action('wp_enqueue_scripts', 'avision_register_levels_config_script', 5);

    // Tables "Cœur d'AVI" : création à l'activation du plugin
    register_activation_hook( __FILE__, 'avi_install_core_tables' );

    // CRON Task Manager : dé-planification propre à la désactivation
    register_deactivation_hook( __FILE__, [ 'Avision\Cron_Task_Manager', 'unschedule' ] );
} else {
    avision_init();
}
