<?php
/**
 * Sprint 10-B — Abonnements Stripe (Radar + Gardien + bundles), webhook, checkout, déverrouillage Gardien.
 *
 * @package Avision
 */

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

/**
 * Migrations idempotentes (colonnes + index) — exécuté une fois par version d’option.
 *
 * @return void
 */
function avi_s10b_schema_migrate(): void {
	if ( ! isset( $GLOBALS['wpdb'] ) || ! is_object( $GLOBALS['wpdb'] ) ) {
		return;
	}
	global $wpdb;
	$done = get_option( 'avi_s10b_schema_migrated', '' );
	if ( $done === AVI_S10B_STRIPE_VERSION ) {
		return;
	}

	$profiles = $wpdb->prefix . 'avi_saas_profiles';
	$stripe   = $wpdb->prefix . 'avi_stripe_sync';

	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $profiles ) ) === $profiles ) {
		$cols = array(
			'guardian_scan_enabled' => "ADD COLUMN `guardian_scan_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=veille piliers requis, 1=scanning actif'",
			'guardian_activated_at'   => "ADD COLUMN `guardian_activated_at` DATETIME NULL COMMENT 'Date activation Guardian via Stripe'",
			'guardian_plan_expiry'    => "ADD COLUMN `guardian_plan_expiry` DATE NULL COMMENT 'Expiration abonnement Gardien'",
			'avi_bundle'              => "ADD COLUMN `avi_bundle` VARCHAR(30) NULL COMMENT 'starter_pro | elite_pro | NULL'",
			'guardian_unlock_at'      => "ADD COLUMN `guardian_unlock_at` DATETIME NULL COMMENT 'Déverrouillage scan Gardien (niveau 4)'",
		);
		foreach ( $cols as $name => $ddl ) {
			if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles}` LIKE %s", $name ) ) !== $name ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DDL fragment contrôlé.
				$wpdb->query( "ALTER TABLE `{$profiles}` {$ddl}" );
			}
		}
		// Rétrocompat : Gardien déjà actif avant S10-B → scan autorisé.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "UPDATE `{$profiles}` SET guardian_scan_enabled = 1 WHERE COALESCE(guardian_active,0) = 1 AND COALESCE(guardian_scan_enabled,0) = 0" );
	}

	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $stripe ) ) === $stripe ) {
		$cols_s = array(
			'stripe_event_id'        => "ADD COLUMN `stripe_event_id` VARCHAR(255) NULL COMMENT 'evt_… idempotence'",
			'stripe_subscription_id' => "ADD COLUMN `stripe_subscription_id` VARCHAR(255) NULL COMMENT 'sub_xxx Stripe'",
			'avi_product'            => "ADD COLUMN `avi_product` VARCHAR(50) NULL COMMENT 'radar_hebdo|…'",
			'action_type'            => "ADD COLUMN `action_type` VARCHAR(20) NOT NULL DEFAULT 'activate' COMMENT 'activate|upgrade|downgrade|cancel'",
			'currency'               => "ADD COLUMN `currency` VARCHAR(8) NOT NULL DEFAULT 'eur' COMMENT 'eur|usd'",
			'processed_at'           => "ADD COLUMN `processed_at` DATETIME NULL",
		);
		foreach ( $cols_s as $name => $ddl ) {
			if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$stripe}` LIKE %s", $name ) ) !== $name ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$wpdb->query( "ALTER TABLE `{$stripe}` {$ddl}" );
			}
		}
		$db_schema = defined( 'DB_NAME' ) ? DB_NAME : '';
		$tbl_name  = preg_replace( '/^`(.+)`$/', '$1', $stripe );
		if ( $db_schema !== '' && $tbl_name !== '' ) {
			$idx_exists = static function ( $index_name ) use ( $wpdb, $db_schema, $tbl_name ) {
				$r = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME = %s LIMIT 1',
						$db_schema,
						$tbl_name,
						$index_name
					)
				);
				return (bool) $r;
			};
			if ( ! $idx_exists( 'uk_avi_stripe_event_id' ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE `{$stripe}` ADD UNIQUE KEY `uk_avi_stripe_event_id` (`stripe_event_id`)" );
			}
			if ( ! $idx_exists( 'idx_avi_stripe_subscription_id' ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE `{$stripe}` ADD KEY `idx_avi_stripe_subscription_id` (`stripe_subscription_id`)" );
			}
			// Permettre plusieurs lignes par client : retirer l’unicité historique sur stripe_cus_id si présente.
			if ( $idx_exists( 'uk_stripe_cus' ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE `{$stripe}` DROP INDEX `uk_stripe_cus`" );
			}
			if ( ! $idx_exists( 'idx_stripe_cus' ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE `{$stripe}` ADD KEY `idx_stripe_cus` (`stripe_cus_id`)" );
			}
		}
	}

	update_option( 'avi_s10b_schema_migrated', AVI_S10B_STRIPE_VERSION, false );
}

/**
 * Palier wholesale agence selon le nombre de fiches gérées.
 *
 * @param int $listing_count Nombre de fiches (≥ 0).
 * @return int 1 = 1–10, 2 = 11–50, 3 = 51+.
 */
function avi_get_wholesale_tier( int $listing_count ): int {
	if ( $listing_count <= 10 ) {
		return 1;
	}
	if ( $listing_count <= 50 ) {
		return 2;
	}
	return 3;
}

/**
 * Clé d’option WordPress du Price ID Stripe wholesale.
 *
 * @param string $product guardian|hebdo|elite.
 * @param int    $tier    1|2|3.
 */
function avi_get_wholesale_price_key( string $product, int $tier ): string {
	$product = preg_replace( '/[^a-z]/', '', strtolower( $product ) );
	if ( ! in_array( $product, array( 'guardian', 'hebdo', 'elite' ), true ) ) {
		return '';
	}
	$tier = in_array( $tier, array( 1, 2, 3 ), true ) ? $tier : 1;
	return 'avi_stripe_price_ws_' . $product . '_t' . (string) $tier;
}

/**
 * Config produits Stripe AVI (S10-B).
 *
 * @return array<string, array<string, mixed>>
 */
function avi_get_stripe_plan_config(): array {
	// S11 : un seul Price ID Stripe par produit (option wp : price_key), multi-devises côté Stripe.
	return array(
		'ltd_solo' => array(
			'label'             => 'AVI Access',
			'price_key'         => 'avi_stripe_price_ltd_solo',
			'radar_plan'        => 'offert',
			'max_keywords'      => 20,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => true,
		),
		'ltd_agency_t1' => array(
			'label'             => 'Agency Access T1',
			'price_key'         => 'avi_stripe_price_ltd_agency_t1',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => true,
			'agency_tier'       => 1,
		),
		'ltd_agency_t2' => array(
			'label'             => 'Agency Access T2',
			'price_key'         => 'avi_stripe_price_ltd_agency_t2',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => true,
			'agency_tier'       => 2,
		),
		'ltd_agency_t3' => array(
			'label'             => 'Agency Access T3',
			'price_key'         => 'avi_stripe_price_ltd_agency_t3',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => true,
			'agency_tier'       => 3,
		),
		'ws_guardian_hebdo' => array(
			'label'             => 'Bundle Guardian + Radar Hebdo',
			'price_key'         => 'avi_stripe_price_ws_guardian_hebdo_t',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => true,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => false,
			'wholesale'         => true,
		),
		'ws_guardian_elite' => array(
			'label'             => 'Bundle Guardian + Radar Elite',
			'price_key'         => 'avi_stripe_price_ws_guardian_elite_t',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => true,
			'guardian_deferred' => false,
			'bundle'            => null,
			'one_time'          => false,
			'wholesale'         => true,
		),
		AVI_STRIPE_PRODUCT_RADAR_HEBDO => array(
			'label'             => 'Growth Radar Weekly',
			'price_key'         => 'avi_stripe_price_radar_hebdo',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
		),
		AVI_STRIPE_PRODUCT_RADAR_ELITE => array(
			'label'             => 'Growth Radar Elite',
			'price_key'         => 'avi_stripe_price_radar_elite',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
		),
		AVI_STRIPE_PRODUCT_GUARDIAN => array(
			'label'             => 'Guardian Mode',
			'price_key'         => 'avi_stripe_price_guardian',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => null,
		),
		AVI_STRIPE_PRODUCT_BUNDLE_STARTER => array(
			'label'             => 'Pack Starter Pro',
			'price_key'         => 'avi_stripe_price_bundle_starter',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => 'starter_pro',
		),
		AVI_STRIPE_PRODUCT_BUNDLE_ELITE => array(
			'label'             => 'Pack Elite Pro',
			'price_key'         => 'avi_stripe_price_bundle_elite',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => 'elite_pro',
		),
		// Wholesale B2B — mêmes effets métier que retail ; Price IDs distincts par palier.
		AVI_STRIPE_PRODUCT_WS_GUARDIAN_T1 => array(
			'label'             => 'Guardian Wholesale 1-10 fiches',
			'price_key'         => 'avi_stripe_price_ws_guardian_t1',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 1,
		),
		AVI_STRIPE_PRODUCT_WS_GUARDIAN_T2 => array(
			'label'             => 'Guardian Wholesale 11-50 fiches',
			'price_key'         => 'avi_stripe_price_ws_guardian_t2',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 2,
		),
		AVI_STRIPE_PRODUCT_WS_GUARDIAN_T3 => array(
			'label'             => 'Guardian Wholesale 51+ fiches',
			'price_key'         => 'avi_stripe_price_ws_guardian_t3',
			'radar_plan'        => null,
			'max_keywords'      => null,
			'guardian'          => true,
			'guardian_deferred' => true,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 3,
		),
		AVI_STRIPE_PRODUCT_WS_HEBDO_T1 => array(
			'label'             => 'Radar Hebdo Wholesale 1-10 fiches',
			'price_key'         => 'avi_stripe_price_ws_hebdo_t1',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 1,
		),
		AVI_STRIPE_PRODUCT_WS_HEBDO_T2 => array(
			'label'             => 'Radar Hebdo Wholesale 11-50 fiches',
			'price_key'         => 'avi_stripe_price_ws_hebdo_t2',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 2,
		),
		AVI_STRIPE_PRODUCT_WS_HEBDO_T3 => array(
			'label'             => 'Radar Hebdo Wholesale 51+ fiches',
			'price_key'         => 'avi_stripe_price_ws_hebdo_t3',
			'radar_plan'        => 'hebdo',
			'max_keywords'      => 75,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 3,
		),
		AVI_STRIPE_PRODUCT_WS_ELITE_T1 => array(
			'label'             => 'Radar Élite Wholesale 1-10 fiches',
			'price_key'         => 'avi_stripe_price_ws_elite_t1',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 1,
		),
		AVI_STRIPE_PRODUCT_WS_ELITE_T2 => array(
			'label'             => 'Radar Élite Wholesale 11-50 fiches',
			'price_key'         => 'avi_stripe_price_ws_elite_t2',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 2,
		),
		AVI_STRIPE_PRODUCT_WS_ELITE_T3 => array(
			'label'             => 'Radar Élite Wholesale 51+ fiches',
			'price_key'         => 'avi_stripe_price_ws_elite_t3',
			'radar_plan'        => 'elite',
			'max_keywords'      => 100,
			'guardian'          => false,
			'guardian_deferred' => false,
			'bundle'            => null,
			'wholesale'         => true,
			'tier'              => 3,
		),
	);
}

/**
 * Invalide le cache KPI flotte de l’agence liée au profil (user_id SaaS / WP aligné).
 *
 * @param int $user_id user_id colonne avi_saas_profiles.
 */
function avi_invalidate_agency_fleet_cache( int $user_id ): void {
	global $wpdb;
	if ( $user_id <= 0 || ! isset( $wpdb ) || ! $wpdb ) {
		return;
	}
	$agency_admin = $wpdb->get_var(
		$wpdb->prepare(
			"SELECT ag.admin_user_id
			 FROM {$wpdb->prefix}avi_saas_agencies ag
			 JOIN {$wpdb->prefix}avi_saas_profiles sp ON sp.agency_id = ag.id
			 WHERE sp.user_id = %d LIMIT 1",
			$user_id
		)
	);
	if ( $agency_admin ) {
		delete_option( 'avi_fleet_kpis_cache_' . (int) $agency_admin );
	}
}

/**
 * @param int         $user_id      ID utilisateur (même sémantique que colonne user_id profil).
 * @param string      $avi_product  Clé produit (metadata).
 * @param array       $config       Entrée avi_get_stripe_plan_config().
 * @param string|null $period_end_ymd Date Y-m-d fin de période (abonnement).
 */
function avi_stripe_activate_avi_product( int $user_id, string $avi_product, array $config, ?string $period_end_ymd = null ): bool {
	global $wpdb;
	$table  = $wpdb->prefix . 'avi_saas_profiles';
	$fields = array();

	if ( $config['radar_plan'] !== null ) {
		$fields['radar_plan']       = $config['radar_plan'];
		$fields['max_keywords']    = (int) $config['max_keywords'];
		$fields['last_radar_scan'] = null;
	}

	if ( ! empty( $config['guardian'] ) ) {
		$fields['guardian_active']       = 1;
		$fields['guardian_activated_at'] = current_time( 'mysql', true );
		$fields['guardian_scan_enabled'] = empty( $config['guardian_deferred'] ) ? 1 : 0;
	}

	if ( ! empty( $config['bundle'] ) ) {
		$fields['avi_bundle'] = $config['bundle'];
	}

	if ( $period_end_ymd && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $period_end_ymd ) && ! empty( $config['guardian'] ) ) {
		$fields['guardian_plan_expiry'] = $period_end_ymd;
	}

	if ( empty( $fields ) ) {
		return false;
	}

	$updated = $wpdb->update( $table, $fields, array( 'user_id' => $user_id ) );
	avi_invalidate_agency_fleet_cache( $user_id );
	error_log( '[AVI-S10B] Activé : ' . $avi_product . ' user=' . $user_id );
	if ( $updated !== false && class_exists( '\Avision\Avision_Email_Manager' ) ) {
		$label = isset( $config['label'] ) ? (string) $config['label'] : $avi_product;
		$wp_u  = function_exists( 'get_userdata' ) ? get_userdata( $user_id ) : null;
		\Avision\Avision_Email_Manager::send_avi_email(
			$user_id,
			\Avision\Avision_Email_Manager::TYPE_STRIPE_ACTIVATE,
			array(
				'recipient_email' => ( $wp_u && ! empty( $wp_u->user_email ) ) ? (string) $wp_u->user_email : '',
				'user_name'       => $wp_u ? (string) $wp_u->display_name : '',
				'plan_label'      => $label,
				'dashboard_url'   => trailingslashit( home_url( '/premium-dashboard/' ) ),
			)
		);
	}
	return $updated !== false;
}

/**
 * @param int    $user_id     ID utilisateur profil.
 * @param string $avi_product Clé produit.
 * @param array  $config      Config.
 */
function avi_stripe_deactivate_avi_product( int $user_id, string $avi_product, array $config ): bool {
	global $wpdb;
	$table  = $wpdb->prefix . 'avi_saas_profiles';
	$fields = array();

	if ( $config['radar_plan'] !== null ) {
		$fields['radar_plan']       = 'offert';
		$fields['max_keywords']    = 20;
		$fields['last_radar_scan'] = null;
	}

	if ( ! empty( $config['guardian'] ) ) {
		$fields['guardian_active']        = 0;
		$fields['guardian_scan_enabled'] = 0;
		$fields['guardian_plan_expiry']  = gmdate( 'Y-m-d' );
	}

	$bundle_cleared = false;
	if ( ! empty( $config['bundle'] ) ) {
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table contrôlé.
		$bundle_cleared = (bool) $wpdb->query( $wpdb->prepare( "UPDATE `{$table}` SET avi_bundle = NULL WHERE user_id = %d", $user_id ) );
	}

	if ( empty( $fields ) && ! $bundle_cleared ) {
		return false;
	}

	$updated = true;
	if ( ! empty( $fields ) ) {
		$updated = $wpdb->update( $table, $fields, array( 'user_id' => $user_id ) );
	}
	avi_invalidate_agency_fleet_cache( $user_id );
	error_log( '[AVI-S10B] Résilié : ' . $avi_product . ' user=' . $user_id );
	if ( ! empty( $fields ) && $updated !== false && class_exists( '\Avision\Avision_Email_Manager' ) ) {
		$label = isset( $config['label'] ) ? (string) $config['label'] : $avi_product;
		$wp_u  = function_exists( 'get_userdata' ) ? get_userdata( $user_id ) : null;
		\Avision\Avision_Email_Manager::send_avi_email(
			$user_id,
			\Avision\Avision_Email_Manager::TYPE_STRIPE_CANCEL,
			array(
				'recipient_email' => ( $wp_u && ! empty( $wp_u->user_email ) ) ? (string) $wp_u->user_email : '',
				'user_name'     => $wp_u ? (string) $wp_u->display_name : '',
				'plan_label'    => $label,
				'dashboard_url' => trailingslashit( home_url( '/premium-dashboard/' ) ),
			)
		);
	}
	return $updated !== false;
}

/**
 * Gestion subscriptions AVI (plans + bundles).
 *
 * @param object $subscription    Objet Stripe Subscription.
 * @param string $action_type     created|updated|deleted (vide = traité comme updated pour la logique métier).
 * @param string $stripe_event_id ex. evt_….
 */
function avi_handle_avi_subscription_event( object $subscription, string $action_type = '', string $stripe_event_id = '' ): void {
	global $wpdb;
	$sync_table = $wpdb->prefix . 'avi_stripe_sync';

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

	$col_evt = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$sync_table}` LIKE %s", 'stripe_event_id' ) );
	if ( $col_evt === 'stripe_event_id' && $stripe_event_id !== '' ) {
		$already = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$sync_table}` WHERE stripe_event_id = %s LIMIT 1",
				$stripe_event_id
			)
		);
		if ( $already ) {
			return;
		}
	}

	$meta        = isset( $subscription->metadata ) && is_object( $subscription->metadata ) ? $subscription->metadata : new \stdClass();
	$user_id     = isset( $meta->user_id ) ? (int) $meta->user_id : 0;
	$avi_product = isset( $meta->avi_product ) ? sanitize_key( (string) $meta->avi_product ) : '';
	$currency    = isset( $subscription->currency ) ? strtolower( (string) $subscription->currency ) : 'eur';
	if ( ! in_array( $currency, array( 'eur', 'usd' ), true ) ) {
		$currency = 'eur';
	}
	$sub_id = isset( $subscription->id ) ? (string) $subscription->id : '';

	if ( ! $user_id || $avi_product === '' ) {
		error_log( '[AVI-S10B] Webhook ignoré — metadata manquante event=' . $stripe_event_id );
		return;
	}

	$config_all = avi_get_stripe_plan_config();
	$config     = isset( $config_all[ $avi_product ] ) ? $config_all[ $avi_product ] : null;
	if ( ! $config ) {
		error_log( '[AVI-S10B] Produit inconnu : ' . $avi_product );
		return;
	}

	$period_end = null;
	if ( isset( $subscription->current_period_end ) && is_numeric( $subscription->current_period_end ) ) {
		$period_end = gmdate( 'Y-m-d', (int) $subscription->current_period_end );
	}

	if ( $action_type === 'deleted' ) {
		$row_action_type = 'cancel';
		avi_stripe_deactivate_avi_product( $user_id, $avi_product, $config );
	} else {
		$row_action_type = 'activate';
		$st = isset( $subscription->status ) ? (string) $subscription->status : '';
		if ( in_array( $st, array( 'active', 'trialing', 'past_due' ), true ) || $st === '' ) {
			avi_stripe_activate_avi_product( $user_id, $avi_product, $config, $period_end );
		}
	}

	$cus_id = isset( $subscription->customer ) ? (string) $subscription->customer : ( $sub_id !== '' ? $sub_id : 'unknown' );
	$expiry = $period_end ? ( $period_end . ' 23:59:59' ) : gmdate( 'Y-m-d H:i:s', strtotime( '+30 days' ) );
	$row    = array(
		'user_id'                => $user_id,
		'stripe_cus_id'          => $cus_id !== '' ? $cus_id : $sub_id,
		'plan_id'                => 'pro',
		'sub_status'             => ( $action_type === 'deleted' ) ? 'canceled' : ( isset( $subscription->status ) ? (string) $subscription->status : 'active' ),
		'expiry_date'            => $expiry,
		'stripe_event_id'        => $stripe_event_id,
		'stripe_subscription_id' => $sub_id,
		'avi_product'            => $avi_product,
		'action_type'            => $row_action_type,
		'currency'               => $currency,
		'processed_at'           => current_time( 'mysql', true ),
	);
	$formats = array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );
	if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$sync_table}` LIKE %s", 'stripe_event_id' ) ) !== 'stripe_event_id' ) {
		unset( $row['stripe_event_id'], $row['stripe_subscription_id'], $row['avi_product'], $row['action_type'], $row['currency'], $row['processed_at'] );
		$formats = array( '%d', '%s', '%s', '%s', '%s' );
	}
	$wpdb->insert( $sync_table, $row, $formats );
}

/**
 * Gère un paiement échoué sur un abonnement AVI.
 *
 * Tentative 1–2 : email d’alerte uniquement.
 * Tentative 3+  : suspension partielle (guardian_scan_enabled = 0).
 * Résiliation    : {@see avi_handle_avi_subscription_event()} sur customer.subscription.deleted.
 *
 * @param string $stripe_subscription_id ID Stripe sub_….
 * @param int    $attempt_nb             invoice.attempt_count.
 * @param string $stripe_event_id        evt_… (idempotence sur avi_stripe_sync).
 * @return void
 */
function avi_handle_payment_failed( string $stripe_subscription_id, int $attempt_nb, string $stripe_event_id = '' ): void {
	global $wpdb;

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

	$col_evt = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$sync_table}` LIKE %s", 'stripe_event_id' ) );
	if ( $col_evt === 'stripe_event_id' && $stripe_event_id !== '' ) {
		$already = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$sync_table}` WHERE stripe_event_id = %s LIMIT 1",
				$stripe_event_id
			)
		);
		if ( $already ) {
			return;
		}
	}

	$stripe_event_id_use = $stripe_event_id !== '' ? $stripe_event_id : ( 'pf_' . uniqid( '', true ) );

	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- nom de table contrôlé.
	$sync = $wpdb->get_row(
		$wpdb->prepare(
			"SELECT user_id, avi_product, stripe_cus_id
			FROM `{$sync_table}`
			WHERE stripe_subscription_id = %s
			AND action_type = %s
			ORDER BY processed_at DESC
			LIMIT 1",
			$stripe_subscription_id,
			'activate'
		),
		ARRAY_A
	);

	if ( ! is_array( $sync ) || empty( $sync['user_id'] ) ) {
		if ( function_exists( 'error_log' ) ) {
			error_log(
				'[AVI-STRIPE] Payment failed: subscription non trouvée ' . $stripe_subscription_id
			);
		}
		return;
	}

	$user_id     = (int) $sync['user_id'];
	$avi_product = isset( $sync['avi_product'] ) ? sanitize_key( (string) $sync['avi_product'] ) : '';
	$cus_id      = isset( $sync['stripe_cus_id'] ) ? (string) $sync['stripe_cus_id'] : '';

	if ( $attempt_nb <= 2 ) {
		avi_send_payment_failed_email( $user_id, $attempt_nb, false );
		return;
	}

	$profiles = $wpdb->prefix . 'avi_saas_profiles';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $profiles ) ) === $profiles
		&& $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$profiles}` LIKE %s", 'guardian_scan_enabled' ) ) === 'guardian_scan_enabled' ) {
		$wpdb->update(
			$profiles,
			array( 'guardian_scan_enabled' => 0 ),
			array( 'user_id' => $user_id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	$expiry = gmdate( 'Y-m-d H:i:s', strtotime( '+30 days' ) );
	$row    = array(
		'user_id'                => $user_id,
		'stripe_cus_id'          => $cus_id !== '' ? $cus_id : $stripe_subscription_id,
		'plan_id'                => 'pro',
		'sub_status'             => 'past_due',
		'expiry_date'            => $expiry,
		'stripe_event_id'        => $stripe_event_id_use,
		'stripe_subscription_id' => $stripe_subscription_id,
		'avi_product'            => $avi_product !== '' ? $avi_product : 'unknown',
		'action_type'            => 'downgrade',
		'currency'               => 'eur',
		'processed_at'           => current_time( 'mysql', true ),
	);
	$formats = array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );
	if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$sync_table}` LIKE %s", 'stripe_event_id' ) ) !== 'stripe_event_id' ) {
		unset( $row['stripe_event_id'], $row['stripe_subscription_id'], $row['avi_product'], $row['action_type'], $row['currency'], $row['processed_at'] );
		$formats = array( '%d', '%s', '%s', '%s', '%s' );
	}
	$wpdb->insert( $sync_table, $row, $formats );

	avi_send_payment_failed_email( $user_id, $attempt_nb, true );

	if ( $attempt_nb >= 3 && class_exists( '\Avision\Avision_Email_Manager' ) && function_exists( 'get_userdata' ) ) {
		$u_suspend = get_userdata( $user_id );
		$portal_suspend = trim( (string) get_option( 'avi_stripe_customer_portal_url', '' ) );
		if ( $portal_suspend === '' ) {
			$portal_suspend = home_url( '/' );
		}
		\Avision\Avision_Email_Manager::send_avi_email(
			$user_id,
			\Avision\Avision_Email_Manager::TYPE_ACCESS_SUSPENDED,
			array(
				'user_name'    => ( $u_suspend instanceof \WP_User ) ? (string) $u_suspend->display_name : '',
				'attempt_nb'   => $attempt_nb,
				'portal_url'   => (string) apply_filters( 'avi_stripe_payment_failed_portal_url', $portal_suspend, $user_id ),
				'dashboard_url' => trailingslashit( home_url( '/premium-dashboard/' ) ),
			)
		);
	}

	if ( function_exists( 'error_log' ) ) {
		error_log(
			'[AVI-STRIPE] Suspension partielle user=' . $user_id . ' tentative=' . $attempt_nb
		);
	}
}

/**
 * Envoie l’email de paiement échoué.
 *
 * Tentative 1–2 : avertissement.
 * Tentative 3+  : urgence + lien mise à jour CB (URL configurable).
 *
 * @param int  $user_id     ID utilisateur WordPress.
 * @param int  $attempt_nb  Numéro de tentative Stripe.
 * @param bool $suspended   true si scan Gardien mis en veille.
 * @return void
 */
function avi_send_payment_failed_email( int $user_id, int $attempt_nb, bool $suspended = false ): void {
	if ( ! function_exists( 'get_userdata' ) ) {
		return;
	}
	$user = get_userdata( $user_id );
	if ( ! $user || empty( $user->user_email ) ) {
		return;
	}

	$portal = trim( (string) get_option( 'avi_stripe_customer_portal_url', '' ) );
	if ( $portal === '' ) {
		$portal = home_url( '/' );
	}
	$update_url = (string) apply_filters( 'avi_stripe_payment_failed_portal_url', $portal, $user_id );

	$params = array(
		'recipient_email' => (string) $user->user_email,
		'user_name'        => (string) $user->display_name,
		'attempt_nb'       => $attempt_nb,
		'suspended'        => $suspended ? 'true' : 'false',
		'portal_url'       => $update_url,
		'dashboard_url'    => trailingslashit( home_url( '/premium-dashboard/' ) ),
	);

	$sent = false;
	if ( class_exists( '\Avision\Avision_Email_Manager' ) ) {
		$sent = \Avision\Avision_Email_Manager::send_avi_email(
			$user_id,
			\Avision\Avision_Email_Manager::TYPE_PAYMENT_FAILED,
			$params
		);
	}

	if ( ! $sent && function_exists( 'wp_mail' ) ) {
		$subject = ( $attempt_nb <= 2 )
			? '[AVI] Payment pending — Update required'
			: '[AVI] Access suspended — Payment failed';
		wp_mail(
			$user->user_email,
			$subject,
			'Please update your payment method: ' . $update_url
		);
	}
}

/**
 * Crée une session Stripe Checkout pour un produit AVI.
 *
 * @param int    $user_id     Utilisateur WP connecté (= user_id profil).
 * @param string $avi_product Clé produit.
 * @param string $currency    eur|usd (conservé pour signature ; Price ID unique S11).
 * @return string URL checkout ou chaîne sentinelle vide / non configuré.
 */
function avi_create_stripe_checkout_session( int $user_id, string $avi_product, string $currency = 'eur' ): string {
	$config_all = avi_get_stripe_plan_config();
	$config     = isset( $config_all[ $avi_product ] ) ? $config_all[ $avi_product ] : null;
	if ( ! $config ) {
		return '';
	}

	$price_key = isset( $config['price_key'] ) ? (string) $config['price_key'] : '';
	if ( $price_key === '' ) {
		return '';
	}
	$price_id = get_option( $price_key, '' );
	$price_id = is_string( $price_id ) ? trim( $price_id ) : '';

	if ( $price_id === '' || strpos( $price_id, 'price_XXXX' ) === 0 ) {
		error_log( '[AVI-S10B] Price ID non configuré : ' . $price_key );
		return '#price-not-configured';
	}

	$secret_key = avi_stripe_s10b_resolve_secret_key();
	if ( $secret_key === '' ) {
		return '';
	}

	if ( in_array( $avi_product, array( 'ltd_agency_t1', 'ltd_agency_t2', 'ltd_agency_t3' ), true ) ) {
		$success_base = trailingslashit( home_url( '/btob-onboarding/' ) );
	} else {
		$success_base = apply_filters(
			'avi_stripe_s10b_checkout_success_url_base',
			trailingslashit( home_url( '/premium-dashboard/' ) )
		);
	}
	$success_url  = $success_base . '?avi_plan_success=1&session_id={CHECKOUT_SESSION_ID}';
	$cancel_url   = $success_base . '?avi_plan_cancel=1';

	$u = get_userdata( $user_id );
	$email = ( $u && is_email( $u->user_email ) ) ? $u->user_email : '';

	$mode = ( isset( $config['one_time'] ) && $config['one_time'] ) ? 'payment' : 'subscription';

	$body = array(
		'mode'                    => $mode,
		'line_items[0][price]'    => $price_id,
		'line_items[0][quantity]' => '1',
		'success_url'             => $success_url,
		'cancel_url'              => $cancel_url,
		'allow_promotion_codes'   => 'true',
	);
	if ( $mode !== 'payment' ) {
		$body['subscription_data[metadata][user_id]']     = (string) $user_id;
		$body['subscription_data[metadata][avi_product]'] = $avi_product;
	} else {
		$body['payment_intent_data[metadata][user_id]']     = (string) $user_id;
		$body['payment_intent_data[metadata][avi_product]'] = $avi_product;
	}
	if ( $email !== '' ) {
		$body['customer_email'] = $email;
	}
	if ( in_array( $avi_product, array( 'ltd_agency_t1', 'ltd_agency_t2', 'ltd_agency_t3' ), true ) ) {
		$body['metadata[user_type]'] = 'B2B';
	}

	// Metadata session (checkout.session.completed lit $session->metadata).
	$body['metadata[avi_product]'] = $avi_product;

	$company_name = '';
	$city         = '';
	$neighborhood = '';
	global $wpdb;
	if ( isset( $wpdb ) && $wpdb instanceof \wpdb && $user_id > 0 ) {
		$pt = $wpdb->prefix . 'avi_saas_profiles';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $pt ) ) === $pt ) {
			$has_wp_uid = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$pt}` LIKE %s", 'wp_user_id' ) ) === 'wp_user_id';
			$sql        = $has_wp_uid
				? $wpdb->prepare( "SELECT business_name, city, neighborhood FROM `{$pt}` WHERE wp_user_id = %d ORDER BY id ASC LIMIT 1", $user_id )
				: $wpdb->prepare( "SELECT business_name, city, neighborhood FROM `{$pt}` WHERE user_id = %d ORDER BY id ASC LIMIT 1", $user_id );
			$pr         = $wpdb->get_row( $sql, ARRAY_A );
			if ( is_array( $pr ) ) {
				$company_name = isset( $pr['business_name'] ) ? trim( (string) $pr['business_name'] ) : '';
				$city         = isset( $pr['city'] ) ? trim( (string) $pr['city'] ) : '';
				$neighborhood = isset( $pr['neighborhood'] ) ? trim( (string) $pr['neighborhood'] ) : '';
			}
		}
	}

	// Metadata enrichies pour le webhook (company_name, city, email, report_id).
	if ( $company_name !== '' ) {
		$body['metadata[company_name]'] = sanitize_text_field( $company_name );
	}
	if ( $city !== '' ) {
		$body['metadata[city]'] = sanitize_text_field( $city );
	}
	// ═══════════════════════════════════════════════════════════════
	// ANTI-RÉGRESSION — metadata[neighborhood] obligatoire pour pipeline
	// neighborhood → profil → KW2 (règle #151).
	// Sans cette ligne, le webhook reçoit neighborhood='' →
	// UPDATE profil ignoré → KW2 jamais généré.
	// NE JAMAIS supprimer sans autorisation Anthony.
	// ═══════════════════════════════════════════════════════════════
	$neighborhood_meta = '';
	if ( ! empty( $neighborhood ) ) {
		$neighborhood_meta = sanitize_text_field( $neighborhood );
	}
	if ( $neighborhood_meta === '' ) {
		// Fallback pré-inscription si non transmis par le formulaire
		$pre_neigh = function_exists( 'avision_get_latest_pre_inscription_by_email' ) && is_email( $email )
			? avision_get_latest_pre_inscription_by_email( $email )
			: null;
		if ( is_array( $pre_neigh ) && ! empty( $pre_neigh['neighborhood'] ) ) {
			$neighborhood_meta = sanitize_text_field( (string) $pre_neigh['neighborhood'] );
		}
	}
	if ( $neighborhood_meta !== '' ) {
		$body['metadata[neighborhood]'] = $neighborhood_meta;
	}
	if ( $email !== '' ) {
		$body['metadata[email]'] = sanitize_email( (string) $email );
	}
	$report_id = (string) apply_filters( 'avi_stripe_s10b_checkout_metadata_report_id', '', $user_id, $avi_product );
	if ( $report_id !== '' ) {
		$body['metadata[report_id]'] = sanitize_text_field( $report_id );
	}

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

	if ( is_wp_error( $response ) ) {
		error_log( '[AVI-S10B] Checkout session error : ' . $response->get_error_message() );
		return '';
	}

	$code = (int) wp_remote_retrieve_response_code( $response );
	$raw  = wp_remote_retrieve_body( $response );
	$data = json_decode( $raw, true );
	if ( $code < 200 || $code >= 300 || ! is_array( $data ) ) {
		error_log( '[AVI-S10B] Checkout session HTTP ' . $code . ' body=' . substr( (string) $raw, 0, 400 ) );
		return '';
	}

	return isset( $data['url'] ) ? (string) $data['url'] : '';
}

/**
 * Déverrouille le scan Guardian au niveau 4 (abonnement en veille).
 *
 * @param int $user_id   Utilisateur.
 * @param int $new_level Niveau atteint.
 */
function avi_maybe_unlock_guardian( int $user_id, int $new_level ): void {
	if ( $new_level < AVI_GUARDIAN_UNLOCK_LEVEL ) {
		return;
	}

	global $wpdb;
	$table = $wpdb->prefix . 'avi_saas_profiles';

	$has_scan = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'guardian_scan_enabled' ) );
	if ( $has_scan !== 'guardian_scan_enabled' ) {
		return;
	}

	$profile = $wpdb->get_row(
		$wpdb->prepare(
			"SELECT guardian_active, guardian_scan_enabled, avi_bundle FROM `{$table}` WHERE user_id = %d ORDER BY id DESC LIMIT 1",
			$user_id
		),
		ARRAY_A
	);

	if (
		! $profile
		|| (int) $profile['guardian_active'] !== 1
		|| (int) $profile['guardian_scan_enabled'] !== 0
	) {
		return;
	}

	$wpdb->update(
		$table,
		array(
			'guardian_scan_enabled' => 1,
			'guardian_unlock_at'    => current_time( 'mysql', true ),
		),
		array( 'user_id' => $user_id ),
		array( '%d', '%s' ),
		array( '%d' )
	);

	avi_invalidate_agency_fleet_cache( $user_id );

	error_log(
		'[AVI-S10B] Guardian déverrouillé user=' . $user_id
		. ' bundle=' . ( isset( $profile['avi_bundle'] ) ? (string) $profile['avi_bundle'] : 'none' )
	);

	if ( class_exists( '\Avision\Avision_Email_Manager' ) && function_exists( 'get_userdata' ) ) {
		$wp_u = get_userdata( $user_id );
		\Avision\Avision_Email_Manager::send_avi_email(
			$user_id,
			\Avision\Avision_Email_Manager::TYPE_GUARDIAN_UNLOCK,
			array(
				'recipient_email' => $wp_u && ! empty( $wp_u->user_email ) ? (string) $wp_u->user_email : '',
				'user_name'       => $wp_u ? (string) $wp_u->display_name : '',
				'dashboard_url'   => trailingslashit( home_url( '/premium-dashboard/' ) ),
			)
		);
	}
}

/**
 * Clé secrète Stripe (checkout S10-B, upgrade, webhooks).
 * Priorité : AVI_STRIPE_SECRET_KEY (wp-config) → AVISION_STRIPE_SECRET_KEY → option WP.
 *
 * @return string
 */
function avi_stripe_s10b_resolve_secret_key(): string {
	// 1. Constante wp-config.php (priorité absolue).
	if ( defined( 'AVI_STRIPE_SECRET_KEY' ) && ! empty( AVI_STRIPE_SECRET_KEY ) ) {
		return is_string( AVI_STRIPE_SECRET_KEY ) ? trim( AVI_STRIPE_SECRET_KEY ) : (string) AVI_STRIPE_SECRET_KEY;
	}
	// 2. Constante alternative (legacy).
	if ( defined( 'AVISION_STRIPE_SECRET_KEY' ) && ! empty( AVISION_STRIPE_SECRET_KEY ) ) {
		return is_string( AVISION_STRIPE_SECRET_KEY ) ? trim( AVISION_STRIPE_SECRET_KEY ) : (string) AVISION_STRIPE_SECRET_KEY;
	}
	// 3. Option WP (fallback).
	return function_exists( 'get_option' ) ? trim( (string) get_option( 'avision_stripe_secret_key', '' ) ) : '';
}

/**
 * Dernière souscription Stripe « activate » avec sub_id pour un user_id SaaS / WP (metadata checkout).
 *
 * @param int $user_id Colonne user_id dans avi_stripe_sync.
 * @return string sub_… ou chaîne vide.
 */
function avi_get_latest_active_stripe_subscription_id( int $user_id ): string {
	if ( $user_id <= 0 || ! isset( $GLOBALS['wpdb'] ) || ! is_object( $GLOBALS['wpdb'] ) ) {
		return '';
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_stripe_sync';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return '';
	}
	if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'stripe_subscription_id' ) ) !== 'stripe_subscription_id' ) {
		return '';
	}
	$sid = $wpdb->get_var(
		$wpdb->prepare(
			"SELECT stripe_subscription_id FROM `{$table}` WHERE user_id = %d AND action_type = %s AND stripe_subscription_id IS NOT NULL AND stripe_subscription_id != '' ORDER BY processed_at DESC LIMIT 1",
			$user_id,
			'activate'
		)
	);
	return is_string( $sid ) ? trim( $sid ) : '';
}

/**
 * Souscription active pour un produit AVI donné (metadata avi_product).
 *
 * @param int    $user_id     user_id dans avi_stripe_sync.
 * @param string $avi_product ex. radar_hebdo.
 * @return string
 */
function avi_get_active_stripe_subscription_id_for_product( int $user_id, string $avi_product ): string {
	if ( $user_id <= 0 || $avi_product === '' || ! isset( $GLOBALS['wpdb'] ) || ! is_object( $GLOBALS['wpdb'] ) ) {
		return '';
	}
	global $wpdb;
	$table = $wpdb->prefix . 'avi_stripe_sync';
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return '';
	}
	if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", 'avi_product' ) ) !== 'avi_product' ) {
		return avi_get_latest_active_stripe_subscription_id( $user_id );
	}
	$sid = $wpdb->get_var(
		$wpdb->prepare(
			"SELECT stripe_subscription_id FROM `{$table}` WHERE user_id = %d AND action_type = %s AND avi_product = %s AND stripe_subscription_id IS NOT NULL AND stripe_subscription_id != '' ORDER BY processed_at DESC LIMIT 1",
			$user_id,
			'activate',
			$avi_product
		)
	);
	return is_string( $sid ) ? trim( $sid ) : '';
}

/**
 * Bouton wholesale Stripe (Price ID en option) ou désactivé.
 *
 * @param string $product        guardian|hebdo|elite.
 * @param int    $tier           1|2|3.
 * @param string $label          Titre court (ex. Guardian).
 * @param string $price_display  Montant affiché (ex. 26€).
 * @param int    $wp_user_id     ID WP agence (réserve extension ; nonce global).
 * @return void
 */
function avi_render_wholesale_checkout_button( string $product, int $tier, string $label, string $price_display, int $wp_user_id ): void {
	unset( $wp_user_id );
	$product = preg_replace( '/[^a-z]/', '', strtolower( $product ) );
	$tier    = in_array( (int) $tier, array( 1, 2, 3 ), true ) ? (int) $tier : 1;
	$key     = function_exists( 'avi_get_wholesale_price_key' ) ? avi_get_wholesale_price_key( $product, $tier ) : '';
	$price_id = ( $key !== '' && function_exists( 'get_option' ) ) ? trim( (string) get_option( $key, '' ) ) : '';
	if ( $price_id === '' || strpos( $price_id, 'price_' ) !== 0 ) {
		echo '<button type="button" class="avi-btn-ws" style="margin-top:8px;padding:8px 12px;border:1px solid #ccc;border-radius:6px;background:#f5f5f5;color:#666;cursor:not-allowed;" disabled>'
			. esc_html( $label )
			. ' — ' . esc_html( $price_display )
			. '<small>' . esc_html__( '/fiche/mois', 'avision-dashboard' ) . '</small>'
			. '</button>';
		return;
	}
	$map = array(
		'guardian' => array( 1 => 'AVI_STRIPE_PRODUCT_WS_GUARDIAN_T1', 2 => 'AVI_STRIPE_PRODUCT_WS_GUARDIAN_T2', 3 => 'AVI_STRIPE_PRODUCT_WS_GUARDIAN_T3' ),
		'hebdo'    => array( 1 => 'AVI_STRIPE_PRODUCT_WS_HEBDO_T1', 2 => 'AVI_STRIPE_PRODUCT_WS_HEBDO_T2', 3 => 'AVI_STRIPE_PRODUCT_WS_HEBDO_T3' ),
		'elite'    => array( 1 => 'AVI_STRIPE_PRODUCT_WS_ELITE_T1', 2 => 'AVI_STRIPE_PRODUCT_WS_ELITE_T2', 3 => 'AVI_STRIPE_PRODUCT_WS_ELITE_T3' ),
	);
	$const_name = isset( $map[ $product ][ $tier ] ) ? $map[ $product ][ $tier ] : '';
	$avi_key    = ( $const_name !== '' && defined( $const_name ) ) ? constant( $const_name ) : '';
	if ( $avi_key === '' ) {
		echo '<button type="button" class="avi-btn-ws" disabled>' . esc_html( $label ) . '</button>';
		return;
	}
	$nonce = function_exists( 'wp_create_nonce' ) ? wp_create_nonce( 'avision_premium_nonce' ) : '';
	$curr  = 'eur';
	echo '<button type="button" class="avi-btn-ws avi-btn-ws-active" style="margin-top:8px;padding:8px 12px;border:1px solid #1a7f37;border-radius:6px;background:#ecfdf5;color:#065f46;cursor:pointer;font-weight:600;"'
		. ' data-avi-product="' . esc_attr( $avi_key ) . '"'
		. ' data-nonce="' . esc_attr( $nonce ) . '"'
		. ' onclick="if(typeof window.aviOpenStripeS10bCheckout===\'function\'){window.aviOpenStripeS10bCheckout(\'' . esc_js( $avi_key ) . '\',\'' . esc_js( $curr ) . '\');}">'
		. esc_html( $label )
		. ' — ' . esc_html( $price_display )
		. '<small>' . esc_html__( '/fiche/mois', 'avision-dashboard' ) . '</small>'
		. '</button>';
}

if ( ! function_exists( 'avi_get_agency_tier' ) ) {
	/**
	 * Tier wholesale agence (1–3) depuis avi_saas_agencies.admin_user_id.
	 *
	 * @param int $agency_user_id ID WP administrateur agence.
	 * @return int
	 */
	function avi_get_agency_tier( int $agency_user_id ): int {
		global $wpdb;
		if ( ! isset( $wpdb ) || ! is_object( $wpdb ) || $agency_user_id <= 0 ) {
			return 1;
		}
		$table = $wpdb->prefix . 'avi_saas_agencies';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- préfixe WP + nom table dérivé.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT tier FROM `{$table}` WHERE admin_user_id = %d LIMIT 1",
				$agency_user_id
			)
		);
		if ( $row && isset( $row->tier ) ) {
			$tier = (int) $row->tier;
			return in_array( $tier, array( 1, 2, 3 ), true ) ? $tier : 1;
		}
		return 1;
	}
}

/**
 * Session Checkout Stripe wholesale multi-fiches (quantity = nombre de profils).
 *
 * @param int    $agency_user_id ID WP agence.
 * @param string $ws_product     Slug wholesale (ws_guardian, ws_hebdo, …).
 * @param array  $profile_ids    IDs numériques profils SaaS.
 * @param string $currency       eur|usd.
 * @return string URL checkout ou vide.
 */
function avi_create_wholesale_bulk_session( int $agency_user_id, string $ws_product, array $profile_ids, string $currency = 'eur' ): string {
	$currency = strtolower( trim( $currency ) );
	if ( ! in_array( $currency, array( 'eur', 'usd' ), true ) ) {
		$currency = 'eur';
	}
	if ( $agency_user_id <= 0 || $ws_product === '' || empty( $profile_ids ) ) {
		return '';
	}

	$allowed = array(
		'ws_guardian',
		'ws_hebdo',
		'ws_elite',
		'ws_guardian_hebdo',
		'ws_guardian_elite',
	);
	if ( ! in_array( $ws_product, $allowed, true ) ) {
		return '';
	}

	$tier = function_exists( 'avi_get_agency_tier' ) ? avi_get_agency_tier( $agency_user_id ) : 1;
	if ( $tier === 0 ) {
		$tier = 1;
	}

	$price_key_map = array(
		'ws_guardian'       => array( 1 => 'avi_stripe_price_ws_guardian_t' . (string) $tier ),
		'ws_hebdo'          => array( 1 => 'avi_stripe_price_ws_hebdo_t' . (string) $tier ),
		'ws_elite'          => array( 1 => 'avi_stripe_price_ws_elite_t' . (string) $tier ),
		'ws_guardian_hebdo' => array( 1 => 'avi_stripe_price_ws_guardian_hebdo_t' . (string) $tier ),
		'ws_guardian_elite' => array( 1 => 'avi_stripe_price_ws_guardian_elite_t' . (string) $tier ),
	);
	$price_key = isset( $price_key_map[ $ws_product ][1] ) ? $price_key_map[ $ws_product ][1] : '';
	if ( $price_key === '' ) {
		return '';
	}
	$price_id = function_exists( 'get_option' ) ? trim( (string) get_option( $price_key, '' ) ) : '';
	if ( $price_id === '' || strpos( $price_id, 'price_' ) !== 0 ) {
		if ( function_exists( 'error_log' ) ) {
			error_log( '[AVI-BULK] Price ID manquant : ' . $price_key );
		}
		return '';
	}

	$secret_key = avi_stripe_s10b_resolve_secret_key();
	if ( $secret_key === '' ) {
		return '';
	}

	$quantity         = count( $profile_ids );
	$profile_ids_json = function_exists( 'wp_json_encode' )
		? wp_json_encode( array_values( $profile_ids ) )
		: json_encode( array_values( $profile_ids ) );

	$success_url = trailingslashit( home_url( '/agency-dashboard/' ) ) . '?avi_bulk_success=1&session_id={CHECKOUT_SESSION_ID}';
	$cancel_url  = trailingslashit( home_url( '/agency-dashboard/' ) ) . '?avi_bulk_cancel=1';

	$u     = function_exists( 'get_userdata' ) ? get_userdata( $agency_user_id ) : null;
	$email = ( $u && function_exists( 'is_email' ) && is_email( $u->user_email ) ) ? $u->user_email : '';

	$body = array(
		'mode'                                       => 'subscription',
		'line_items[0][price]'                       => $price_id,
		'line_items[0][quantity]'                    => (string) $quantity,
		'subscription_data[metadata][agency_user_id]' => (string) $agency_user_id,
		'subscription_data[metadata][ws_product]'     => $ws_product,
		'subscription_data[metadata][profile_ids]'   => $profile_ids_json,
		'subscription_data[metadata][quantity]'      => (string) $quantity,
		'metadata[agency_user_id]'                   => (string) $agency_user_id,
		'metadata[ws_product]'                       => $ws_product,
		'metadata[profile_ids]'                      => $profile_ids_json,
		'success_url'                                => $success_url,
		'cancel_url'                                 => $cancel_url,
	);
	if ( $email !== '' ) {
		$body['customer_email'] = $email;
	}

	if ( function_exists( 'error_log' ) ) {
		error_log( '[AVI-BULK] product=' . $ws_product . ' tier=' . (string) $tier . ' qty=' . (string) $quantity . ' price_key=' . $price_key );
	}

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

	if ( is_wp_error( $response ) ) {
		if ( function_exists( 'error_log' ) ) {
			error_log( '[AVI-BULK] WP_Error : ' . $response->get_error_message() );
		}
		return '';
	}
	$code = (int) wp_remote_retrieve_response_code( $response );
	$raw  = wp_remote_retrieve_body( $response );
	$data = json_decode( $raw, true );
	if ( $code < 200 || $code >= 300 || ! is_array( $data ) ) {
		if ( function_exists( 'error_log' ) ) {
			error_log( '[AVI-BULK] HTTP ' . (string) $code . ' body=' . substr( (string) $raw, 0, 400 ) );
		}
		return '';
	}

	return isset( $data['url'] ) ? (string) $data['url'] : '';
}

/**
 * AJAX : création session Checkout wholesale bulk (agence connectée).
 *
 * @return void
 */
function avi_handle_bulk_wholesale_checkout(): void {
	$nonce = isset( $_POST['nonce'] ) ? sanitize_text_field( wp_unslash( (string) $_POST['nonce'] ) ) : '';
	if ( ! function_exists( 'wp_verify_nonce' ) || ! wp_verify_nonce( $nonce, 'avision_agency_nonce' ) ) {
		wp_send_json_error( array( 'code' => 'bad_nonce' ), 403 );
	}
	$uid = function_exists( 'get_current_user_id' ) ? (int) get_current_user_id() : 0;
	if ( $uid <= 0 ) {
		wp_send_json_error( array( 'code' => 'not_logged_in' ), 401 );
	}
	$ws_product = isset( $_POST['ws_product'] ) ? sanitize_key( wp_unslash( (string) $_POST['ws_product'] ) ) : '';
	$raw_ids    = isset( $_POST['profile_ids'] ) ? wp_unslash( $_POST['profile_ids'] ) : '';
	$profile_ids = array();
	if ( is_array( $raw_ids ) ) {
		foreach ( $raw_ids as $pid ) {
			$clean = (int) $pid;
			if ( $clean > 0 ) {
				$profile_ids[] = $clean;
			}
		}
	}
	$currency = isset( $_POST['currency'] ) ? strtolower( sanitize_text_field( wp_unslash( (string) $_POST['currency'] ) ) ) : 'eur';
	if ( ! in_array( $currency, array( 'eur', 'usd' ), true ) ) {
		$currency = 'eur';
	}
	if ( $ws_product === '' || empty( $profile_ids ) ) {
		wp_send_json_error( array( 'code' => 'invalid_params' ), 400 );
	}
	$url = avi_create_wholesale_bulk_session( $uid, $ws_product, $profile_ids, $currency );
	if ( $url === '' ) {
		wp_send_json_error(
			array(
				'code'    => 'session_failed',
				'message' => 'Bulk session unavailable',
			),
			500
		);
	}
	wp_send_json_success( array( 'url' => $url ) );
}

add_action( 'wp_ajax_avision_bulk_wholesale_checkout', 'avi_handle_bulk_wholesale_checkout' );

/**
 * Session Checkout Stripe one-time — upgrade palier agence (prorata T1→T2, T1→T3, T2→T3).
 *
 * @param int    $user_id     Utilisateur WP agence.
 * @param string $from_tier   agency_tier1|agency_tier2|agency_tier3.
 * @param string $target_tier agency_tier1|agency_tier2|agency_tier3.
 * @param string $currency    usd|eur.
 * @return array{url:string,error:string}
 */
function avi_agency_create_tier_upgrade_session( int $user_id, string $from_tier, string $target_tier, string $currency = 'usd' ): array {
	$allowed = array( 'agency_tier1', 'agency_tier2', 'agency_tier3' );
	$currency = strtolower( $currency );
	if ( ! in_array( $currency, array( 'usd', 'eur' ), true ) ) {
		$currency = 'usd';
	}
	if ( $user_id <= 0 || ! in_array( $from_tier, $allowed, true ) || ! in_array( $target_tier, $allowed, true ) ) {
		return array(
			'url'   => '',
			'error' => 'Invalid tier',
		);
	}

	$prices = array(
		'agency_tier1' => 149,
		'agency_tier2' => 299,
		'agency_tier3' => 499,
	);
	$amount = ( (int) $prices[ $target_tier ] - (int) $prices[ $from_tier ] ) * 100;
	if ( $amount <= 0 ) {
		return array(
			'url'   => '',
			'error' => 'Invalid upgrade amount',
		);
	}

	$secret_key = avi_stripe_s10b_resolve_secret_key();
	if ( $secret_key === '' ) {
		return array(
			'url'   => '',
			'error' => 'Stripe not configured',
		);
	}

	$product_name = 'Agency Upgrade to ' . strtoupper( $target_tier );
	$success_url  = trailingslashit( home_url( '/agency-dashboard/' ) ) . '?upgrade_success=1';
	$cancel_url   = trailingslashit( home_url( '/agency-dashboard/' ) );

	try {
		if ( class_exists( '\Stripe\Stripe' ) && class_exists( '\Stripe\Checkout\Session' ) ) {
			\Stripe\Stripe::setApiKey( $secret_key );
			$session = \Stripe\Checkout\Session::create(
				array(
					'mode'        => 'payment',
					'line_items'  => array(
						array(
							'price_data' => array(
								'currency'     => $currency,
								'unit_amount'  => $amount,
								'product_data' => array(
									'name' => $product_name,
								),
							),
							'quantity'   => 1,
						),
					),
					'success_url' => $success_url,
					'cancel_url'  => $cancel_url,
					'metadata'    => array(
						'avi_product'  => 'agency_tier_upgrade',
						'target_tier'  => $target_tier,
						'from_tier'    => $from_tier,
						'user_id'      => (string) $user_id,
						'user_type'    => 'B2B',
					),
				)
			);
			$url = ( is_object( $session ) && isset( $session->url ) ) ? (string) $session->url : '';
			if ( $url === '' ) {
				return array(
					'url'   => '',
					'error' => 'Empty checkout URL',
				);
			}
			return array(
				'url'   => $url,
				'error' => '',
			);
		}

		$body = array(
			'mode'                                           => 'payment',
			'line_items[0][price_data][currency]'            => $currency,
			'line_items[0][price_data][unit_amount]'         => (string) $amount,
			'line_items[0][price_data][product_data][name]'  => $product_name,
			'line_items[0][quantity]'                        => '1',
			'success_url'                                    => $success_url,
			'cancel_url'                                     => $cancel_url,
			'metadata[avi_product]'                        => 'agency_tier_upgrade',
			'metadata[target_tier]'                          => $target_tier,
			'metadata[from_tier]'                            => $from_tier,
			'metadata[user_id]'                              => (string) $user_id,
			'metadata[user_type]'                            => 'B2B',
		);

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

		if ( is_wp_error( $response ) ) {
			return array(
				'url'   => '',
				'error' => $response->get_error_message(),
			);
		}

		$code = (int) wp_remote_retrieve_response_code( $response );
		$raw  = wp_remote_retrieve_body( $response );
		$data = json_decode( $raw, true );
		if ( $code < 200 || $code >= 300 || ! is_array( $data ) ) {
			$err_msg = is_array( $data ) && isset( $data['error']['message'] ) ? (string) $data['error']['message'] : 'Checkout session HTTP ' . $code;
			return array(
				'url'   => '',
				'error' => $err_msg,
			);
		}

		$url = isset( $data['url'] ) ? (string) $data['url'] : '';
		if ( $url === '' ) {
			return array(
				'url'   => '',
				'error' => 'Empty checkout URL',
			);
		}

		return array(
			'url'   => $url,
			'error' => '',
		);
	} catch ( \Exception $e ) {
		return array(
			'url'   => '',
			'error' => $e->getMessage(),
		);
	}
}

add_action( 'plugins_loaded', 'avi_s10b_schema_migrate', 5 );
add_action( 'avi_level_validated', 'avi_maybe_unlock_guardian', 10, 2 );
