<?php
/**
 * Webhook Stripe — Écoute checkout.session.completed.
 * Récupère les metadata, crée utilisateur/profil, injecte préférences, lie les add-ons au PID.
 *
 * @package Avision
 */

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

/**
 * Classe Webhook Stripe AVI.
 */
class Avision_Stripe_Webhook {

	const ROUTE = 'avision/v1/stripe-webhook';

	/**
	 * Enregistre la route REST (POST, sans authentification, body brut pour signature).
	 */
	public static function register() {
		add_action( 'rest_api_init', [ __CLASS__, 'register_route' ] );
	}

	/**
	 * Enregistre la route.
	 */
	public static function register_route() {
		register_rest_route( 'avision/v1', '/stripe-webhook', [
			'methods'             => 'POST',
			'callback'            => [ __CLASS__, 'handle' ],
			'permission_callback' => '__return_true',
			'args'                => [],
		] );
	}

	/**
	 * Réponse 200 « received » en court-circuitant la pile REST (évite 500 post-filtres / middleware).
	 *
	 * @return void
	 */
	private static function respond_ok_and_exit() {
		if ( function_exists( 'status_header' ) ) {
			status_header( 200 );
		} else {
			header( 'HTTP/1.1 200 OK', true, 200 );
		}
		header( 'Content-Type: application/json; charset=UTF-8' );
		while ( function_exists( 'ob_get_level' ) && ob_get_level() > 0 ) {
			if ( function_exists( 'ob_end_clean' ) ) {
				ob_end_clean();
			} else {
				break;
			}
		}
		echo function_exists( 'wp_json_encode' )
			? wp_json_encode( [ 'received' => true ] )
			: json_encode( [ 'received' => true ] );
		exit;
	}

	/**
	 * Traite le webhook Stripe (body brut requis pour vérification signature).
	 *
	 * Succès (200) : sortie JSON directe + exit (court-circuit). Erreurs client (4xx/503) : {@see \WP_REST_Response}.
	 *
	 * @param \WP_REST_Request $request Requête REST (corps brut via php://input / repli WP).
	 * @return \WP_REST_Response|void   void si réponse 200 envoyée puis exit.
	 */
	public static function handle( \WP_REST_Request $request ) {
		$ob_base = function_exists( 'ob_get_level' ) ? (int) ob_get_level() : 0;
		if ( function_exists( 'ob_start' ) ) {
			ob_start();
		}
		try {
		if ( function_exists( 'error_log' ) ) {
			error_log(
				'[AVI-STRIPE-ENTRY] Webhook reçu - method: '
				. ( isset( $_SERVER['REQUEST_METHOD'] ) ? (string) $_SERVER['REQUEST_METHOD'] : 'unknown' )
			);
		}

		$secret = '';
		if ( defined( 'AVISION_STRIPE_WEBHOOK_SECRET' ) && is_string( AVISION_STRIPE_WEBHOOK_SECRET ) && AVISION_STRIPE_WEBHOOK_SECRET !== '' ) {
			$secret = AVISION_STRIPE_WEBHOOK_SECRET;
		}
		if ( $secret === '' ) {
			$secret = trim( (string) get_option( 'avi_stripe_webhook_secret', '' ) );
		}
		if ( $secret === '' ) {
			$secret = trim( (string) get_option( 'avision_stripe_webhook_secret', '' ) );
		}
		if ( $secret === '' ) {
			return new \WP_REST_Response( [ 'error' => 'Webhook secret not configured' ], 503 );
		}

		// Corps brut : constante remplie très tôt (plugins_loaded) avant lecture REST ; sinon repli WP.
		if ( defined( 'AVI_STRIPE_RAW_BODY' ) && is_string( AVI_STRIPE_RAW_BODY ) && AVI_STRIPE_RAW_BODY !== '' ) {
			$payload = AVI_STRIPE_RAW_BODY;
		} else {
			$payload = '';
			if ( class_exists( '\WP_REST_Server' ) ) {
				$payload = (string) \WP_REST_Server::get_raw_data();
			}
			if ( $payload === '' ) {
				$payload = (string) $request->get_body();
			}
		}

		if ( $payload === '' ) {
			return new \WP_REST_Response( [ 'error' => 'Missing payload' ], 400 );
		}

		$sig_header = $request->get_header( 'stripe_signature' );
		if ( empty( $sig_header ) ) {
			$sig_header = isset( $_SERVER['HTTP_STRIPE_SIGNATURE'] ) ? (string) wp_unslash( $_SERVER['HTTP_STRIPE_SIGNATURE'] ) : '';
		}
		$sig_header = trim( $sig_header );
		if ( $sig_header === '' ) {
			return new \WP_REST_Response( [ 'error' => 'Missing Stripe-Signature' ], 400 );
		}

		$event = null;
		try {
			$event = self::parse_and_verify( $payload, $sig_header, $secret );
		} catch ( \Throwable $e ) {
			if ( function_exists( 'error_log' ) ) {
				error_log( '[AVI-STRIPE-500] verify: ' . $e->getMessage() . ' ' . $e->getFile() . ':' . $e->getLine() );
			}
			return new \WP_REST_Response( [ 'error' => $e->getMessage() ], 400 );
		}

		$event_type = isset( $event->type ) ? (string) $event->type : '';

		try {
			if ( $event_type === 'checkout.session.completed' ) {
				$session = ( isset( $event->data ) && is_object( $event->data ) && isset( $event->data->object ) && is_object( $event->data->object ) )
					? $event->data->object
					: null;
				if ( $session ) {
					self::handle_checkout_completed( $session );
				}
			} elseif (
				$event_type !== ''
				&& strpos( $event_type, 'customer.subscription.' ) === 0
				&& function_exists( 'avi_handle_avi_subscription_event' )
			) {
				// Inclut customer.subscription.created | updated | deleted (S10-B).
				$sub = ( isset( $event->data ) && is_object( $event->data ) && isset( $event->data->object ) )
					? $event->data->object
					: null;
				$evt_id = isset( $event->id ) ? (string) $event->id : '';
				if ( is_object( $sub ) && $evt_id !== '' ) {
					avi_handle_avi_subscription_event( $event_type, $sub, $evt_id );
				}
			} elseif ( $event_type === 'invoice.payment_failed' && function_exists( 'avi_handle_payment_failed' ) ) {
				$invoice = ( isset( $event->data ) && is_object( $event->data ) && isset( $event->data->object ) && is_object( $event->data->object ) )
					? $event->data->object
					: null;
				if ( $invoice ) {
					$sub_raw = isset( $invoice->subscription ) ? $invoice->subscription : null;
					$sub_id  = '';
					if ( is_object( $sub_raw ) && isset( $sub_raw->id ) ) {
						$sub_id = (string) $sub_raw->id;
					} elseif ( is_string( $sub_raw ) && $sub_raw !== '' ) {
						$sub_id = $sub_raw;
					}
					$attempt_nb = isset( $invoice->attempt_count ) ? (int) $invoice->attempt_count : 1;
					if ( $sub_id !== '' ) {
						if ( function_exists( 'error_log' ) ) {
							error_log(
								'[AVI-STRIPE] Paiement échoué tentative '
								. $attempt_nb . ' subscription=' . $sub_id
							);
						}
						$pf_evt_id = isset( $event->id ) ? (string) $event->id : '';
						avi_handle_payment_failed( $sub_id, $attempt_nb, $pf_evt_id );
					}
				}
			}
		} catch ( \Throwable $e ) {
			if ( function_exists( 'error_log' ) ) {
				error_log(
					'[AVI-STRIPE-FATAL] '
					. get_class( $e ) . ': '
					. $e->getMessage()
					. ' in '
					. $e->getFile() . ':' . $e->getLine()
				);
			}
			// 200 : accusé de réception pour éviter boucle de retry Stripe sur erreurs métier / BDD.
			self::respond_ok_and_exit();
		}

		self::respond_ok_and_exit();
		} finally {
			while ( function_exists( 'ob_get_level' ) && ob_get_level() > $ob_base ) {
				if ( function_exists( 'ob_end_clean' ) ) {
					ob_end_clean();
				} else {
					break;
				}
			}
		}
	}

	/**
	 * Vérifie la signature Stripe (schéma officiel v1 : HMAC-SHA256 du couple timestamp + corps) et décode le JSON.
	 * Sans SDK Stripe PHP — équivalent à Stripe\Webhook::constructEvent sur le principe v1.
	 *
	 * @param string $payload Body brut JSON.
	 * @param string $sig     Header Stripe-Signature (ex. t=…,v1=…).
	 * @param string $secret  Signing secret Stripe complet (ex. whsec_…), clé HMAC telle quelle.
	 * @return object         Objet événement.
	 * @throws \Exception     Signature ou JSON invalide.
	 * @see https://stripe.com/docs/webhooks/signatures
	 */
	private static function parse_and_verify( $payload, $sig_header, $secret ) {
		$timestamp = '';
		$v1_parts  = array();

		foreach ( explode( ',', $sig_header ) as $part ) {
			$part = trim( $part );
			if ( $part === '' ) {
				continue;
			}
			$kv = array_pad( explode( '=', $part, 2 ), 2, '' );
			$k  = trim( $kv[0] );
			$v  = isset( $kv[1] ) ? $kv[1] : '';
			if ( $k === 't' ) {
				$timestamp = $v;
			}
			if ( $k === 'v1' ) {
				$v1_parts[] = $v;
			}
		}

		if ( $timestamp === '' || empty( $v1_parts ) ) {
			throw new \Exception( 'Stripe-Signature header malformé' );
		}

		$ts_int = (int) $timestamp;
		if ( $ts_int <= 0 || abs( time() - $ts_int ) > 600 ) {
			throw new \Exception( 'Webhook timestamp trop ancien' );
		}

		$signed_payload = $timestamp . '.' . $payload;
		$expected       = hash_hmac( 'sha256', $signed_payload, $secret );

		$valid = false;
		foreach ( $v1_parts as $v1 ) {
			if ( $v1 !== '' && hash_equals( $expected, $v1 ) ) {
				$valid = true;
				break;
			}
		}

		if ( ! $valid ) {
			throw new \Exception( 'Invalid signature (v1 HMAC mismatch)' );
		}

		$event = json_decode( $payload );
		if ( JSON_ERROR_NONE !== json_last_error() ) {
			throw new \Exception( 'JSON invalide : ' . json_last_error_msg() );
		}
		if ( ! is_object( $event ) || ! isset( $event->type ) ) {
			throw new \Exception( 'Invalid JSON or missing type' );
		}

		return $event;
	}

	/**
	 * Crée agency + clé embed pour achat LocalEdge Agency hébergé (source=localedge_hosted + account_type=agency).
	 * Flow Profil D : agence sans WordPress propre, tout hébergé sur avision.org.
	 *
	 * @param object $session Stripe Session.
	 */
	private static function handle_localedge_hosted_agency_purchase( $session ) {
		global $wpdb;

		$meta       = isset( $session->metadata ) && is_object( $session->metadata )
		              ? $session->metadata : new \stdClass();
		$email      = isset( $session->customer_details->email )
		              ? sanitize_email( (string) $session->customer_details->email )
		              : ( isset( $meta->email ) ? sanitize_email( (string) $meta->email ) : '' );
		$lang       = isset( $meta->lang )
		              ? sanitize_text_field( (string) $meta->lang ) : 'en';
		// Remap pt-br → pt pour les emails (templates non dupliques)
		if ( $lang === 'pt-br' ) {
			$lang = 'pt';
		}
		$tier       = max( 1, min( 3, absint( isset( $meta->tier ) ? $meta->tier : 1 ) ) );
		$session_id = isset( $session->id )
		              ? sanitize_text_field( (string) $session->id ) : '';

		if ( ! $email ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — email manquant session ' . $session_id );
			return;
		}

		/* Lock idempotence */
		$lock_key = 'le_agency_hosted_' . md5( $session_id );
		if ( get_transient( $lock_key ) ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — déjà traité ' . $session_id );
			return;
		}
		set_transient( $lock_key, 1, 3600 );

		$t_users    = $wpdb->prefix . 'avi_saas_users';
		$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
		$t_agencies = $wpdb->prefix . 'avi_saas_agencies';
		$t_keys     = $wpdb->prefix . 'avi_embed_keys';

		/* Tier → max_listings + radar_plan */
		$tier_max     = array( 1 => 10, 2 => 50, 3 => 1000 );
		$max_listings = $tier_max[ $tier ];
		$radar_plan   = 'agency_tier' . $tier;

		/* 1. SaaS User */
		$existing_uid = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM `{$t_users}` WHERE email = %s LIMIT 1", $email
		) );
		if ( $existing_uid ) {
			$saas_user_id = (int) $existing_uid;
		} else {
			$wpdb->insert( $t_users, array( 'email' => $email ), array( '%s' ) );
			$saas_user_id = (int) $wpdb->insert_id;
		}
		if ( ! $saas_user_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — échec user ' . $email );
			delete_transient( $lock_key );
			return;
		}

		/* 2. WP User — admin_user_id doit être le WP user ID */
		$wp_user_id  = 0;
		$wp_password = '';
		if ( ! email_exists( $email ) ) {
			$wp_password = wp_generate_password( 12, false );
			$wp_user_id  = wp_create_user( $email, $wp_password, $email );
			if ( ! is_wp_error( $wp_user_id ) ) {
				$wp_user = new \WP_User( $wp_user_id );
				$wp_user->set_role( 'localedge_agency' );
			} else {
				error_log( '[AVI WEBHOOK] localedge_hosted_agency — wp_create_user: '
					. $wp_user_id->get_error_message() );
				$wp_user_id = 0;
			}
		} else {
			$existing_wp = get_user_by( 'email', $email );
			if ( $existing_wp ) {
				$wp_user_id = $existing_wp->ID;
				$existing_wp->set_role( 'localedge_agency' );
			}
		}
		if ( ! $wp_user_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — wp_user_id=0, abandon ' . $email );
			delete_transient( $lock_key );
			return;
		}

		/* 3. Profil admin agence */
		$existing_pid = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM `{$t_profiles}` WHERE user_id = %d LIMIT 1", $saas_user_id
		) );
		if ( $existing_pid ) {
			$profile_id = (int) $existing_pid;
		} else {
			$ins_profile = array(
				'user_id'       => $saas_user_id,
				'business_name' => 'LocalEdge Agency',
				'city'          => '-',
				'category'      => 'Agency',
				'current_level' => 1,
				'score'         => 0,
				'country'       => 'US',
			);
			$fmt_profile = array( '%d', '%s', '%s', '%s', '%d', '%d', '%s' );
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'lang'
			) ) === 'lang' ) {
				$ins_profile['lang'] = $lang;
				$fmt_profile[]       = '%s';
			}
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'radar_plan'
			) ) === 'radar_plan' ) {
				$ins_profile['radar_plan'] = $radar_plan;
				$fmt_profile[]             = '%s';
			}
			$wpdb->insert( $t_profiles, $ins_profile, $fmt_profile );
			$profile_id = (int) $wpdb->insert_id;
		}
		if ( ! $profile_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — échec profil ' . $saas_user_id );
			delete_transient( $lock_key );
			return;
		}

		/* 4. Agency — admin_user_id = $wp_user_id (WP user ID, pas SaaS) */
		$existing_agency_id = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM `{$t_agencies}` WHERE admin_user_id = %d LIMIT 1", $wp_user_id
		) );
		if ( $existing_agency_id ) {
			$agency_id = (int) $existing_agency_id;
		} else {
			$ins_agency = array(
				'uuid'          => wp_generate_uuid4(),
				'name'          => 'LocalEdge Agency',
				'admin_user_id' => $wp_user_id,
				'tier'          => $tier,
			);
			$fmt_agency = array( '%s', '%s', '%d', '%d' );
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_agencies}` LIKE %s", 'max_listings'
			) ) === 'max_listings' ) {
				$ins_agency['max_listings'] = $max_listings;
				$fmt_agency[]               = '%d';
			}
			$wpdb->insert( $t_agencies, $ins_agency, $fmt_agency );
			$agency_id = (int) $wpdb->insert_id;
		}
		if ( ! $agency_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted_agency — échec agency ' . $wp_user_id );
			delete_transient( $lock_key );
			return;
		}

		// 4b. Creer/mettre a jour avi_localedge_agency_meta (tier + quota LocalEdge)
		$t_le_meta       = $wpdb->prefix . 'avi_localedge_agency_meta';
		$existing_meta   = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$t_le_meta}` WHERE agency_id = %d LIMIT 1",
				$agency_id
			)
		);
		if ( $existing_meta ) {
			// Agence existante — mettre a jour tier + max_listings
			$wpdb->update(
				$t_le_meta,
				array(
					'tier'              => $tier,
					'max_listings'      => $max_listings,
					'stripe_session_id' => $session->id,
				),
				array( 'agency_id' => $agency_id ),
				array( '%d', '%d', '%s' ),
				array( '%d' )
			);
		} else {
			// Nouvelle agence — INSERT
			$wpdb->insert(
				$t_le_meta,
				array(
					'agency_id'         => $agency_id,
					'tier'              => $tier,
					'max_listings'      => $max_listings,
					'stripe_session_id' => $session->id,
				),
				array( '%d', '%d', '%d', '%s' )
			);
		}
		// Mettre a jour agency_stack sur avi_saas_agencies
		$wpdb->update(
			$wpdb->prefix . 'avi_saas_agencies',
			array( 'agency_stack' => 'localedge' ),
			array( 'id' => $agency_id ),
			array( '%s' ),
			array( '%d' )
		);
		error_log( '[AVI] localedge_agency_meta synced agency_id=' . $agency_id . ' tier=' . $tier . ' max=' . $max_listings );

		/* 5. Clé embed agency — dédup (agency_id, key_type='agency') */
		$existing_key = $wpdb->get_var( $wpdb->prepare(
			"SELECT key_id FROM `{$t_keys}`
			 WHERE agency_id = %d AND key_type = 'agency' AND status = 'active' LIMIT 1",
			$agency_id
		) );
		if ( $existing_key ) {
			$key_id = $existing_key;
		} else {
			$raw_secret  = bin2hex( random_bytes( 32 ) );
			$secret_hash = hash( 'sha256', $raw_secret );
			$key_id      = 'avi_agency_' . bin2hex( random_bytes( 14 ) );
			$ins_key     = array(
				'key_id'        => $key_id,
				'secret_hash'   => $secret_hash,
				'key_type'      => 'agency',
				'agency_id'     => $agency_id,
				'status'        => 'active',
				'guardian_plan' => 'free',
			);
			$fmt_key = array( '%s', '%s', '%s', '%d', '%s', '%s' );
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_keys}` LIKE %s", 'lang'
			) ) === 'lang' ) {
				$ins_key['lang'] = $lang;
				$fmt_key[]       = '%s';
			}
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_keys}` LIKE %s", 'hosted'
			) ) === 'hosted' ) {
				$ins_key['hosted'] = 1;
				$fmt_key[]         = '%d';
			}
			$wpdb->insert( $t_keys, $ins_key, $fmt_key );
			if ( ! $wpdb->insert_id ) {
				error_log( '[AVI WEBHOOK] localedge_hosted_agency — échec clé agency ' . $agency_id );
				delete_transient( $lock_key );
				return;
			}
		}

		/* 6. WP user metas */
		update_user_meta( $wp_user_id, 'avi_saas_user_id', $saas_user_id );
		update_user_meta( $wp_user_id, 'avi_agency_id', $agency_id );
		update_user_meta( $wp_user_id, 'avi_profile_id', $profile_id );
		update_user_meta( $wp_user_id, 'avi_embed_key', $key_id );
		update_user_meta( $wp_user_id, 'avi_localedge_lang', $lang );
		update_user_meta( $wp_user_id, 'avi_account_type', 'agency' );
		update_user_meta( $wp_user_id, 'avi_agency_tier', $tier );

		/* 7. Email onboarding multilingue + profile_id dans connect_url */
		$connect_url = add_query_arg(
			array(
				'hosted'     => '1',
				'key'        => $key_id,
				'profile_id' => $profile_id,
				'lang'       => $lang,
			),
			home_url( '/localedge-connect/' )
		);
		$subjects = array(
			'fr' => 'LocalEdge Agency — Connectez votre première fiche Google Business',
			'es' => 'LocalEdge Agency — Conecta tu primera ficha Google Business',
			'de' => 'LocalEdge Agency — Verbinden Sie Ihr erstes Google Business Profil',
			'pt' => 'LocalEdge Agency — Conecte seu primeiro perfil Google Business',
			'tr' => 'LocalEdge Agency — İlk Google Business Profilinizi Bağlayın',
			'en' => 'LocalEdge Agency — Connect your first Google Business Profile',
		);
		$bodies = array(
			'fr' => 'Bonjour,' . "\n\n"
				. 'Votre accès LocalEdge Agency (Tier ' . $tier . ', ' . $max_listings . ' fiches max) est prêt.' . "\n\n"
				. 'Cliquez sur le lien pour connecter votre première fiche GBP :' . "\n\n"
				. $connect_url . "\n\n"
				. '— L\'équipe Avision',
			'es' => 'Hola,' . "\n\n"
				. 'Su acceso LocalEdge Agency (Tier ' . $tier . ', máximo ' . $max_listings . ' fichas) está listo.' . "\n\n"
				. 'Haga clic en el enlace para conectar su primera ficha GBP:' . "\n\n"
				. $connect_url . "\n\n"
				. '— El equipo de Avision',
			'de' => 'Hallo,' . "\n\n"
				. 'Ihr LocalEdge Agency Zugang (Tier ' . $tier . ', bis zu ' . $max_listings . ' Profile) ist bereit.' . "\n\n"
				. 'Klicken Sie auf den Link, um Ihr erstes Google Business Profil zu verbinden:' . "\n\n"
				. $connect_url . "\n\n"
				. '— Das Avision-Team',
			'pt' => 'Olá,' . "\n\n"
				. 'Seu acesso LocalEdge Agency (Tier ' . $tier . ', até ' . $max_listings . ' perfis) está pronto.' . "\n\n"
				. 'Clique no link para conectar seu primeiro perfil Google Business:' . "\n\n"
				. $connect_url . "\n\n"
				. '— A equipe Avision',
			'tr' => 'Merhaba,' . "\n\n"
				. 'LocalEdge Agency erişiminiz (Tier ' . $tier . ', maksimum ' . $max_listings . ' profil) hazır.' . "\n\n"
				. 'İlk Google Business Profilinizi bağlamak için aşağıdaki bağlantıya tıklayın:' . "\n\n"
				. $connect_url . "\n\n"
				. '— Avision Ekibi',
			'en' => 'Hello,' . "\n\n"
				. 'Your LocalEdge Agency access (Tier ' . $tier . ', up to ' . $max_listings . ' listings) is ready.' . "\n\n"
				. 'Click the link below to connect your first Google Business Profile:' . "\n\n"
				. $connect_url . "\n\n"
				. '— The Avision Team',
		);
		if ( ! empty( $wp_password ) ) {
			$login_lines = array(
				'en'    => "\n\n--- Your login details ---\nEmail: " . $email . "\nPassword: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'fr'    => "\n\n--- Vos identifiants de connexion ---\nEmail : " . $email . "\nMot de passe : " . $wp_password . "\nConnexion : " . home_url( '/localedge-login/' ),
				'es'    => "\n\n--- Sus datos de acceso ---\nEmail: " . $email . "\nContrase\u{f1}a: " . $wp_password . "\nAcceso: " . home_url( '/localedge-login/' ),
				'pt'    => "\n\n--- Seus dados de acesso ---\nEmail: " . $email . "\nSenha: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'pt-br' => "\n\n--- Seus dados de acesso ---\nEmail: " . $email . "\nSenha: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'tr'    => "\n\n--- Giri\u{15f} bilgileriniz ---\nE-posta: " . $email . "\n\u{15e}ifre: " . $wp_password . "\nGiri\u{15f}: " . home_url( '/localedge-login/' ),
				'de'    => "\n\n--- Ihre Zugangsdaten ---\nE-Mail: " . $email . "\nPasswort: " . $wp_password . "\nAnmeldung: " . home_url( '/localedge-login/' ),
			);
			$login_lang = isset( $login_lines[ $lang ] ) ? $lang : 'en';
			$body_key   = isset( $bodies[ $lang ] ) ? $lang : 'en';
			$bodies[ $body_key ] .= $login_lines[ $login_lang ];
		} else {
			// User existant — ajouter lien reset password
			$reset_lines = array(
				'en'    => "\n\nYou already have an account. Use your existing password or reset it here: " . home_url( '/localedge-login/' ),
				'fr'    => "\n\nVous avez d\u{e9}j\u{e0} un compte. Utilisez votre mot de passe existant ou r\u{e9}initialisez-le ici : " . home_url( '/localedge-login/' ),
				'es'    => "\n\nYa tiene una cuenta. Use su contrase\u{f1}a existente o recu\u{e9}pere la aqu\u{ed}: " . home_url( '/localedge-login/' ),
				'pt'    => "\n\nVoc\u{ea} j\u{e1} tem uma conta. Use sua senha existente ou redefina aqui: " . home_url( '/localedge-login/' ),
				'pt-br' => "\n\nVoc\u{ea} j\u{e1} tem uma conta. Use sua senha existente ou redefina aqui: " . home_url( '/localedge-login/' ),
				'tr'    => "\n\nZaten bir hesab\u{131}n\u{131}z var. Mevcut \u{15f}ifrenizi kullan\u{131}n veya buradan s\u{131}f\u{131}rlay\u{131}n: " . home_url( '/localedge-login/' ),
				'de'    => "\n\nSie haben bereits ein Konto. Verwenden Sie Ihr bestehendes Passwort oder setzen Sie es hier zur\u{fc}ck: " . home_url( '/localedge-login/' ),
			);
			$reset_lang = isset( $reset_lines[ $lang ] ) ? $lang : 'en';
			$body_key   = isset( $bodies[ $lang ] ) ? $lang : 'en';
			$bodies[ $body_key ] .= $reset_lines[ $reset_lang ];
		}
		$subject = isset( $subjects[ $lang ] ) ? $subjects[ $lang ] : $subjects['en'];
		$body    = isset( $bodies[ $lang ] ) ? $bodies[ $lang ] : $bodies['en'];
		wp_mail( $email, $subject, $body, array( 'Content-Type: text/plain; charset=UTF-8' ) );

		error_log( '[AVI WEBHOOK] localedge_hosted_agency — OK wp_user=' . $wp_user_id
			. ' agency=' . $agency_id . ' key=' . $key_id
			. ' tier=' . $tier . ' email=' . $email );
	}

	/**
	 * Crée user + profil + clé embed pour achat LocalEdge hébergé (source=localedge_hosted).
	 * Flow Profil B : solo sans WordPress propre, tout hébergé sur avision.org.
	 *
	 * @param object $session Stripe Session.
	 */
	private static function handle_localedge_hosted_purchase( $session ) {
		global $wpdb;

		$meta       = isset( $session->metadata ) && is_object( $session->metadata )
		              ? $session->metadata : new \stdClass();
		$email      = isset( $session->customer_details->email )
		              ? sanitize_email( (string) $session->customer_details->email )
		              : ( isset( $meta->email ) ? sanitize_email( (string) $meta->email ) : '' );
		$lang       = isset( $meta->lang )
		              ? sanitize_text_field( (string) $meta->lang ) : 'en';
		// Remap pt-br → pt pour les emails (templates non dupliques)
		if ( $lang === 'pt-br' ) {
			$lang = 'pt';
		}
		$session_id = isset( $session->id )
		              ? sanitize_text_field( (string) $session->id ) : '';

		if ( ! $email ) {
			error_log( '[AVI WEBHOOK] localedge_hosted — email manquant session ' . $session_id );
			return;
		}

		/* Lock idempotence — évite double traitement sur retry Stripe */
		$lock_key = 'le_hosted_' . md5( $session_id );
		if ( get_transient( $lock_key ) ) {
			error_log( '[AVI WEBHOOK] localedge_hosted — déjà traité ' . $session_id );
			return;
		}
		set_transient( $lock_key, 1, 3600 );

		$t_users    = $wpdb->prefix . 'avi_saas_users';
		$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
		$t_keys     = $wpdb->prefix . 'avi_embed_keys';

		/* 1. User */
		$existing_uid = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM `{$t_users}` WHERE email = %s LIMIT 1", $email
		) );
		if ( $existing_uid ) {
			$saas_user_id = (int) $existing_uid;
		} else {
			$wpdb->insert( $t_users, array( 'email' => $email ), array( '%s' ) );
			$saas_user_id = (int) $wpdb->insert_id;
		}
		if ( ! $saas_user_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted — échec user ' . $email );
			delete_transient( $lock_key );
			return;
		}

		/* 2. Profil */
		$existing_pid = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM `{$t_profiles}` WHERE user_id = %d LIMIT 1", $saas_user_id
		) );
		if ( $existing_pid ) {
			$profile_id = (int) $existing_pid;
		} else {
			$ins_profile = array(
				'user_id'       => $saas_user_id,
				'business_name' => 'LocalEdge',
				'city'          => '-',
				'category'      => 'Client',
				'current_level' => 1,
				'score'         => 0,
				'country'       => 'US',
			);
			$fmt_profile = array( '%d', '%s', '%s', '%s', '%d', '%d', '%s' );
			/* Guard lang */
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'lang'
			) ) === 'lang' ) {
				$ins_profile['lang'] = $lang;
				$fmt_profile[]       = '%s';
			}
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'language'
			) ) === 'language' ) {
				$ins_profile['language'] = $lang;
				$fmt_profile[]           = '%s';
			}
			$wpdb->insert( $t_profiles, $ins_profile, $fmt_profile );
			$profile_id = (int) $wpdb->insert_id;
		}
		if ( ! $profile_id ) {
			error_log( '[AVI WEBHOOK] localedge_hosted — échec profil user ' . $saas_user_id );
			delete_transient( $lock_key );
			return;
		}

		/* 3. Clé embed */
		$existing_key = $wpdb->get_var( $wpdb->prepare(
			"SELECT key_id FROM `{$t_keys}`
			 WHERE profile_id = %d AND key_type = 'solo' LIMIT 1",
			$profile_id
		) );
		if ( $existing_key ) {
			$key_id = $existing_key;
		} else {
			$raw_secret  = bin2hex( random_bytes( 32 ) );
			$secret_hash = hash( 'sha256', $raw_secret );
			$key_id      = 'avi_' . bin2hex( random_bytes( 14 ) );
			$ins_key     = array(
				'key_id'        => $key_id,
				'secret_hash'   => $secret_hash,
				'key_type'      => 'solo',
				'profile_id'    => $profile_id,
				'status'        => 'active',
				'guardian_plan' => 'free',
			);
			$fmt_key = array( '%s', '%s', '%s', '%d', '%s', '%s' );
			/* Guard lang sur embed_keys */
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_keys}` LIKE %s", 'lang'
			) ) === 'lang' ) {
				$ins_key['lang'] = $lang;
				$fmt_key[]       = '%s';
			}
			/* hosted=1 pour Profil B hébergé Avision */
			if ( $wpdb->get_var( $wpdb->prepare(
				"SHOW COLUMNS FROM `{$t_keys}` LIKE %s", 'hosted'
			) ) === 'hosted' ) {
				$ins_key['hosted'] = 1;
				$fmt_key[]         = '%d';
			}
			$wpdb->insert( $t_keys, $ins_key, $fmt_key );
			if ( ! $wpdb->insert_id ) {
				error_log( '[AVI WEBHOOK] localedge_hosted — échec clé profil ' . $profile_id );
				delete_transient( $lock_key );
				return;
			}
		}

		/* 4. Création WP user localedge_solo */
		$wp_user_id  = 0;
		$wp_password = '';
		if ( ! email_exists( $email ) ) {
			$wp_password = wp_generate_password( 12, false );
			$wp_user_id  = wp_create_user( $email, $wp_password, $email );
			if ( ! is_wp_error( $wp_user_id ) ) {
				$wp_user = new \WP_User( $wp_user_id );
				$wp_user->set_role( 'localedge_solo' );
				update_user_meta( $wp_user_id, 'avi_saas_user_id', $saas_user_id );
				update_user_meta( $wp_user_id, 'avi_profile_id', $profile_id );
				update_user_meta( $wp_user_id, 'avi_embed_key', $key_id );
				update_user_meta( $wp_user_id, 'avi_localedge_lang', $lang );
			} else {
				error_log( '[AVI WEBHOOK] localedge_hosted — wp_create_user error: '
					. $wp_user_id->get_error_message() );
				$wp_user_id = 0;
			}
		} else {
			$existing_wp = get_user_by( 'email', $email );
			if ( $existing_wp ) {
				$wp_user_id = $existing_wp->ID;
				$existing_wp->set_role( 'localedge_solo' );
				update_user_meta( $wp_user_id, 'avi_profile_id', $profile_id );
				update_user_meta( $wp_user_id, 'avi_embed_key', $key_id );
			}
		}

		/* 4. Email onboarding multilingue */
		$connect_url = add_query_arg(
			array(
				'hosted'     => '1',
				'key'        => $key_id,
				'profile_id' => $profile_id,
				'lang'       => $lang,
			),
			home_url( '/localedge-connect/' )
		);
		$subjects = array(
			'fr' => 'LocalEdge — Connectez votre fiche Google Business',
			'es' => 'LocalEdge — Conecta tu ficha Google Business',
			'de' => 'LocalEdge — Google Business Profil verbinden',
			'pt' => 'LocalEdge — Conecte seu perfil Google Business',
			'tr' => 'LocalEdge — Google Business Profilinizi Baglayın',
			'en' => 'LocalEdge — Connect your Google Business Profile',
		);
		$bodies = array(
			'fr' => 'Bonjour,' . "\n\n" . 'Votre accès LocalEdge est prêt. Cliquez sur le lien ci-dessous pour connecter votre fiche Google Business :' . "\n\n" . $connect_url . "\n\n" . '— L\'équipe Avision',
			'es' => 'Hola,' . "\n\n" . 'Su acceso LocalEdge está listo. Haga clic en el enlace para conectar su ficha Google Business:' . "\n\n" . $connect_url . "\n\n" . '— El equipo de Avision',
			'de' => 'Hallo,' . "\n\n" . 'Ihr LocalEdge-Zugang ist bereit. Klicken Sie auf den Link, um Ihr Google Business Profil zu verbinden:' . "\n\n" . $connect_url . "\n\n" . '— Das Avision-Team',
			'pt' => 'Olá,' . "\n\n" . 'Seu acesso LocalEdge está pronto. Clique no link abaixo para conectar seu perfil Google Business:' . "\n\n" . $connect_url . "\n\n" . '— A equipe Avision',
			'tr' => 'Merhaba,' . "\n\n" . 'LocalEdge erisimınız hazır. Google Business Profilinizi baglamak icin asagıdaki bagıantıya tıklayın:' . "\n\n" . $connect_url . "\n\n" . '— Avision Ekibi',
			'en' => 'Hello,' . "\n\n" . 'Your LocalEdge access is ready. Click the link below to connect your Google Business Profile:' . "\n\n" . $connect_url . "\n\n" . '— The Avision Team',
		);
		if ( ! empty( $wp_password ) ) {
			$login_lines = array(
				'en'    => "\n\n--- Your login details ---\nEmail: " . $email . "\nPassword: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'fr'    => "\n\n--- Vos identifiants de connexion ---\nEmail : " . $email . "\nMot de passe : " . $wp_password . "\nConnexion : " . home_url( '/localedge-login/' ),
				'es'    => "\n\n--- Sus datos de acceso ---\nEmail: " . $email . "\nContrase\u{f1}a: " . $wp_password . "\nAcceso: " . home_url( '/localedge-login/' ),
				'pt'    => "\n\n--- Seus dados de acesso ---\nEmail: " . $email . "\nSenha: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'pt-br' => "\n\n--- Seus dados de acesso ---\nEmail: " . $email . "\nSenha: " . $wp_password . "\nLogin: " . home_url( '/localedge-login/' ),
				'tr'    => "\n\n--- Giri\u{15f} bilgileriniz ---\nE-posta: " . $email . "\n\u{15e}ifre: " . $wp_password . "\nGiri\u{15f}: " . home_url( '/localedge-login/' ),
				'de'    => "\n\n--- Ihre Zugangsdaten ---\nE-Mail: " . $email . "\nPasswort: " . $wp_password . "\nAnmeldung: " . home_url( '/localedge-login/' ),
			);
			$login_lang = isset( $login_lines[ $lang ] ) ? $lang : 'en';
			$body_key   = isset( $bodies[ $lang ] ) ? $lang : 'en';
			$bodies[ $body_key ] .= $login_lines[ $login_lang ];
		} else {
			// User existant — ajouter lien reset password
			$reset_lines = array(
				'en'    => "\n\nYou already have an account. Use your existing password or reset it here: " . home_url( '/localedge-login/' ),
				'fr'    => "\n\nVous avez d\u{e9}j\u{e0} un compte. Utilisez votre mot de passe existant ou r\u{e9}initialisez-le ici : " . home_url( '/localedge-login/' ),
				'es'    => "\n\nYa tiene una cuenta. Use su contrase\u{f1}a existente o recu\u{e9}pere la aqu\u{ed}: " . home_url( '/localedge-login/' ),
				'pt'    => "\n\nVoc\u{ea} j\u{e1} tem uma conta. Use sua senha existente ou redefina aqui: " . home_url( '/localedge-login/' ),
				'pt-br' => "\n\nVoc\u{ea} j\u{e1} tem uma conta. Use sua senha existente ou redefina aqui: " . home_url( '/localedge-login/' ),
				'tr'    => "\n\nZaten bir hesab\u{131}n\u{131}z var. Mevcut \u{15f}ifrenizi kullan\u{131}n veya buradan s\u{131}f\u{131}rlay\u{131}n: " . home_url( '/localedge-login/' ),
				'de'    => "\n\nSie haben bereits ein Konto. Verwenden Sie Ihr bestehendes Passwort oder setzen Sie es hier zur\u{fc}ck: " . home_url( '/localedge-login/' ),
			);
			$reset_lang = isset( $reset_lines[ $lang ] ) ? $lang : 'en';
			$body_key   = isset( $bodies[ $lang ] ) ? $lang : 'en';
			$bodies[ $body_key ] .= $reset_lines[ $reset_lang ];
		}
		$subject = isset( $subjects[ $lang ] ) ? $subjects[ $lang ] : $subjects['en'];
		$body    = isset( $bodies[ $lang ] ) ? $bodies[ $lang ] : $bodies['en'];
		wp_mail(
			$email,
			$subject,
			$body,
			array( 'Content-Type: text/plain; charset=UTF-8' )
		);

		error_log( '[AVI WEBHOOK] localedge_hosted — OK user=' . $saas_user_id
			. ' profile=' . $profile_id . ' key=' . $key_id . ' email=' . $email );
	}

	/**
	 * Crédite un achat LocalEdge embed (checkout Stripe metadata source=localedge_embed).
	 *
	 * @param object $session Objet Stripe Session.
	 * @return void
	 */
	private static function handle_localedge_embed_purchase(
		$session
	): void {
		global $wpdb;

		$meta       = isset( $session->metadata )
		              && is_object( $session->metadata )
		              ? $session->metadata
		              : new \stdClass();
		$key_id     = isset( $meta->key_id )
		              ? sanitize_text_field(
		                  (string) $meta->key_id
		              ) : '';
		$profile_id = isset( $meta->profile_id )
		              ? absint( $meta->profile_id )
		              : 0;
		$credits    = isset( $meta->credits )
		              ? absint( $meta->credits )
		              : 0;
		$pack       = isset( $meta->pack )
		              ? sanitize_text_field(
		                  (string) $meta->pack
		              ) : '';
		$type       = isset( $meta->type )
		              ? sanitize_text_field( (string) $meta->type )
		              : 'solo';
		$session_id = isset( $session->id )
		              ? sanitize_text_field(
		                  (string) $session->id
		              ) : '';

		if ( ! $key_id || ! $credits || ! $session_id ) {
			return;
		}

		$lock_key = 'le_stripe_' . md5( $session_id );
		if ( get_option( $lock_key ) ) {
			return;
		}
		update_option( $lock_key, time(), false );

		if ( $type === 'agency' ) {
			$key_row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT agency_id FROM
					 `{$wpdb->prefix}avi_embed_keys`
					 WHERE key_id = %s
					   AND status = 'active'
					 LIMIT 1",
					$key_id
				)
			);

			if ( ! $key_row || ! $key_row->agency_id ) {
				return;
			}

			$agency_id = (int) $key_row->agency_id;

			$existing_hm = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id FROM
					 `{$wpdb->prefix}avi_heatmap_credits`
					 WHERE owner_type = 'agency'
					   AND agency_id = %d
					 LIMIT 1",
					$agency_id
				)
			);

			if ( $existing_hm ) {
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE `{$wpdb->prefix}avi_heatmap_credits`
						 SET credits_total = credits_total + %d,
						     updated_at = NOW()
						 WHERE owner_type = 'agency'
						   AND agency_id = %d",
						$credits,
						$agency_id
					)
				);
			} else {
				$wpdb->insert(
					$wpdb->prefix . 'avi_heatmap_credits',
					array(
						'owner_type'    => 'agency',
						'agency_id'     => $agency_id,
						'profile_id'    => $profile_id,
						'credits_total' => $credits,
						'credits_used'  => 0,
					),
					array( '%s', '%d', '%d', '%d', '%d' )
				);
			}
		} else {
			$existing = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id FROM
					 `{$wpdb->prefix}avi_embed_credits`
					 WHERE key_id = %s
					 LIMIT 1",
					$key_id
				)
			);

			if ( $existing ) {
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE `{$wpdb->prefix}avi_embed_credits`
						 SET credits_total = credits_total + %d,
						     pack_type = %s,
						     updated_at = NOW()
						 WHERE key_id = %s",
						$credits,
						$pack,
						$key_id
					)
				);
			} else {
				$wpdb->insert(
					$wpdb->prefix . 'avi_embed_credits',
					array(
						'key_id'        => $key_id,
						'profile_id'    => $profile_id,
						'credits_total' => $credits,
						'credits_used'  => 0,
						'pack_type'     => $pack,
						'expires_at'    => null,
					),
					array(
						'%s', '%d', '%d', '%d', '%s', '%s'
					)
				);
			}
		}
	}

	/**
	 * Traite checkout.session.completed : metadata → BDD, préférences, add-ons.
	 *
	 * @param object $session Objet Stripe Session (metadata, customer_email, etc.).
	 */
	private static function handle_checkout_completed( $session ) {
		/* ── LocalEdge Embed credits ── */
		$le_source = isset( $session->metadata->source )
			? (string) $session->metadata->source
			: '';

		if ( $le_source === 'localedge_embed' ) {
			self::handle_localedge_embed_purchase(
				$session
			);
			return;
		}
		if ( $le_source === 'localedge_hosted' ) {
			$le_account_type = isset( $session->metadata->account_type )
				? strtolower( sanitize_text_field( (string) $session->metadata->account_type ) )
				: '';
			if ( $le_account_type === 'agency' ) {
				self::handle_localedge_hosted_agency_purchase( $session );
				return;
			}
			self::handle_localedge_hosted_purchase( $session );
			return;
		}
		if ( $le_source === 'localedge_tier_change' ) {
			global $wpdb;

			$agency_id = isset( $session->metadata->agency_id ) ? intval( $session->metadata->agency_id ) : 0;
			$new_tier  = isset( $session->metadata->new_tier ) ? intval( $session->metadata->new_tier ) : 0;
			$old_tier  = isset( $session->metadata->old_tier ) ? intval( $session->metadata->old_tier ) : 0;

			if ( ! $agency_id || ! in_array( $new_tier, array( 1, 2, 3 ), true ) ) {
				error_log( '[AVI WEBHOOK] localedge_tier_change : parametres invalides agency_id=' . $agency_id . ' new_tier=' . $new_tier );
				return;
			}

			$session_id    = $session->id;
			$transient_key = 'le_tier_change_' . md5( $session_id );
			if ( get_transient( $transient_key ) ) {
				error_log( '[AVI WEBHOOK] localedge_tier_change : session deja traitee ' . $session_id );
				return;
			}
			set_transient( $transient_key, 1, 3600 );

			$tier_max = array( 1 => 10, 2 => 50, 3 => 1000 );
			$new_max  = isset( $tier_max[ $new_tier ] ) ? $tier_max[ $new_tier ] : 10;

			$updated = $wpdb->update(
				$wpdb->prefix . 'avi_localedge_agency_meta',
				array(
					'tier'              => $new_tier,
					'max_listings'      => $new_max,
					'stripe_session_id' => $session_id,
				),
				array( 'agency_id' => $agency_id ),
				array( '%d', '%d', '%s' ),
				array( '%d' )
			);

			if ( $updated === false ) {
				error_log( '[AVI WEBHOOK] localedge_tier_change : echec UPDATE meta agency_id=' . $agency_id );
				return;
			}

			$admin_user_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT admin_user_id FROM `{$wpdb->prefix}avi_saas_agencies` WHERE id = %d LIMIT 1",
					$agency_id
				)
			);
			if ( $admin_user_id ) {
				update_user_meta( $admin_user_id, 'avi_agency_tier', $new_tier );
			}

			error_log( '[AVI WEBHOOK] localedge_tier_change OK : agency_id=' . $agency_id . ' tier ' . $old_tier . '->' . $new_tier . ' max=' . $new_max );
			return;
		}

		$meta = isset( $session->metadata ) && is_object( $session->metadata ) ? $session->metadata : new \stdClass();
		$upgrade_type = isset( $meta->upgrade_type ) ? strtolower( trim( (string) $meta->upgrade_type ) ) : '';
		$upgrade_profile_id = isset( $meta->profile_id ) ? (int) $meta->profile_id : 0;
		if ( $upgrade_type !== '' && $upgrade_profile_id > 0 ) {
			self::apply_in_app_upgrade( $upgrade_profile_id, $upgrade_type );
			return;
		}

		$avi_product = isset( $meta->avi_product ) ? sanitize_key( (string) $meta->avi_product ) : '';
		if ( $avi_product === 'agency_tier_upgrade' ) {
			global $wpdb;
			$target_tier = isset( $meta->target_tier ) ? sanitize_text_field( (string) $meta->target_tier ) : '';
			$from_tier   = isset( $meta->from_tier ) ? sanitize_text_field( (string) $meta->from_tier ) : '';
			$meta_uid    = isset( $meta->user_id ) ? (int) $meta->user_id : 0;

			if ( $target_tier === '' || $meta_uid <= 0 ) {
				error_log( '[AVI WEBHOOK] agency_tier_upgrade — missing metadata' );
				return;
			}

			$tier_caps = array(
				'agency_tier1' => 10,
				'agency_tier2' => 50,
				'agency_tier3' => 1000,
			);
			$new_cap = isset( $tier_caps[ $target_tier ] ) ? (int) $tier_caps[ $target_tier ] : 10;

			$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t_profiles ) ) === $t_profiles ) {
				$wpdb->update(
					$t_profiles,
					array( 'radar_plan' => $target_tier ),
					array( 'user_id' => $meta_uid ),
					array( '%s' ),
					array( '%d' )
				);
			}

			$t_agencies = $wpdb->prefix . 'avi_saas_agencies';
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t_agencies ) ) === $t_agencies
				&& $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_agencies}` LIKE %s", 'max_listings' ) ) === 'max_listings' ) {
				$wpdb->update(
					$t_agencies,
					array( 'max_listings' => $new_cap ),
					array( 'admin_user_id' => $meta_uid ),
					array( '%d' ),
					array( '%d' )
				);
			}

			if ( function_exists( 'error_log' ) ) {
				error_log(
					'[AVI WEBHOOK] agency_tier_upgrade OK — user ' . (int) $meta_uid . ' ' . $from_tier . ' → ' . $target_tier
				);
			}
			return;
		}

		$report_id = isset( $meta->report_id ) ? sanitize_text_field( (string) $meta->report_id ) : '';
		$meta_type = isset( $meta->type ) ? sanitize_text_field( (string) $meta->type ) : '';
		if ( $report_id !== '' && $meta_type === 'b2c_onboarding' && function_exists( 'avision_convert_report_to_profile' ) ) {
			avision_convert_report_to_profile( $report_id );
		}
		$report_meta = array();
		if ( $report_id !== '' && function_exists( 'get_transient' ) ) {
			$report_data = get_transient( 'avision_' . $report_id );
			if ( is_array( $report_data ) && isset( $report_data['meta'] ) && is_array( $report_data['meta'] ) ) {
				$report_meta = $report_data['meta'];
			}
		}
		// ANTI-RÉGRESSION [04 juin 2026] — customer_email null sur coupon 100% → fallback customer_details->email
		$email = ! empty( $session->customer_email )
			? (string) $session->customer_email
			: ( ! empty( $session->customer_details?->email )
				? (string) $session->customer_details->email
				: ( isset( $meta->email ) ? (string) $meta->email : '' )
			);
		if ( empty( $email ) && ! empty( $report_meta['email'] ) ) {
			$email = $report_meta['email'];
		}
		$email = is_email( $email ) ? $email : '';
		if ( empty( $email ) ) {
			return;
		}

		$user_type = isset( $meta->user_type ) ? strtoupper( trim( (string) $meta->user_type ) ) : 'B2C';
		if ( $user_type !== 'B2B' ) {
			$user_type = 'B2C';
		}

		$company_name = isset( $meta->company_name ) ? sanitize_text_field( (string) $meta->company_name ) : '';
		$city         = isset( $meta->city ) ? sanitize_text_field( (string) $meta->city ) : '';
		$country      = isset( $meta->country ) ? sanitize_text_field( (string) $meta->country ) : 'FR';
		$neighborhood = isset( $meta->neighborhood ) ? sanitize_text_field( (string) $meta->neighborhood ) : '';
		$category     = isset( $meta->category ) ? sanitize_text_field( (string) $meta->category ) : '';
		if ( $company_name === '' && ! empty( $report_meta['name'] ) ) {
			$company_name = sanitize_text_field( (string) $report_meta['name'] );
		}
		if ( $city === '' && ! empty( $report_meta['city'] ) ) {
			$city = sanitize_text_field( (string) $report_meta['city'] );
		}
		if ( $country === 'FR' && ! empty( $report_meta['country'] ) ) {
			$country = sanitize_text_field( (string) $report_meta['country'] );
		}
		if ( $neighborhood === '' && ! empty( $report_meta['neighborhood'] ) ) {
			$neighborhood = sanitize_text_field( (string) $report_meta['neighborhood'] );
		}
		if ( $category === '' ) {
			if ( ! empty( $report_meta['category'] ) ) {
				$category = sanitize_text_field( (string) $report_meta['category'] );
			} elseif ( ! empty( $report_meta['cat'] ) ) {
				$category = sanitize_text_field( (string) $report_meta['cat'] );
			}
		}

		$phone = isset( $meta->phone ) ? sanitize_text_field( (string) $meta->phone ) : '';
		if ( $phone === '' && ! empty( $report_meta['phone'] ) ) {
			$phone = sanitize_text_field( (string) $report_meta['phone'] );
		}

		$addon_guardian = isset( $meta->addon_guardian ) && ( $meta->addon_guardian === 'true' || $meta->addon_guardian === '1' );
		$addon_radar    = isset( $meta->addon_radar ) && ( $meta->addon_radar === 'true' || $meta->addon_radar === '1' );
		$user_type_lower = ( $user_type === 'B2B' ) ? 'b2b' : 'b2c';

		// Langue préférée : metadata Stripe (lang) prioritaire, sinon heuristique pays.
		$preferred_lang = 'fr';
		if ( in_array( strtoupper( $country ), [ 'GB', 'US' ], true ) ) {
			$preferred_lang = 'en';
		} elseif ( strtoupper( $country ) === 'ES' ) {
			$preferred_lang = 'es';
		}
		if ( isset( $meta->lang ) ) {
			$ml = strtolower( trim( (string) $meta->lang ) );
			if ( in_array( $ml, [ 'fr', 'en', 'es' ], true ) ) {
				$preferred_lang = $ml;
			}
		}

		global $wpdb;

		// 0) B2C « blindé » : mw_user + wor1515_avi_saas_profiles à partir de la pré-inscription (password_hash dans data).
		$provisioned = null;
		if ( $user_type_lower === 'b2c' && function_exists( 'avision_stripe_provision_b2c_from_pre_inscription' ) ) {
			$provisioned = avision_stripe_provision_b2c_from_pre_inscription( $email, $company_name, $city, $country, $neighborhood, $category, $phone );
		}

		// 1) Créer ou récupérer utilisateur SaaS + profil (si pas déjà provisionné wor1515).
		$profile_id   = null;
		$saas_user_id = null;
		if ( is_array( $provisioned ) && ! empty( $provisioned['profile_id'] ) ) {
			$profile_id = (int) $provisioned['profile_id'];
			if ( ! empty( $provisioned['mw_user_id'] ) ) {
				$saas_user_id = (int) $provisioned['mw_user_id'];
			}
		} elseif ( 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 ) {
				$business_name = $company_name ?: 'Profil ' . $email;
				$phone_san     = $phone !== '' ? sanitize_text_field( $phone ) : '';
				$profile       = $db->create_saas_profile( $saas_user_id, $business_name, $city ?: '-', $category ?: 'Client', null, null, $neighborhood, $country, $phone_san );
				if ( $profile && ! empty( $profile['id'] ) ) {
					$profile_id = (int) $profile['id'];
				}
			}
		}

		// 2) Si pas de PDO/SaaS, tenter création via $wpdb (tables WordPress)
		$t_profiles = $wpdb->prefix . 'avi_saas_profiles';
		$t_users    = $wpdb->prefix . 'avi_saas_users';
		if ( $profile_id === null && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $t_users ) ) ) === $t_users ) {
			$existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$t_users}` WHERE email = %s LIMIT 1", $email ) );
			if ( $existing ) {
				$saas_user_id = (int) $existing;
			} else {
				$wpdb->insert( $t_users, [ 'email' => $email ], [ '%s' ] );
				$saas_user_id = $wpdb->insert_id;
			}
			if ( $saas_user_id && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $t_profiles ) ) ) === $t_profiles ) {
				$ins_profiles = [
					'user_id'               => $saas_user_id,
					'business_name'         => $company_name ?: 'Profil',
					'city'                  => $city ?: '-',
					'neighborhood'          => $neighborhood,
					'category'              => $category ?: 'Client',
					'current_level'         => 1,
					'score'                 => 0,
					'country'               => in_array( strtoupper( $country ), [ 'FR', 'GB', 'US', 'ES' ], true ) ? strtoupper( $country ) : 'FR',
					'promotion_expires_at'  => gmdate( 'Y-m-d H:i:s', strtotime( '+24 hours' ) ),
				];
				$fmt_profiles = [ '%d', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s' ];
				if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'lang' ) ) === 'lang' ) {
					$ins_profiles['lang'] = $preferred_lang;
					$fmt_profiles[]       = '%s';
				}
				if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'language' ) ) === 'language' ) {
					$ins_profiles['language'] = $preferred_lang;
					$fmt_profiles[]           = '%s';
				}
				if ( $phone !== '' && $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'phone' ) ) === 'phone' ) {
					$ins_profiles['phone'] = $phone;
					$fmt_profiles[]        = '%s';
				}
				$wpdb->insert( $t_profiles, $ins_profiles, $fmt_profiles );
				$profile_id = $wpdb->insert_id;
				// Mémoriser le profile_id Stripe pour l'OAuth (si WP user déjà connu, ex. provision B2C).
				if ( $profile_id && isset( $wp_user_id ) && (int) $wp_user_id > 0 ) {
					update_user_meta( (int) $wp_user_id, 'avi_stripe_profile_id', (int) $profile_id );
				}
			}
		}

		// 3) Colonne user_type si absente (MySQL)
		if ( $profile_id && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $t_profiles ) ) ) === $t_profiles ) {
			$schema = defined( 'DB_NAME' ) ? DB_NAME : ( isset( $wpdb->dbname ) ? $wpdb->dbname : '' );
			$col    = $schema ? $wpdb->get_results( $wpdb->prepare( "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'user_type'", $schema, $t_profiles ) ) : null;
			if ( empty( $col ) ) {
				$wpdb->query( "ALTER TABLE `{$t_profiles}` ADD COLUMN user_type VARCHAR(10) DEFAULT 'b2c'" );
			}
			$wpdb->update(
				$t_profiles,
				[
					'user_type'        => $user_type_lower,
					'guardian_active'  => $addon_guardian ? 1 : 0,
					'radar_plan'       => $addon_radar ? 'hebdo' : 'offert',
					'max_keywords'     => $addon_radar ? 75 : 20,
				],
				[ 'id' => $profile_id ],
				[ '%s', '%d', '%s', '%d' ],
				[ '%d' ]
			);
		}

		// 4) Créer utilisateur WordPress (email = identifiant unique) + préférences — si pas déjà créé par provision B2C.
		$wp_user    = get_user_by( 'email', $email );
		$wp_user_id = 0;
		if ( is_array( $provisioned ) && ! empty( $provisioned['wp_user_id'] ) ) {
			$wp_user_id = (int) $provisioned['wp_user_id'];
		}
		if ( $wp_user_id <= 0 && ! $wp_user ) {
			$login = sanitize_user( $email, true );
			if ( empty( $login ) ) {
				$login = 'user_' . preg_replace( '/[^a-z0-9]/i', '', $email );
			}
			$plain_pass_webhook = wp_generate_password( 24, true );
			$created = wp_create_user( $login, $plain_pass_webhook, $email );
			$wp_user_id = ( ! is_wp_error( $created ) ) ? (int) $created : 0;
		} elseif ( $wp_user_id <= 0 && $wp_user ) {
			$wp_user_id = (int) $wp_user->ID;
			// ANTI-RÉGRESSION [03 juin 2026] — Générer nouveau mot de passe pour user existant
			// $plain_pass_webhook non défini si user existait déjà → email credentials jamais envoyé
			if ( ! isset( $plain_pass_webhook ) || $plain_pass_webhook === '' ) {
				$plain_pass_webhook = wp_generate_password( 12, false );
				wp_set_password( $plain_pass_webhook, $wp_user_id );
			}
		}
		if ( $wp_user_id > 0 ) {
			update_user_meta( $wp_user_id, 'avi_user_type', $user_type_lower );
			update_user_meta( $wp_user_id, 'avi_preferred_lang', $preferred_lang );
			update_user_meta( $wp_user_id, 'avi_country', $country );
			update_user_meta( $wp_user_id, 'avi_category', $category !== '' ? $category : 'Client' );
			// Solo B2C : compte premium explicite (jamais agency), y compris filière SaaS sans provision wor1515.
			if ( $user_type_lower === 'b2c' && function_exists( 'avision_ensure_b2c_solo_account_meta' ) ) {
				avision_ensure_b2c_solo_account_meta( $wp_user_id );
			}
			// B2B : créer l'agence si elle n'existe pas encore
			if ( $user_type_lower === 'b2b' && $wp_user_id > 0 ) {
				$t_agencies = $wpdb->prefix . 'avi_saas_agencies';
				if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $t_agencies ) ) ) === $t_agencies ) {
					// Vérifier si une agence existe déjà pour cet admin
					$existing_agency_id = $wpdb->get_var( $wpdb->prepare(
						"SELECT id FROM `{$t_agencies}` WHERE admin_user_id = %d LIMIT 1",
						$wp_user_id
					) );
					if ( ! $existing_agency_id ) {
						$agency_name  = $company_name !== '' ? $company_name : ( 'Agency ' . $email );
						$agency_slug  = sanitize_title( $agency_name . '-' . $wp_user_id );
						$tier_int_map = array( 'ltd_agency_t1' => 1, 'ltd_agency_t2' => 2, 'ltd_agency_t3' => 3 );
						$tier_int     = isset( $tier_int_map[ $avi_product ] ) ? $tier_int_map[ $avi_product ] : 1;
						$agency_data  = array(
							'uuid'          => wp_generate_uuid4(),
							'name'          => $agency_name,
							'admin_user_id' => $wp_user_id,
							'tier'          => $tier_int,
						);
						$agency_fmt = array( '%s', '%s', '%d', '%d' );
						$wpdb->insert( $t_agencies, $agency_data, $agency_fmt );
						$new_agency_id = (int) $wpdb->insert_id;
						if ( $new_agency_id ) {
							update_user_meta( $wp_user_id, 'avi_agency_id', (int) $new_agency_id );
							update_user_meta( $wp_user_id, 'avi_agency_slug', $agency_slug );
							// Assigner le rôle avi_agency
							$wp_user_obj = get_user_by( 'id', $wp_user_id );
							if ( $wp_user_obj && ! in_array( 'avi_agency', (array) $wp_user_obj->roles, true ) ) {
								$wp_user_obj->set_role( 'avi_agency' );
								update_user_meta( $wp_user_id, 'is_agency_master', '1' );
							}
							// ANTI-RÉGRESSION [04 juin 2026] — Ne pas assigner agency_id au profil SaaS admin
							// $profile_id = profil legacy wor1515_avi_saas_users (admin), pas une fiche cliente
							// Assigner agency_id sur ce profil le fait apparaître comme fiche cliente dans la flotte
							// Le profil maître agence est créé séparément via l'import OAuth
						}
					} else {
						// Agence déjà existante (ex. onboarding) → tier uniquement, ne pas écraser le nom
						$tier_int_map = array( 'ltd_agency_t1' => 1, 'ltd_agency_t2' => 2, 'ltd_agency_t3' => 3 );
						$tier_int     = isset( $tier_int_map[ $avi_product ] ) ? $tier_int_map[ $avi_product ] : 1;
						$wpdb->update(
							$t_agencies,
							array( 'tier' => $tier_int ),
							array( 'admin_user_id' => $wp_user_id ),
							array( '%d' ),
							array( '%d' )
						);
						// Mettre à jour les meta
						update_user_meta( $wp_user_id, 'avi_agency_id', (int) $existing_agency_id );
						$existing_slug = get_user_meta( $wp_user_id, 'avi_agency_slug', true );
						if ( empty( $existing_slug ) ) {
							$agency_name  = $company_name !== '' ? $company_name : ( 'Agency ' . $email );
							update_user_meta( $wp_user_id, 'avi_agency_slug', sanitize_title( $agency_name . '-' . $wp_user_id ) );
							// Assigner le rôle avi_agency
							$wp_user_obj = get_user_by( 'id', $wp_user_id );
							if ( $wp_user_obj && ! in_array( 'avi_agency', (array) $wp_user_obj->roles, true ) ) {
								$wp_user_obj->set_role( 'avi_agency' );
								update_user_meta( $wp_user_id, 'is_agency_master', '1' );
							}
						}
					}
				}
			}
			// Email credentials B2B
			if ( $user_type_lower === 'b2b' && $wp_user_id > 0 && isset( $plain_pass_webhook ) && $plain_pass_webhook !== '' ) {
				$u_b2b       = get_userdata( $wp_user_id );
				$dash_b2b    = trailingslashit( home_url( '/agency-dashboard/' ) );
				if ( class_exists( '\Avision\Avision_Email_Manager' ) ) {
					// Template selon langue
					$b2b_tpl_map = array( 'fr' => 135, 'es' => 136, 'en' => 134 );
					$b2b_tpl_id  = isset( $b2b_tpl_map[ $preferred_lang ] ) ? $b2b_tpl_map[ $preferred_lang ] : 134;
					update_option( 'avi_brevo_tpl_ltd_agency_welcome', $b2b_tpl_id );
					\Avision\Avision_Email_Manager::send_avi_email(
						$wp_user_id,
						\Avision\Avision_Email_Manager::TYPE_AGENCY_WELCOME,
						array(
							'recipient_email' => $email,
							'user_name'       => $u_b2b ? (string) $u_b2b->display_name : $email,
							'user_pass'       => $plain_pass_webhook,
							'dashboard_url'   => $dash_b2b,
							'login_url'       => trailingslashit( home_url( '/connexion/' ) ),
							'account_type'    => 'Agency',
						)
					);
				}
			}
			if ( $profile_id ) {
				update_user_meta( $wp_user_id, 'avi_stripe_profile_id', (int) $profile_id );
			}
		}

		// 5) Lier pré-inscription au PID (profile_id dans le JSON `data`, pas colonne séparée)
		if ( $profile_id && function_exists( 'avision_merge_pre_inscription_profile_id_by_email' ) ) {
			avision_merge_pre_inscription_profile_id_by_email( $email, (int) $profile_id );
		}

		// 6) Enregistrer session Stripe (avi_stripe_sync) pour traçabilité
		$t_stripe = $wpdb->prefix . 'avi_stripe_sync';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $t_stripe ) ) ) === $t_stripe && ! empty( $session->id ) && $wp_user_id ) {
			$stripe_cus = isset( $session->customer ) ? $session->customer : ( isset( $session->customer_email ) ? 'email:' . $session->customer_email : $session->id );
			if ( $wp_user_id ) {
				$wpdb->replace(
					$t_stripe,
					[
						'user_id'       => $wp_user_id,
						'stripe_cus_id' => $stripe_cus,
						'plan_id'       => 'pro',
						'sub_status'    => 'active',
						'expiry_date'   => gmdate( 'Y-m-d H:i:s', strtotime( '+1 year' ) ),
					],
					[ '%d', '%s', '%s', '%s', '%s' ]
				);
			}
		}

		if ( $profile_id && function_exists( 'avision_stripe_persist_profile_lang_column' ) ) {
			$meta_arr = json_decode( wp_json_encode( $meta ), true );
			if ( ! is_array( $meta_arr ) ) {
				$meta_arr = array();
			}
			$meta_arr['lang'] = $preferred_lang;
			avision_stripe_persist_profile_lang_column( (int) $profile_id, $meta_arr );
		}

		if ( $profile_id && function_exists( 'avision_stripe_mysql_merge_profile_fields_from_metadata' ) && function_exists( 'avision_stripe_profile_fields_from_metadata' ) ) {
			$meta_for_row = json_decode( wp_json_encode( $meta ), true );
			if ( ! is_array( $meta_for_row ) ) {
				$meta_for_row = array();
			}
			$meta_for_row['city']         = $city;
			$meta_for_row['neighborhood'] = $neighborhood;
			$meta_for_row['category']     = $category !== '' ? $category : 'Client';
			$meta_for_row['phone']       = $phone;
			$meta_for_row['country']     = $country;
			if ( $company_name !== '' ) {
				$meta_for_row['company_name']  = $company_name;
				$meta_for_row['business_name'] = $company_name;
			}
			avision_stripe_mysql_merge_profile_fields_from_metadata( (int) $profile_id, avision_stripe_profile_fields_from_metadata( $meta_for_row ) );
		}

		if ( $profile_id && $addon_guardian && function_exists( 'avision_trigger_immediate_wave3' ) ) {
			avision_trigger_immediate_wave3( $profile_id );
		}

		// ── NONPROFIT TRACKING (Sprint 41B) ──────────
		// Bloc 1 : Tracking parrainage np_ref
		// Lire np_ref depuis metadata Stripe
		$np_ref_meta = isset( $meta->np_ref )
			? sanitize_text_field( (string) $meta->np_ref )
			: '';

		// Fallback : lire depuis user_meta si présent
		if ( $np_ref_meta === '' && $profile_id ) {
			$np_user_id_check = (int) get_post_meta(
				$profile_id, '_user_id', true );
			if ( ! $np_user_id_check ) {
				global $wpdb;
				$np_user_id_check = (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT user_id FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
						$profile_id
					)
				);
			}
			if ( $np_user_id_check > 0 ) {
				$np_ref_meta = (string) get_user_meta(
					$np_user_id_check, 'avi_np_ref_pending', true );
			}
		}

		if ( $np_ref_meta !== ''
			&& strpos( $np_ref_meta, 'np_' ) === 0 ) {
			$np_ref_slug = substr( $np_ref_meta, 3 );
			global $wpdb;

			// Trouver l'association référente
			$ref_np = $wpdb->get_row( $wpdb->prepare(
				"SELECT id, referral_count, current_coupon_percent
				 FROM {$wpdb->prefix}avi_nonprofits
				 WHERE slug = %s
				   AND verification_status = 'verified'
				 LIMIT 1",
				$np_ref_slug
			), ARRAY_A );

			if ( $ref_np ) {
				$ref_np_id = (int) $ref_np['id'];

				// Montant réel payé (en centimes → euros)
				$amount_paid = isset( $session->amount_total )
					? round( (float) $session->amount_total / 100, 2 )
					: 0.00;
				$donation    = round( $amount_paid * 0.5, 2 );

				// Récupérer referred_user_id
				$referred_uid = 0;
				if ( $profile_id ) {
					$referred_uid = (int) $wpdb->get_var(
						$wpdb->prepare(
							"SELECT user_id FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
							$profile_id
						)
					);
				}

				// Vérifier pas de doublon referral
				$existing_ref = $wpdb->get_var( $wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}avi_nonprofit_referrals
					 WHERE nonprofit_id = %d
					   AND referred_user_id = %d LIMIT 1",
					$ref_np_id, $referred_uid
				) );

				if ( ! $existing_ref ) {
					// Créer entrée referral
					$wpdb->insert(
						$wpdb->prefix . 'avi_nonprofit_referrals',
						array(
							'nonprofit_id'      => $ref_np_id,
							'referred_user_id'  => $referred_uid,
							'stripe_session_id' => isset( $session->id )
								? sanitize_text_field( $session->id ) : '',
							'amount_paid'       => $amount_paid,
							'donation_amount'   => $donation,
							'paid_out'          => 0,
							'created_at'        => current_time( 'mysql' ),
						),
						array( '%d', '%d', '%s', '%f', '%f', '%d', '%s' )
					);

					// Incrémenter compteur + vérifier palier
					$new_count = (int) $ref_np['referral_count'] + 1;
					$wpdb->update(
						$wpdb->prefix . 'avi_nonprofits',
						array(
							'referral_count'         => $new_count,
							'last_referral_at'       => current_time( 'mysql' ),
							'total_donations_earned' => $wpdb->get_var(
								$wpdb->prepare(
									"SELECT COALESCE(SUM(donation_amount), 0) FROM {$wpdb->prefix}avi_nonprofit_referrals WHERE nonprofit_id = %d",
									$ref_np_id
								)
							) + $donation,
						),
						array( 'id' => $ref_np_id ),
						array( '%d', '%s', '%f' ),
						array( '%d' )
					);

					// Vérifier upgrade palier
					if ( class_exists( 'Avision_Nonprofit_Admin' )
						&& method_exists( 'Avision_Nonprofit_Admin', 'maybe_upgrade_referral_tier' ) ) {
						Avision_Nonprofit_Admin::maybe_upgrade_referral_tier(
							$ref_np_id, $new_count );
					}

					// Nettoyer user_meta
					if ( $referred_uid > 0 ) {
						delete_user_meta(
							$referred_uid, 'avi_np_ref_pending' );
					}
				}
			}
		}

		// Bloc 2 : Tracking coupon NP- utilisé
		// Détecter si un coupon NP- a été appliqué
		$np_coupon_used = '';
		if ( isset( $session->discounts )
			&& is_array( $session->discounts ) ) {
			foreach ( $session->discounts as $discount ) {
				$coupon_id = '';
				if ( isset( $discount->coupon->id ) ) {
					$coupon_id = (string) $discount->coupon->id;
				} elseif ( isset( $discount->promotion_code )
					&& is_object( $discount->promotion_code )
					&& isset( $discount->promotion_code->coupon->id ) ) {
					$coupon_id = (string) $discount->promotion_code->coupon->id;
				}
				if ( strpos( $coupon_id, 'NP-' ) === 0 ) {
					$np_coupon_used = $coupon_id;
					break;
				}
			}
		}

		if ( $np_coupon_used !== '' ) {
			global $wpdb;
			// Trouver l'association propriétaire du coupon
			$np_owner = $wpdb->get_row( $wpdb->prepare(
				"SELECT id, user_id FROM {$wpdb->prefix}avi_nonprofits WHERE coupon_premium = %s LIMIT 1",
				$np_coupon_used
			), ARRAY_A );

			if ( $np_owner ) {
				// Logger l'utilisation du coupon
				error_log( '[AVI NP] Coupon nonprofit utilisé: '
					. $np_coupon_used
					. ' par email=' . $email
					. ' np_id=' . $np_owner['id'] );
			}
		}
		// ── FIN NONPROFIT TRACKING ────────────────────
	}

	/**
	 * Applique un upgrade in-app immédiatement sur le profil existant.
	 *
	 * @param int    $profile_id
	 * @param string $upgrade_type
	 * @return void
	 */
	private static function apply_in_app_upgrade( $profile_id, $upgrade_type ) {
		$profile_id   = (int) $profile_id;
		$upgrade_type = strtolower( trim( (string) $upgrade_type ) );
		if ( $profile_id <= 0 || $upgrade_type === '' ) {
			return;
		}

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

		$update = [];
		$format = [];
		if ( $upgrade_type === 'guardian' ) {
			$update['guardian_active'] = 1;
			$format[] = '%d';
			$has_pilot_guardian_mode = ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t_profiles}` LIKE %s", 'pilot_guardian_mode' ) ) === 'pilot_guardian_mode' );
			if ( $has_pilot_guardian_mode ) {
				$update['pilot_guardian_mode'] = 1;
				$format[] = '%d';
			}
		} elseif ( $upgrade_type === 'keyword_75' ) {
			$update['radar_plan'] = 'hebdo';
			$format[] = '%s';
			$update['max_keywords'] = 75;
			$format[] = '%d';
		} elseif ( $upgrade_type === 'daily_elite' ) {
			$update['radar_plan'] = 'elite';
			$format[] = '%s';
			$update['max_keywords'] = 100;
			$format[] = '%d';
		}

		if ( ! empty( $update ) ) {
			$wpdb->update(
				$t_profiles,
				$update,
				[ 'id' => $profile_id ],
				$format,
				[ '%d' ]
			);
		}

		if ( $upgrade_type === 'guardian' && function_exists( 'avision_trigger_immediate_wave3' ) ) {
			avision_trigger_immediate_wave3( $profile_id );
		}
	}
}

Avision_Stripe_Webhook::register();
