<?php
/**
 * Admin — demandes programme Nonprofit (approbation / rejet).
 *
 * @package Avision
 */

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

/**
 * Sous-menu AVI Control Center.
 */
class Avision_Nonprofit_Admin {

	const PAGE_SLUG = 'avi-nonprofits';

	/**
	 * @return void
	 */
	public static function init() {
		add_action( 'admin_menu', array( __CLASS__, 'register_menu' ), 30 );
		add_action( 'wp_ajax_avision_nonprofit_approve', array( __CLASS__, 'handle_approve' ) );
		add_action( 'wp_ajax_avision_nonprofit_reject', array( __CLASS__, 'handle_reject' ) );
		add_action( 'admin_init', array( __CLASS__, 'ensure_litespeed_nonprofits_cache_exc' ) );
		add_action( 'admin_init', array( __CLASS__, 'ensure_nonprofit_columns' ) );
		add_action( 'rest_api_init', array( __CLASS__, 'register_rest_routes' ) );
		add_filter( 'rest_post_dispatch', array( __CLASS__, 'intercept_first_sync_response' ), 10, 3 );
		add_action( 'template_redirect', array( __CLASS__, 'handle_referral_cookie' ), 1 );
		add_filter( 'avi_stripe_s10b_checkout_metadata_report_id', array( __CLASS__, 'inject_np_ref_in_metadata' ), 10, 3 );
		add_filter( 'cron_schedules', array( __CLASS__, 'add_monthly_cron_schedule' ) );
		if ( ! wp_next_scheduled( 'avi_np_monthly_payouts' ) ) {
			wp_schedule_event(
				strtotime( 'first day of next month 09:00:00' ),
				'monthly',
				'avi_np_monthly_payouts'
			);
		}
		add_action( 'avi_np_monthly_payouts', array( __CLASS__, 'process_monthly_payouts' ) );
		// Cron vérification preuves partage expirées — 1x/mois, le 2 du mois à 10h UTC
		if ( ! wp_next_scheduled( 'avi_np_check_share_proofs' ) ) {
			wp_schedule_event(
				strtotime( 'next month 10:00:00' ),
				'monthly',
				'avi_np_check_share_proofs'
			);
		}
		add_action( 'avi_np_check_share_proofs', array( __CLASS__, 'process_check_share_proofs' ) );
		add_action( 'wp_ajax_avision_np_save_payout', array( __CLASS__, 'handle_save_payout' ) );
		add_action( 'wp_ajax_avision_np_suspend', array( __CLASS__, 'handle_suspend' ) );
		add_action( 'wp_ajax_avision_np_get_detail', array( __CLASS__, 'handle_get_detail' ) );
		add_action( 'wp_ajax_avision_np_login_as', array( __CLASS__, 'handle_login_as' ) );
		add_action( 'wp_ajax_avision_np_validate_share', array( __CLASS__, 'handle_validate_share' ) );
		add_action( 'wp_ajax_avision_np_send_inactivity_warning', array( __CLASS__, 'handle_send_inactivity_warning' ) );
		add_action( 'wp_ajax_avision_np_reset_coupon', array( __CLASS__, 'handle_reset_coupon' ) );
		add_action( 'wp_ajax_avision_np_restore_coupon', array( __CLASS__, 'handle_restore_coupon' ) );
		add_action( 'wp_ajax_avision_np_set_coupon', array( __CLASS__, 'handle_set_coupon' ) );
		add_action( 'wp_ajax_avision_np_reset_gbp', array( __CLASS__, 'handle_reset_gbp' ) );
		add_action( 'wp_ajax_avision_np_reset_oauth', array( __CLASS__, 'handle_reset_oauth' ) );
		// Cron hebdomadaire emails inactivité auto
		if ( ! wp_next_scheduled( 'avi_np_weekly_inactivity_check' ) ) {
			wp_schedule_event(
				strtotime( 'next monday 09:00:00' ),
				'weekly',
				'avi_np_weekly_inactivity_check'
			);
		}
		add_action( 'avi_np_weekly_inactivity_check', array( __CLASS__, 'process_weekly_inactivity_check' ) );
		add_action( 'init', array( __CLASS__, 'maybe_switch_to_user' ), 1 );
		add_action( 'init', array( __CLASS__, 'maybe_switch_back' ), 1 );
	}

	/**
	 * Exclut /nonprofits/ du cache LiteSpeed (formulaire AJAX dynamique).
	 *
	 * @return void
	 */
	public static function ensure_litespeed_nonprofits_cache_exc() {
		$exc = get_option( 'litespeed.conf.cache-exc', array() );
		if ( is_string( $exc ) ) {
			$exc = json_decode( $exc, true );
		}
		if ( ! is_array( $exc ) ) {
			$exc = array();
		}
		if ( ! in_array( '/nonprofits/', $exc, true ) ) {
			$exc[] = '/nonprofits/';
			update_option( 'litespeed.conf.cache-exc', json_encode( $exc ) );
		}
	}

	/**
	 * Ajoute les colonnes nonprofit / table payouts si absentes.
	 *
	 * @return void
	 */
	public static function ensure_nonprofit_columns() {
		global $wpdb;
		$table = $wpdb->prefix . 'avi_nonprofits';

		$columns = array(
			'ambassadrice_accepted_at' => 'DATETIME NULL DEFAULT NULL',
			'ambassadrice_status'      => "VARCHAR(20) NOT NULL DEFAULT 'active'",
			'payout_method'            => "VARCHAR(20) NOT NULL DEFAULT 'stripe'",
			'payout_details'           => 'TEXT NULL DEFAULT NULL',
			'stripe_customer_id'       => "VARCHAR(100) NOT NULL DEFAULT ''",
			'current_coupon_percent'   => 'INT NOT NULL DEFAULT 50',
			'warning_sent_at'          => 'DATETIME NULL DEFAULT NULL',
			'last_referral_at'         => 'DATETIME NULL DEFAULT NULL',
		);

		foreach ( $columns as $col => $definition ) {
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
					$table,
					$col
				)
			);
			if ( ! $exists ) {
				$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$col}` {$definition}" );
			}
		}

		$payouts_table = $wpdb->prefix . 'avi_nonprofit_payouts';
		$exists_table  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $payouts_table ) );
		if ( ! $exists_table ) {
			$wpdb->query(
				"CREATE TABLE `{$payouts_table}` (
					id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
					nonprofit_id BIGINT UNSIGNED NOT NULL,
					period_month VARCHAR(7) NOT NULL DEFAULT '',
					total_referral_revenue DECIMAL(10,2) NOT NULL DEFAULT 0.00,
					donation_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
					payout_method VARCHAR(20) NOT NULL DEFAULT 'stripe',
					payout_status VARCHAR(20) NOT NULL DEFAULT 'pending',
					payout_date DATETIME NULL,
					stripe_transfer_id VARCHAR(255) NOT NULL DEFAULT '',
					created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
					PRIMARY KEY (id),
					KEY nonprofit_id (nonprofit_id),
					KEY period_month (period_month),
					KEY payout_status (payout_status)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
			);
		}
	}

	/**
	 * Routes REST dédiées au programme nonprofit.
	 *
	 * @return void
	 */
	public static function register_rest_routes() {
		register_rest_route(
			'avision/v1',
			'/nonprofit-register',
			array(
				'methods'             => 'POST',
				'callback'            => array( __CLASS__, 'rest_nonprofit_register' ),
				'permission_callback' => '__return_true',
			)
		);
	}

	/**
	 * Crée compte WP + profil SaaS pour une candidature nonprofit.
	 *
	 * @param WP_REST_Request $request Requête REST.
	 * @return WP_REST_Response
	 */
	public static function rest_nonprofit_register( $request ) {
		$email            = sanitize_email( (string) $request->get_param( 'email' ) );
		$org_name         = sanitize_text_field( (string) $request->get_param( 'org_name' ) );
		$org_number       = sanitize_text_field( (string) $request->get_param( 'org_number' ) );
		$country          = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$city             = sanitize_text_field( (string) $request->get_param( 'city' ) );
		$neighborhood     = sanitize_text_field( (string) $request->get_param( 'neighborhood' ) );
		$gbp_name         = sanitize_text_field( (string) $request->get_param( 'gbp_name' ) );
		$website          = esc_url_raw( (string) $request->get_param( 'website' ) );
		$password         = (string) $request->get_param( 'password' );
		$password_confirm = (string) $request->get_param( 'password_confirm' );
		$lang             = sanitize_text_field( (string) $request->get_param( 'language' ) );
		$np_id            = (int) $request->get_param( 'np_id' );

		if ( $lang === '' ) {
			$lang = 'en';
		}

		if ( ! $email || ! is_email( $email ) ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'invalid_email',
				),
				400
			);
		}
		if ( ! $org_name || ! $password || ! $city || ! $neighborhood || ! $gbp_name || ! $country ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'missing_fields',
				),
				400
			);
		}
		if ( strlen( $password ) < 8 ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'password_too_short',
				),
				400
			);
		}
		if ( $password !== $password_confirm ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'password_mismatch',
				),
				400
			);
		}

		if ( email_exists( $email ) ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'email_exists',
					'message' => 'An account already exists with this email.',
				),
				409
			);
		}

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

		$wp_user_id = wp_create_user( $username, $password, $email );
		if ( is_wp_error( $wp_user_id ) ) {
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => $wp_user_id->get_error_message(),
				),
				500
			);
		}

		$wp_user = get_userdata( $wp_user_id );
		if ( $wp_user instanceof WP_User ) {
			$wp_user->set_role( 'subscriber' );
		}

		global $wpdb;
		$profiles_table = $wpdb->prefix . 'avi_saas_profiles';
		$uuid           = function_exists( 'wp_generate_uuid4' ) ? wp_generate_uuid4() : wp_generate_password( 36, false );

		$profile_data = array(
			'user_id'       => $wp_user_id,
			'email'         => $email,
			'business_name' => $org_name,
			'city'          => $city,
			'neighborhood'  => $neighborhood,
			'category'      => $gbp_name,
			'language'      => $lang,
			'uuid'          => $uuid,
			'current_level' => 1,
			'created_at'    => current_time( 'mysql' ),
			'updated_at'    => current_time( 'mysql' ),
		);

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

		$insert_data   = array();
		$insert_format = array();
		foreach ( $profile_data as $col => $val ) {
			if ( in_array( $col, $existing_cols, true ) ) {
				$insert_data[ $col ] = $val;
				$insert_format[]     = is_int( $val ) ? '%d' : '%s';
			}
		}

		$profile_inserted = $wpdb->insert( $profiles_table, $insert_data, $insert_format );

		if ( ! $profile_inserted ) {
			wp_delete_user( $wp_user_id );
			return new WP_REST_Response(
				array(
					'success' => false,
					'error'   => 'profile_insert_failed',
					'sql'     => $wpdb->last_error,
				),
				500
			);
		}

		$profile_id = (int) $wpdb->insert_id;

		$wpdb->update(
			$profiles_table,
			array( 'category' => 'nonprofit_community' ),
			array( 'id' => $profile_id ),
			array( '%s' ),
			array( '%d' )
		);

		if ( $np_id > 0 ) {
			$wpdb->update(
				$wpdb->prefix . 'avi_nonprofits',
				array(
					'user_id'    => $wp_user_id,
					'profile_id' => $profile_id,
				),
				array( 'id' => $np_id ),
				array( '%d', '%d' ),
				array( '%d' )
			);
			update_user_meta( $wp_user_id, 'avi_np_id', $np_id );
		}

		if ( $website ) {
			update_user_meta( $wp_user_id, 'avi_np_website', $website );
		}
		if ( $org_number ) {
			update_user_meta( $wp_user_id, 'avi_np_org_number', $org_number );
		}

		// Envoyer email selon statut nonprofit.
		if ( $np_id > 0 ) {
			$np_status_check = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT verification_status FROM {$wpdb->prefix}avi_nonprofits WHERE id = %d LIMIT 1",
					$np_id
				)
			);

			$lang_for_email = sanitize_text_field( (string) $request->get_param( 'language' ) );
			if ( $lang_for_email === '' ) {
				$lang_for_email = 'en';
			}
			if ( ! in_array( $lang_for_email, array( 'en', 'fr', 'es' ), true ) ) {
				$lang_for_email = 'en';
			}

			update_option( 'avi_np_applicant_email_' . $np_id, $email, false );
			update_option( 'avi_np_applicant_lang_' . $np_id, $lang_for_email, false );

			if ( $np_status_check === 'verified' ) {
				if ( function_exists( 'avi_nonprofit_send_approval_email' ) ) {
					avi_nonprofit_send_approval_email( $np_id );
				}
			} elseif ( in_array( $np_status_check, array( 'manual', 'pending', 'category_verified' ), true ) ) {
				$reset_url     = home_url( '/wp-login.php?action=lostpassword' );
				$dashboard_url = home_url( '/premium-dashboard/' );
				if ( $profile_id > 0 ) {
					$profile_uuid_mail = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT uuid FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
							$profile_id
						)
					);
					if ( $profile_uuid_mail ) {
						$dashboard_url = add_query_arg( 'pid', $profile_uuid_mail, $dashboard_url );
					}
				}

				$subjects = array(
					'en' => 'Your Nonprofit Account — Ready to Access',
					'fr' => 'Votre compte association — Prêt à accéder',
					'es' => 'Tu cuenta ONG — Lista para acceder',
				);
				$bodies = array(
					'en' => "Hello,\n\nYour account has been created successfully.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nYOUR ACCOUNT ACCESS\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard: {dashboard_url}\nLogin: {email}\nPassword: the one you chose during registration\n\nForgot your password?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nVERIFICATION IN PROGRESS\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nWe are verifying your nonprofit registration number.\nOnce verified (usually within 48 hours), you will receive a second email with your 50% discount code for premium options.\n\nThe Avision Team\ncontact@avision.org",
					'fr' => "Bonjour,\n\nVotre compte a été créé avec succès.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nACCÈS À VOTRE COMPTE\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard : {dashboard_url}\nIdentifiant : {email}\nMot de passe : celui que vous avez choisi lors de votre inscription\n\nMot de passe oublié ?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nVÉRIFICATION EN COURS\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nNous vérifions votre numéro d'enregistrement.\nUne fois vérifié (généralement sous 48 heures), vous recevrez un second email avec votre code de réduction de 50% sur les options premium.\n\nL'équipe Avision\ncontact@avision.org",
					'es' => "Hola,\n\nTu cuenta ha sido creada con éxito.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nACCESO A TU CUENTA\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard: {dashboard_url}\nUsuario: {email}\nContraseña: la que elegiste al registrarte\n\n¿Olvidaste tu contraseña?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nVERIFICACIÓN EN CURSO\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nEstamos verificando tu número de registro.\nUna vez verificado (normalmente en menos de 48 horas), recibirás un segundo email con tu código de descuento del 50% en opciones premium.\n\nEl equipo de Avision\ncontact@avision.org",
				);

				$subj = isset( $subjects[ $lang_for_email ] ) ? $subjects[ $lang_for_email ] : $subjects['en'];
				$body = isset( $bodies[ $lang_for_email ] ) ? $bodies[ $lang_for_email ] : $bodies['en'];
				$body = str_replace(
					array( '{dashboard_url}', '{email}', '{reset_url}' ),
					array( $dashboard_url, $email, $reset_url ),
					$body
				);

				wp_mail(
					$email,
					$subj,
					$body,
					array( 'From: Avision <no-reply@avision.org>' )
				);
			}
		}

		wp_set_current_user( $wp_user_id );
		wp_set_auth_cookie( $wp_user_id, true );

		$redirect = home_url( '/premium-dashboard/' );

		return new WP_REST_Response(
			array(
				'success'    => true,
				'user_id'    => $wp_user_id,
				'profile_id' => $profile_id,
				'redirect'   => $redirect,
			),
			200
		);
	}

	/**
	 * @return void
	 */
	public static function register_menu() {
		if ( ! class_exists( 'Avision_Control_Center' ) ) {
			return;
		}
		add_submenu_page(
			Avision_Control_Center::PAGE_SLUG,
			'Nonprofit Applications',
			'🤝 Nonprofits',
			'manage_options',
			self::PAGE_SLUG,
			array( __CLASS__, 'render_page' )
		);
	}

	/**
	 * @return string
	 */
	private static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'avi_nonprofits';
	}

	/**
	 * @param string $status Statut brut.
	 * @return string
	 */
	private static function status_label( $status ) {
		$map = array(
			'pending'           => 'Pending',
			'manual'            => 'Manual',
			'category_mismatch' => 'Category Mismatch',
			'verified'          => 'Verified',
			'rejected'          => 'Rejected',
			'awaiting_gbp'      => 'Awaiting GBP',
		);
		return isset( $map[ $status ] ) ? $map[ $status ] : ucfirst( (string) $status );
	}

	/**
	 * @return void
	 */
	public static function render_page() {
		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}

		global $wpdb;
		$table        = self::table_name();
		$ref_table    = $wpdb->prefix . 'avi_nonprofit_referrals';
		$payout_table = $wpdb->prefix . 'avi_nonprofit_payouts';
		$filter       = isset( $_GET['np_filter'] )
			? sanitize_key( (string) wp_unslash( $_GET['np_filter'] ) )
			: 'all';
		$search       = isset( $_GET['np_search'] )
			? sanitize_text_field( (string) wp_unslash( $_GET['np_search'] ) )
			: '';
		$allowed_filters = array(
			'all',
			'pending',
			'manual',
			'category_mismatch',
			'verified',
			'rejected',
			'suspended',
		);
		if ( ! in_array( $filter, $allowed_filters, true ) ) {
			$filter = 'all';
		}

		$base_url = admin_url( 'admin.php?page=' . self::PAGE_SLUG );

		$stats = array(
			'total'               => 0,
			'verified'            => 0,
			'pending'             => 0,
			'referrals_month'     => 0,
			'donations_pending'   => 0.00,
			'donations_paid'      => 0.00,
		);

		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$stats['total'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
			$stats['verified'] = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM `{$table}` WHERE verification_status = %s",
					'verified'
				)
			);
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$stats['pending'] = (int) $wpdb->get_var(
				"SELECT COUNT(*) FROM `{$table}` WHERE verification_status IN ('pending','manual','category_mismatch')"
			);
		}

		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $ref_table ) ) === $ref_table ) {
			$stats['referrals_month'] = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM `{$ref_table}` WHERE DATE_FORMAT(created_at,'%%Y-%%m') = %s",
					date( 'Y-m' )
				)
			);
		}

		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $payout_table ) ) === $payout_table ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$stats['donations_pending'] = (float) $wpdb->get_var(
				"SELECT COALESCE(SUM(donation_amount),0) FROM `{$payout_table}` WHERE payout_status = 'pending'"
			);
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$stats['donations_paid'] = (float) $wpdb->get_var(
				"SELECT COALESCE(SUM(donation_amount),0) FROM `{$payout_table}` WHERE payout_status = 'paid'"
			);
		}

		$rows = array();
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table ) {
			$where_parts = array();
			$where_vals  = array();

			if ( $filter !== 'all' ) {
				if ( $filter === 'suspended' ) {
					$where_parts[] = 'ambassadrice_status = %s';
					$where_vals[]  = 'suspended';
				} else {
					$where_parts[] = 'verification_status = %s';
					$where_vals[]  = $filter;
				}
			}
			if ( $search !== '' ) {
				$where_parts[] = '(org_name LIKE %s OR org_number LIKE %s OR country_code LIKE %s)';
				$like          = '%' . $wpdb->esc_like( $search ) . '%';
				$where_vals[]  = $like;
				$where_vals[]  = $like;
				$where_vals[]  = $like;
			}

			$where_sql = empty( $where_parts )
				? ''
				: 'WHERE ' . implode( ' AND ', $where_parts );

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				( empty( $where_vals )
					? "SELECT n.*,
						(SELECT COUNT(*) FROM `{$ref_table}` r
						 WHERE r.nonprofit_id = n.id)
						 AS referral_count_live,
						(SELECT COALESCE(SUM(r2.donation_amount),0)
						 FROM `{$ref_table}` r2
						 WHERE r2.nonprofit_id = n.id)
						 AS total_donations_live,
						p.current_level AS profile_level,
						p.guardian_active AS profile_guardian,
						u.user_registered AS user_registered,
						u.user_email AS user_email
					   FROM `{$table}` n
					   LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
						 ON p.id = n.profile_id
					   LEFT JOIN {$wpdb->users} u
						 ON u.ID = n.user_id
					   ORDER BY n.created_at DESC LIMIT 500"
					: $wpdb->prepare(
						"SELECT n.*,
						  (SELECT COUNT(*) FROM `{$ref_table}` r
						   WHERE r.nonprofit_id = n.id)
						   AS referral_count_live,
						  (SELECT COALESCE(SUM(r2.donation_amount),0)
						   FROM `{$ref_table}` r2
						   WHERE r2.nonprofit_id = n.id)
						   AS total_donations_live,
						  p.current_level AS profile_level,
						  p.guardian_active AS profile_guardian,
						  u.user_registered AS user_registered,
						  u.user_email AS user_email
						 FROM `{$table}` n
						 LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
						   ON p.id = n.profile_id
						 LEFT JOIN {$wpdb->users} u
						   ON u.ID = n.user_id
						 {$where_sql}
						 ORDER BY n.created_at DESC LIMIT 500",
						...$where_vals
					)
				),
				ARRAY_A
			);
		}
		if ( ! is_array( $rows ) ) {
			$rows = array();
		}

		// Helper inactivité (local à render_page)
		$get_inactivity = function( $row ) {
			$dates = array();
			if ( ! empty( $row['last_activity_at'] ) ) {
				$dates[] = strtotime( $row['last_activity_at'] );
			}
			if ( ! empty( $row['last_referral_at'] ) ) {
				$dates[] = strtotime( $row['last_referral_at'] );
			}
			if ( ! empty( $row['last_share_proof_at'] ) ) {
				$dates[] = strtotime( $row['last_share_proof_at'] );
			}
			if ( ! empty( $row['user_registered'] ) ) {
				$dates[] = strtotime( $row['user_registered'] );
			}
			if ( empty( $dates ) ) {
				return array(
					'days'  => 999,
					'label' => 'Jamais connecté',
					'color' => '#ef4444',
				);
			}
			$last = max( $dates );
			$days = (int) floor( ( time() - $last ) / 86400 );
			if ( $days <= 30 ) {
				return array(
					'days'  => $days,
					'label' => $days === 0 ? "Aujourd'hui" : "il y a {$days}j",
					'color' => '#34d399',
				);
			} elseif ( $days <= 90 ) {
				return array(
					'days'  => $days,
					'label' => "il y a {$days}j",
					'color' => '#fbbf24',
				);
			} else {
				return array(
					'days'  => $days,
					'label' => "inactif {$days}j",
					'color' => '#ef4444',
				);
			}
		};

		// Helper badge niveau
		$get_level_badge = function( $level ) {
			$level = (int) $level;
			if ( $level <= 0 ) {
				return '<span style="background:rgba(100,116,139,0.15);'
					. 'color:#64748b;padding:2px 7px;'
					. 'border-radius:10px;font-size:10px;'
					. 'font-weight:700;">LVL —</span>';
			}
			if ( $level <= 3 ) {
				$color = '#94a3b8';
				$bg    = 'rgba(148,163,184,0.12)';
			} elseif ( $level <= 7 ) {
				$color = '#60a5fa';
				$bg    = 'rgba(96,165,250,0.12)';
			} elseif ( $level <= 14 ) {
				$color = '#34d399';
				$bg    = 'rgba(52,211,153,0.12)';
			} else {
				$color = '#DAA520';
				$bg    = 'rgba(218,165,32,0.12)';
			}
			return '<span style="background:' . $bg . ';'
				. 'color:' . $color . ';padding:2px 7px;'
				. 'border-radius:10px;font-size:10px;'
				. 'font-weight:700;">LVL ' . $level . '</span>';
		};

		$filters = array(
			'all'               => 'Toutes',
			'pending'           => 'En attente',
			'manual'            => 'Manuel',
			'category_mismatch' => 'Catégorie incorrecte',
			'verified'          => 'Vérifiées',
			'rejected'          => 'Rejetées',
			'suspended'         => 'Suspendues',
		);

		$counts = array();
		foreach ( $filters as $key => $label ) {
			if ( $key === 'all' ) {
				$counts['all'] = count( $rows );
				continue;
			}
			$counts[ $key ] = 0;
			foreach ( $rows as $r ) {
				if ( $key === 'suspended' ) {
					if ( isset( $r['ambassadrice_status'] ) && $r['ambassadrice_status'] === 'suspended' ) {
						$counts[ $key ]++;
					}
				} elseif ( isset( $r['verification_status'] ) && $r['verification_status'] === $key ) {
					$counts[ $key ]++;
				}
			}
		}
		?>
		<style>
		#avi-np-admin {
			font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
		}
		#avi-np-admin .avi-np-header {
			background: linear-gradient(135deg, #0f172a 0%, #1e293b 100%);
			border-radius: 12px;
			padding: 24px 28px;
			margin: 16px 0 24px;
			border: 1px solid rgba(218,165,32,0.2);
		}
		#avi-np-admin .avi-np-header h1 {
			color: #DAA520;
			font-size: 22px;
			margin: 0 0 4px;
			font-weight: 700;
		}
		#avi-np-admin .avi-np-header p {
			color: #94a3b8;
			margin: 0;
			font-size: 13px;
		}
		#avi-np-admin .avi-np-stats {
			display: grid;
			grid-template-columns: repeat(4, 1fr);
			gap: 12px;
			margin-bottom: 20px;
		}
		@media (max-width: 1200px) {
			#avi-np-admin .avi-np-stats {
				grid-template-columns: repeat(2, 1fr);
			}
		}
		#avi-np-admin .avi-np-stat-card {
			background: #1e293b;
			border: 1px solid rgba(255,255,255,0.06);
			border-radius: 10px;
			padding: 16px 20px;
		}
		#avi-np-admin .avi-np-stat-label {
			font-size: 11px;
			text-transform: uppercase;
			letter-spacing: 0.08em;
			color: #64748b;
			margin-bottom: 6px;
			font-weight: 600;
		}
		#avi-np-admin .avi-np-stat-value {
			font-size: 28px;
			font-weight: 800;
			color: #DAA520;
			line-height: 1;
		}
		#avi-np-admin .avi-np-stat-sub {
			font-size: 11px;
			color: #475569;
			margin-top: 4px;
		}
		#avi-np-admin .avi-np-toolbar {
			display: flex;
			align-items: center;
			gap: 12px;
			margin-bottom: 16px;
			flex-wrap: wrap;
		}
		#avi-np-admin .avi-np-search {
			flex: 1;
			min-width: 200px;
			padding: 8px 14px;
			border: 1px solid #334155;
			border-radius: 8px;
			background: #1e293b;
			color: #e2e8f0;
			font-size: 13px;
		}
		#avi-np-admin .avi-np-filters {
			display: flex;
			gap: 6px;
			flex-wrap: wrap;
		}
		#avi-np-admin .avi-np-filter-btn {
			padding: 6px 14px;
			border-radius: 20px;
			font-size: 12px;
			font-weight: 600;
			text-decoration: none;
			border: 1px solid rgba(255,255,255,0.1);
			color: #94a3b8;
			background: #1e293b;
			display: inline-flex;
			align-items: center;
			gap: 5px;
		}
		#avi-np-admin .avi-np-filter-btn.is-active {
			background: #DAA520;
			color: #0f172a;
			border-color: #DAA520;
		}
		#avi-np-admin .avi-np-filter-count {
			background: rgba(0,0,0,0.2);
			border-radius: 10px;
			padding: 1px 6px;
			font-size: 10px;
		}
		#avi-np-admin .is-active .avi-np-filter-count {
			background: rgba(0,0,0,0.15);
		}
		#avi-np-admin .avi-np-table-wrap {
			background: #1e293b;
			border-radius: 12px;
			border: 1px solid rgba(255,255,255,0.06);
			overflow: hidden;
		}
		#avi-np-admin table {
			width: 100%;
			border-collapse: collapse;
			font-size: 13px;
		}
		#avi-np-admin table th {
			background: #0f172a;
			color: #64748b;
			font-size: 11px;
			text-transform: uppercase;
			letter-spacing: 0.06em;
			padding: 12px 14px;
			text-align: left;
			font-weight: 600;
			border-bottom: 1px solid rgba(255,255,255,0.05);
		}
		#avi-np-admin table td {
			padding: 12px 14px;
			color: #cbd5e1;
			border-bottom: 1px solid rgba(255,255,255,0.04);
			vertical-align: middle;
		}
		#avi-np-admin table tr:last-child td {
			border-bottom: none;
		}
		#avi-np-admin table tr:hover td {
			background: rgba(255,255,255,0.02);
		}
		#avi-np-admin .avi-np-badge {
			display: inline-block;
			padding: 3px 10px;
			border-radius: 20px;
			font-size: 11px;
			font-weight: 700;
			text-transform: uppercase;
			letter-spacing: 0.04em;
		}
		#avi-np-admin .badge-pending { background: rgba(251,191,36,0.12); color: #fbbf24; }
		#avi-np-admin .badge-manual { background: rgba(59,130,246,0.12); color: #60a5fa; }
		#avi-np-admin .badge-category_mismatch { background: rgba(167,139,250,0.12); color: #a78bfa; }
		#avi-np-admin .badge-verified { background: rgba(52,211,153,0.12); color: #34d399; }
		#avi-np-admin .badge-category_verified { background: rgba(52,211,153,0.08); color: #6ee7b7; }
		#avi-np-admin .badge-rejected { background: rgba(248,113,113,0.12); color: #f87171; }
		#avi-np-admin .badge-suspended { background: rgba(100,116,139,0.12); color: #94a3b8; }
		#avi-np-admin .avi-np-actions {
			display: flex;
			gap: 5px;
			flex-wrap: wrap;
			align-items: center;
		}
		#avi-np-admin .avi-np-btn {
			padding: 5px 12px;
			border-radius: 6px;
			font-size: 12px;
			font-weight: 600;
			cursor: pointer;
			border: none;
			transition: opacity 0.2s;
		}
		#avi-np-admin .avi-np-btn:hover { opacity: 0.85; }
		#avi-np-admin .btn-approve { background: #059669; color: #fff; }
		#avi-np-admin .btn-reject { background: #dc2626; color: #fff; }
		#avi-np-admin .btn-suspend { background: #64748b; color: #fff; }
		#avi-np-admin .btn-restore { background: #0284c7; color: #fff; }
		#avi-np-admin .btn-detail {
			background: rgba(218,165,32,0.15);
			color: #DAA520;
			border: 1px solid rgba(218,165,32,0.3);
		}
		#avi-np-admin .avi-np-reject-box {
			display: none;
			margin-top: 8px;
			padding: 12px;
			background: rgba(220,38,38,0.06);
			border: 1px solid rgba(220,38,38,0.2);
			border-radius: 8px;
			min-width: 320px;
		}
		#avi-np-admin .avi-np-reject-box.is-open { display: block; }
		#avi-np-admin .avi-np-reject-box textarea {
			width: 100%;
			background: #0f172a;
			border: 1px solid #334155;
			border-radius: 6px;
			color: #e2e8f0;
			padding: 8px;
			font-size: 12px;
			margin: 6px 0;
			resize: vertical;
		}
		#avi-np-admin .avi-np-reject-box label {
			color: #94a3b8;
			font-size: 12px;
			display: flex;
			align-items: center;
			gap: 6px;
			margin-bottom: 8px;
		}
		#avi-np-modal-overlay {
			display: none;
			position: fixed;
			inset: 0;
			background: rgba(0,0,0,0.7);
			z-index: 99999;
			align-items: center;
			justify-content: center;
		}
		#avi-np-modal-overlay.is-open { display: flex; }
		#avi-np-modal {
			background: #1e293b;
			border: 1px solid rgba(218,165,32,0.2);
			border-radius: 16px;
			padding: 28px 32px;
			max-width: 640px;
			width: 90%;
			max-height: 80vh;
			overflow-y: auto;
			position: relative;
		}
		#avi-np-modal h2 {
			color: #DAA520;
			font-size: 18px;
			margin: 0 0 20px;
		}
		#avi-np-modal .avi-np-modal-close {
			position: absolute;
			top: 16px;
			right: 16px;
			background: rgba(255,255,255,0.06);
			border: none;
			color: #94a3b8;
			width: 32px;
			height: 32px;
			border-radius: 50%;
			cursor: pointer;
			font-size: 16px;
		}
		#avi-np-modal .avi-np-modal-grid {
			display: grid;
			grid-template-columns: 1fr 1fr;
			gap: 12px;
			margin-bottom: 20px;
		}
		#avi-np-modal .avi-np-modal-kpi {
			background: rgba(0,0,0,0.2);
			border-radius: 8px;
			padding: 12px 14px;
		}
		#avi-np-modal .avi-np-modal-kpi-label {
			font-size: 10px;
			text-transform: uppercase;
			color: #64748b;
			margin-bottom: 4px;
			letter-spacing: 0.08em;
		}
		#avi-np-modal .avi-np-modal-kpi-value {
			font-size: 18px;
			font-weight: 700;
			color: #DAA520;
		}
		#avi-np-modal .avi-np-modal-section { margin-bottom: 16px; }
		#avi-np-modal .avi-np-modal-section-title {
			font-size: 11px;
			text-transform: uppercase;
			color: #64748b;
			letter-spacing: 0.08em;
			margin-bottom: 8px;
			font-weight: 600;
		}
		#avi-np-modal table th,
		#avi-np-modal table td {
			font-size: 12px;
			padding: 8px 10px;
		}
		.avi-np-coupon-code {
			font-family: monospace;
			background: rgba(218,165,32,0.08);
			border: 1px dashed rgba(218,165,32,0.3);
			border-radius: 6px;
			padding: 3px 8px;
			color: #DAA520;
			font-size: 12px;
		}
		#avi-np-admin .avi-np-empty {
			text-align: center;
			padding: 40px 0;
			color: #475569;
			font-size: 14px;
		}
		</style>

		<div id="avi-np-admin">
			<div class="avi-np-header">
				<h1>🤝 Nonprofit Applications</h1>
				<p>Review, approve and manage nonprofit organizations — track referrals and donations.</p>
			</div>

			<div class="avi-np-stats">
				<div class="avi-np-stat-card">
					<div class="avi-np-stat-label">Total organizations</div>
					<div class="avi-np-stat-value"><?php echo (int) $stats['total']; ?></div>
					<div class="avi-np-stat-sub">
						<?php echo (int) $stats['verified']; ?> verified · <?php echo (int) $stats['pending']; ?> pending
					</div>
				</div>
				<div class="avi-np-stat-card">
					<div class="avi-np-stat-label">Referrals this month</div>
					<div class="avi-np-stat-value"><?php echo (int) $stats['referrals_month']; ?></div>
					<div class="avi-np-stat-sub">New clients referred</div>
				</div>
				<div class="avi-np-stat-card">
					<div class="avi-np-stat-label">Donations pending</div>
					<div class="avi-np-stat-value"><?php echo number_format( $stats['donations_pending'], 2 ); ?> €</div>
					<div class="avi-np-stat-sub">To be paid this month</div>
				</div>
				<div class="avi-np-stat-card">
					<div class="avi-np-stat-label">Donations paid</div>
					<div class="avi-np-stat-value"><?php echo number_format( $stats['donations_paid'], 2 ); ?> €</div>
					<div class="avi-np-stat-sub">Total paid to date</div>
				</div>
			</div>

			<div class="avi-np-toolbar">
				<form method="get" style="display:contents;">
					<input type="hidden" name="page" value="<?php echo esc_attr( self::PAGE_SLUG ); ?>">
					<input type="hidden" name="np_filter" value="<?php echo esc_attr( $filter ); ?>">
					<input type="text" name="np_search" class="avi-np-search" placeholder="🔍 Search by name, country, number..." value="<?php echo esc_attr( $search ); ?>">
					<button type="button" class="avi-np-btn btn-detail" onclick="this.form.submit();">Search</button>
					<?php if ( $search !== '' ) : ?>
					<a href="<?php echo esc_url( $base_url ); ?>" class="avi-np-btn btn-suspend">✕ Clear</a>
					<?php endif; ?>
				</form>
				<div class="avi-np-filters">
					<?php foreach ( $filters as $key => $label ) : ?>
					<a href="<?php echo esc_url( add_query_arg( array( 'np_filter' => $key, 'np_search' => $search ), $base_url ) ); ?>" class="avi-np-filter-btn <?php echo $filter === $key ? 'is-active' : ''; ?>">
						<?php echo esc_html( $label ); ?>
						<span class="avi-np-filter-count"><?php echo (int) ( isset( $counts[ $key ] ) ? $counts[ $key ] : 0 ); ?></span>
					</a>
					<?php endforeach; ?>
				</div>
			</div>

			<div class="avi-np-table-wrap">
				<table>
					<thead>
						<tr>
							<th>ID</th>
							<th>Organisation</th>
							<th>Statut</th>
							<th>Pays</th>
							<th>Réf. / Dons</th>
							<th>Inscription / Activité</th>
							<th>Niveau</th>
							<th>Actions</th>
						</tr>
					</thead>
					<tbody>
					<?php if ( empty( $rows ) ) : ?>
						<tr>
							<td colspan="8" class="avi-np-empty">No applications found.</td>
						</tr>
					<?php else : ?>
						<?php foreach ( $rows as $row ) : ?>
						<?php
						$np_id        = isset( $row['id'] ) ? (int) $row['id'] : 0;
						$status       = isset( $row['verification_status'] ) ? (string) $row['verification_status'] : '';
						$amb_status   = isset( $row['ambassadrice_status'] ) ? (string) $row['ambassadrice_status'] : 'active';
						$created      = isset( $row['created_at'] ) ? (string) $row['created_at'] : '';
						$ref_count    = isset( $row['referral_count_live'] ) ? (int) $row['referral_count_live'] : 0;
						$total_don    = isset( $row['total_donations_live'] ) ? (float) $row['total_donations_live'] : 0;
						$coupon       = isset( $row['coupon_premium'] ) ? (string) $row['coupon_premium'] : '';
						$coupon_pct   = isset( $row['current_coupon_percent'] ) ? (int) $row['current_coupon_percent'] : 50;
						$can_act      = in_array( $status, array( 'pending', 'manual', 'category_mismatch' ), true );
						$is_verified  = in_array( $status, array( 'verified', 'category_verified' ), true );
						$is_suspended = $amb_status === 'suspended';
						$inact        = $get_inactivity( $row );
						?>
						<tr data-np-id="<?php echo esc_attr( (string) $np_id ); ?>">
							<!-- ID -->
							<td style="color:#64748b;font-size:12px;">
								#<?php echo (int) $np_id; ?>
							</td>

							<!-- Organisation (nom + pays + email) -->
							<td>
								<strong style="color:#e2e8f0;">
									<?php echo esc_html( $row['org_name'] ?? '' ); ?>
								</strong>
								<?php if ( ! empty( $row['org_number'] ) ) : ?>
								<div style="font-size:10px;color:#475569;margin-top:2px;">
									<span style="font-family:monospace;"><?php echo esc_html( $row['org_number'] ); ?></span>
								</div>
								<?php endif; ?>
								<?php if ( ! empty( $row['user_email'] ) ) : ?>
								<div style="margin-top:3px;">
									<a href="mailto:<?php echo esc_attr( $row['user_email'] ); ?>"
										style="color:#60a5fa;font-size:10px;text-decoration:none;">
										<?php echo esc_html( $row['user_email'] ); ?>
									</a>
								</div>
								<?php endif; ?>
								<?php if ( ! empty( $row['slug'] ) ) : ?>
								<div style="font-size:10px;color:#334155;margin-top:1px;">
									np_<?php echo esc_html( $row['slug'] ); ?>
								</div>
								<?php endif; ?>
							</td>

							<!-- Statut -->
							<td>
								<span class="avi-np-badge badge-<?php echo esc_attr( $status ); ?>">
									<?php echo esc_html( ucfirst( str_replace( '_', ' ', $status ) ) ); ?>
								</span>
								<?php if ( $is_suspended ) : ?>
								<span class="avi-np-badge badge-suspended" style="margin-left:4px;">
									Suspended
								</span>
								<?php endif; ?>
							</td>

							<!-- Pays -->
							<td style="text-align:center;">
								<span style="font-size:13px;font-weight:700;color:#e2e8f0;">
									<?php echo esc_html( strtoupper( $row['country_code'] ?? '—' ) ); ?>
								</span>
							</td>

							<!-- Référés / Dons -->
							<td style="text-align:center;">
								<strong style="color:<?php echo $ref_count > 0 ? '#DAA520' : '#475569'; ?>">
									<?php echo (int) $ref_count; ?>
								</strong>
								<span style="color:#475569;font-size:10px;"> réf.</span>
								<br>
								<strong style="color:<?php echo $total_don > 0 ? '#34d399' : '#475569'; ?>;font-size:11px;">
									<?php echo number_format( $total_don, 2 ); ?>€
								</strong>
							</td>

							<!-- Inscription / Activité -->
							<td style="white-space:nowrap;font-size:11px;">
								<div style="color:#64748b;">
									<?php echo esc_html( $created !== '' ? date( 'd/m/Y', strtotime( $created ) ) : '' ); ?>
								</div>
								<div style="margin-top:4px;">
									<span style="font-weight:600;color:<?php echo esc_attr( $inact['color'] ); ?>;">
										<?php echo esc_html( $inact['label'] ); ?>
									</span>
									<?php if ( $inact['days'] <= 30 ) : ?>
									<span title="Compte actif">✅</span>
									<?php elseif ( $inact['days'] >= 60 && $inact['days'] < 90 ) : ?>
									<span title="Avertissement à envoyer">⚠️</span>
									<?php elseif ( $inact['days'] >= 90 ) : ?>
									<span title="Compte passif">🔴</span>
									<?php endif; ?>
								</div>
							</td>

							<!-- Niveau -->
							<td>
								<?php echo $get_level_badge( $row['profile_level'] ?? 0 ); ?>
								<?php if ( ! empty( $row['profile_guardian'] ) && (int) $row['profile_guardian'] === 1 ) : ?>
								<span title="Guardian actif" style="font-size:11px;">🛡️</span>
								<?php endif; ?>
							</td>

							<!-- Actions -->
							<td>
								<div class="avi-np-actions">
									<?php
									// Vérifier si preuve en attente
									$np_share_pending_key = 'avi_np_share_pending_' . (int) $np_id;
									$np_share_pending     = get_option( $np_share_pending_key );

									// Statut preuve partage
									$np_last_share = ! empty( $row['last_share_proof_at'] )
										? strtotime( $row['last_share_proof_at'] )
										: 0;
									$np_share_due  = ( time() - $np_last_share ) >= ( 6 * 30 * 86400 );

									$np_profile_uuid = '';
									if ( ! empty( $row['profile_id'] ) ) {
										$np_profile_uuid = (string) $wpdb->get_var(
											$wpdb->prepare(
												"SELECT uuid FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
												(int) $row['profile_id']
											)
										);
									}
									?>
									<?php if ( $np_profile_uuid ) : ?>
									<a href="<?php echo esc_url( home_url( '/premium-dashboard/?pid=' . $np_profile_uuid ) ); ?>"
										target="_blank" rel="noopener"
										class="avi-np-btn btn-detail"
										title="Visit dashboard"
										style="text-decoration:none;">
										🏠
									</a>
									<?php endif; ?>
									<?php if ( ! empty( $row['user_id'] ) ) : ?>
									<button type="button"
										class="avi-np-btn btn-restore avi-np-btn-login-as"
										data-np-id="<?php echo (int) $np_id; ?>"
										title="Login as this user"
										style="background:rgba(139,92,246,0.15);color:#a78bfa;border:1px solid rgba(139,92,246,0.3);">
										🔑
									</button>
									<?php endif; ?>
									<?php if ( ! empty( $row['profile_id'] ) ) : ?>
									<button type="button"
										class="avi-np-btn btn-detail avi-np-btn-reset-gbp"
										data-np-id="<?php echo (int) $np_id; ?>"
										data-profile-id="<?php echo (int) $row['profile_id']; ?>"
										title="Réinitialiser connexion GBP → modal re-sélection fiche"
										style="background:rgba(96,165,250,0.12);color:#60a5fa;border:1px solid rgba(96,165,250,0.3);font-size:11px;padding:4px 8px;">
										🔄 GBP
									</button>
									<button type="button"
										class="avi-np-btn btn-reject avi-np-btn-reset-oauth"
										data-np-id="<?php echo (int) $np_id; ?>"
										data-profile-id="<?php echo (int) $row['profile_id']; ?>"
										title="Reset OAuth complet — force reconnexion Google complète"
										style="background:rgba(239,68,68,0.08);color:#f87171;border:1px solid rgba(239,68,68,0.2);font-size:11px;padding:4px 8px;">
										🔄 OAuth
									</button>
									<?php endif; ?>
									<?php if ( $inact['days'] >= 60 ) : ?>
									<button type="button"
										class="avi-np-btn btn-suspend avi-np-btn-warn-inactive"
										data-np-id="<?php echo (int) $np_id; ?>"
										title="Envoyer avertissement inactivité"
										style="background:rgba(251,191,36,0.15);color:#fbbf24;border:1px solid rgba(251,191,36,0.3);">
										⚠️
									</button>
									<?php endif; ?>
									<!-- Bouton Valider preuve partage -->
									<?php if ( $np_share_pending ) : ?>
									<button type="button"
										class="avi-np-btn btn-approve avi-np-btn-validate-share"
										data-np-id="<?php echo (int) $np_id; ?>"
										title="Valider preuve de partage réseau"
										style="background:rgba(52,211,153,0.15);color:#34d399;border:1px solid rgba(52,211,153,0.3);">
										📣 Valider
									</button>
									<?php elseif ( $np_share_due && $is_verified ) : ?>
									<span style="font-size:10px;color:#fbbf24;padding:2px 6px;background:rgba(251,191,36,0.08);border-radius:4px;" title="Preuve partage requise">
										📣 Preuve due
									</span>
									<?php else : ?>
									<span style="font-size:10px;color:#34d399;padding:2px 6px;background:rgba(52,211,153,0.08);border-radius:4px;" title="Partage à jour">
										📣 ✅
									</span>
									<?php endif; ?>
									<!-- Reset coupon → 0% -->
									<?php if ( (int) ( $row['current_coupon_percent'] ?? 0 ) > 0 ) : ?>
									<button type="button"
										class="avi-np-btn btn-reject avi-np-btn-reset-coupon"
										data-np-id="<?php echo (int) $np_id; ?>"
										data-current="<?php echo (int) ( $row['current_coupon_percent'] ?? 0 ); ?>"
										title="Reset réduction ambassador à 0%"
										style="font-size:11px;padding:4px 8px;">
										🔴 0%
									</button>
									<?php endif; ?>
									<!-- Restore coupon → % avant reset -->
									<?php if ( ! empty( $row['coupon_percent_before_reset'] ) && (int) $row['coupon_percent_before_reset'] !== (int) ( $row['current_coupon_percent'] ?? 0 ) ) : ?>
									<button type="button"
										class="avi-np-btn btn-restore avi-np-btn-restore-coupon"
										data-np-id="<?php echo (int) $np_id; ?>"
										data-restore="<?php echo (int) $row['coupon_percent_before_reset']; ?>"
										title="Restaurer réduction à <?php echo (int) $row['coupon_percent_before_reset']; ?>%"
										style="font-size:11px;padding:4px 8px;">
										♻️ <?php echo (int) $row['coupon_percent_before_reset']; ?>%
									</button>
									<?php endif; ?>
									<!-- Définir % manuellement -->
									<button type="button"
										class="avi-np-btn btn-detail avi-np-btn-set-coupon"
										data-np-id="<?php echo (int) $np_id; ?>"
										data-current="<?php echo (int) ( $row['current_coupon_percent'] ?? 50 ); ?>"
										title="Définir % manuellement"
										style="font-size:11px;padding:4px 8px;">
										✏️
									</button>
									<button type="button" class="avi-np-btn btn-detail avi-np-btn-detail" data-np-id="<?php echo (int) $np_id; ?>" title="View details">👁</button>
									<?php if ( $can_act ) : ?>
									<button type="button" class="avi-np-btn btn-approve avi-np-btn-approve" data-np-id="<?php echo (int) $np_id; ?>">✅ Approve</button>
									<button type="button" class="avi-np-btn btn-reject avi-np-btn-reject-open" data-np-id="<?php echo (int) $np_id; ?>">❌ Reject</button>
									<div class="avi-np-reject-box" id="reject-box-<?php echo (int) $np_id; ?>">
										<textarea rows="3" placeholder="Reason for rejection..." id="reject-reason-<?php echo (int) $np_id; ?>"></textarea>
										<label>
											<input type="checkbox" id="reject-btoc-<?php echo (int) $np_id; ?>">
											Redirect to B2C paid plan
										</label>
										<button type="button" class="avi-np-btn btn-reject avi-np-confirm-reject" data-np-id="<?php echo (int) $np_id; ?>">Confirm rejection</button>
									</div>
									<?php endif; ?>
									<?php if ( $is_verified && ! $is_suspended ) : ?>
									<button type="button" class="avi-np-btn btn-suspend avi-np-btn-suspend" data-np-id="<?php echo (int) $np_id; ?>" data-action="suspend">🔒 Suspend</button>
									<?php elseif ( $is_suspended ) : ?>
									<button type="button" class="avi-np-btn btn-restore avi-np-btn-suspend" data-np-id="<?php echo (int) $np_id; ?>" data-action="restore">🔓 Restore</button>
									<?php endif; ?>
								</div>
							</td>
						</tr>
						<?php endforeach; ?>
					<?php endif; ?>
					</tbody>
				</table>
			</div>
		</div>

		<div id="avi-np-modal-overlay">
			<div id="avi-np-modal">
				<button type="button" class="avi-np-modal-close" onclick="document.getElementById('avi-np-modal-overlay').classList.remove('is-open');">✕</button>
				<h2 id="avi-np-modal-title">Organization details</h2>
				<div id="avi-np-modal-content">Loading...</div>
			</div>
		</div>

		<script>
		(function () {
			var ajaxUrl = '<?php echo esc_url( admin_url( 'admin-ajax.php' ) ); ?>';
			var nonce   = '<?php echo esc_js( wp_create_nonce( 'avision_nonprofit_nonce' ) ); ?>';

			document.querySelectorAll('.avi-np-btn-approve').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					if (!confirm('Approve this organization?')) return;
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_nonprofit_approve');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
								btn.textContent = '✅ Approve';
							}
						});
				});
			});

			document.querySelectorAll('.avi-np-btn-reject-open').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var box = document.getElementById('reject-box-' + id);
					if (box) {
						box.classList.toggle('is-open');
					}
				});
			});

			document.querySelectorAll('.avi-np-confirm-reject').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var reasonEl = document.getElementById('reject-reason-' + id);
					var btocEl = document.getElementById('reject-btoc-' + id);
					var reason = reasonEl ? String(reasonEl.value).trim() : '';
					var btoc = (btocEl && btocEl.checked) ? 1 : 0;
					if (!reason) {
						alert('Please enter a rejection reason.');
						return;
					}
					btn.disabled = true;
					var fd = new FormData();
					fd.append('action', 'avision_nonprofit_reject');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fd.append('reason', reason);
					fd.append('redirect_btoc', btoc);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							} else {
								alert('Error');
								btn.disabled = false;
							}
						});
				});
			});

			document.querySelectorAll('.avi-np-btn-suspend').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var action = this.dataset.action;
					if (!confirm(action === 'suspend' ? 'Suspend this account?' : 'Restore this account?')) return;
					btn.disabled = true;
					var fd = new FormData();
					fd.append('action', 'avision_np_suspend');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fd.append('suspend_action', action);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							}
						});
				});
			});

			document.querySelectorAll('.avi-np-btn-detail').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var overlay = document.getElementById('avi-np-modal-overlay');
					var content = document.getElementById('avi-np-modal-content');
					overlay.classList.add('is-open');
					content.innerHTML = '<p style="color:#94a3b8;text-align:center;padding:20px;">Loading...</p>';
					var fd = new FormData();
					fd.append('action', 'avision_np_get_detail');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success && res.data) {
								document.getElementById('avi-np-modal-title').textContent = res.data.org_name || 'Details';
								content.innerHTML = res.data.html;
							}
						});
				});
			});

			document.querySelectorAll('.avi-np-btn-login-as').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					if (!confirm(
						'You will be logged in as this nonprofit user.\n'
						+ 'Use the "Switch back" link to return to admin.')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_login_as');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success && res.data && res.data.login_url) {
								window.open(res.data.login_url, '_blank');
								btn.disabled = false;
								btn.textContent = '🔑';
							} else {
								alert((res.data && res.data.message) ? res.data.message : 'Error — no user linked');
								btn.disabled = false;
								btn.textContent = '🔑';
							}
						});
				});
			});

			// Avertissement inactivité
			document.querySelectorAll('.avi-np-btn-warn-inactive').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					if (!confirm('Send inactivity warning to this org?')) {
						return;
					}
					btn.disabled = true;
					var fd = new FormData();
					fd.append('action', 'avision_np_send_inactivity_warning');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								alert('Warning sent ✅');
								location.reload();
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
							}
						});
				});
			});

			// Valider preuve partage
			document.querySelectorAll('.avi-np-btn-validate-share').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					if (!confirm(
						'Validate this share proof?\n'
						+ 'This will update the last_share_proof_at '
						+ 'and mark the org as active.')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_validate_share');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								alert('✅ Proof validated!');
								location.reload();
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
								btn.textContent = '📣 Valider';
							}
						});
				});
			});

			// Reset coupon → 0%
			document.querySelectorAll('.avi-np-btn-reset-coupon').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var current = this.dataset.current;
					if (!confirm(
						'⚠️ Reset ambassador discount to 0%?\n'
						+ 'Current: ' + current + '%\n'
						+ 'The org will be notified by email.')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_reset_coupon');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
								btn.textContent = '🔴 0%';
							}
						});
				});
			});

			// Reset GBP → modal re-sélection
			document.querySelectorAll('.avi-np-btn-reset-gbp').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var pid = this.dataset.profileId;
					if (!confirm(
						'🔄 Reset GBP connection?\n\n'
						+ 'This will:\n'
						+ '- Clear google_location_name\n'
						+ '- Clear pending_google_locations\n'
						+ '- Keep OAuth token intact\n\n'
						+ 'The org will see the GBP selection modal on next login.')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_reset_gbp');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fd.append('profile_id', pid);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								alert('✅ GBP reset — org will see selection modal on next login.');
								btn.textContent = '🔄 GBP';
								btn.disabled = false;
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
								btn.textContent = '🔄 GBP';
							}
						});
				});
			});

			// Reset OAuth complet
			document.querySelectorAll('.avi-np-btn-reset-oauth').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var pid = this.dataset.profileId;
					if (!confirm(
						'⚠️ RESET OAUTH COMPLET\n\n'
						+ 'This will:\n'
						+ '- Clear ALL Google tokens\n'
						+ '- Clear GBP location\n'
						+ '- Force full Google reconnection\n\n'
						+ 'The org will need to reconnect their Google account from scratch.\n\n'
						+ 'Use only if GBP selection modal shows empty list.\n\n'
						+ 'Confirm?')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_reset_oauth');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fd.append('profile_id', pid);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								alert('✅ OAuth reset complete —\nOrg must reconnect Google from their dashboard.');
								btn.textContent = '🔄 OAuth';
								btn.disabled = false;
							} else {
								alert('Error: ' + ((res.data && res.data.message) ? res.data.message : 'failed'));
								btn.disabled = false;
								btn.textContent = '🔄 OAuth';
							}
						});
				});
			});

			// Restore coupon
			document.querySelectorAll('.avi-np-btn-restore-coupon').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var restore = this.dataset.restore;
					if (!confirm(
						'♻️ Restore ambassador discount to '
						+ restore + '%?\n'
						+ 'The org will be notified by email.')) {
						return;
					}
					btn.disabled = true;
					btn.textContent = '...';
					var fd = new FormData();
					fd.append('action', 'avision_np_restore_coupon');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							} else {
								alert('Error');
								btn.disabled = false;
								btn.textContent = '♻️ ' + restore + '%';
							}
						});
				});
			});

			// Définir % manuellement
			document.querySelectorAll('.avi-np-btn-set-coupon').forEach(function (btn) {
				btn.addEventListener('click', function () {
					var id = this.dataset.npId;
					var current = this.dataset.current;
					var pct = prompt(
						'Set ambassador discount %:\n'
						+ 'Allowed values: 0, 50, 70, 80, 90, 100\n'
						+ 'Current: ' + current + '%',
						current
					);
					if (pct === null) {
						return;
					}
					pct = parseInt(pct, 10);
					var allowed = [0, 50, 70, 80, 90, 100];
					if (isNaN(pct) || allowed.indexOf(pct) === -1) {
						alert('Invalid value. Allowed: 0, 50, 70, 80, 90, 100');
						return;
					}
					btn.disabled = true;
					var fd = new FormData();
					fd.append('action', 'avision_np_set_coupon');
					fd.append('nonce', nonce);
					fd.append('np_id', id);
					fd.append('percent', pct);
					fetch(ajaxUrl, { method: 'POST', body: fd, credentials: 'same-origin' })
						.then(function (r) { return r.json(); })
						.then(function (res) {
							if (res.success) {
								location.reload();
							} else {
								alert('Error');
								btn.disabled = false;
							}
						});
				});
			});

			document.getElementById('avi-np-modal-overlay').addEventListener('click', function (e) {
				if (e.target === this) {
					this.classList.remove('is-open');
				}
			});
		})();
		</script>
		<?php
	}

	/**
	 * @return void
	 */
	public static function handle_get_detail() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}

		$np_id = isset( $_POST['np_id'] ) ? (int) wp_unslash( $_POST['np_id'] ) : 0;
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid ID' ) );
			return;
		}

		global $wpdb;
		$table        = self::table_name();
		$ref_table    = $wpdb->prefix . 'avi_nonprofit_referrals';
		$payout_table = $wpdb->prefix . 'avi_nonprofit_payouts';

		$org = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM `{$table}` WHERE id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org ) {
			wp_send_json_error( array( 'message' => 'Not found' ) );
			return;
		}

		$profile_uuid_admin = '';
		$dashboard_link     = '';
		if ( ! empty( $org['profile_id'] ) ) {
			$profile_uuid_admin = (string) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT uuid FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
					(int) $org['profile_id']
				)
			);
			if ( $profile_uuid_admin ) {
				$dashboard_link = home_url( '/premium-dashboard/?pid=' . $profile_uuid_admin );
			}
		}

		$referrals = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.created_at, r.amount_paid, r.donation_amount, r.paid_out, p.business_name, p.city
				FROM `{$ref_table}` r
				LEFT JOIN {$wpdb->prefix}avi_saas_profiles p ON r.referred_user_id = p.user_id
				WHERE r.nonprofit_id = %d
				ORDER BY r.created_at DESC LIMIT 20",
				$np_id
			),
			ARRAY_A
		);

		$payouts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT period_month, donation_amount, payout_method, payout_status, payout_date
				FROM `{$payout_table}`
				WHERE nonprofit_id = %d
				ORDER BY period_month DESC LIMIT 12",
				$np_id
			),
			ARRAY_A
		);

		if ( ! is_array( $referrals ) ) {
			$referrals = array();
		}
		if ( ! is_array( $payouts ) ) {
			$payouts = array();
		}

		$total_ref = count( $referrals );
		$total_don = array_sum( array_column( $referrals, 'donation_amount' ) );
		$pending_don = 0.00;
		foreach ( $payouts as $p ) {
			if ( isset( $p['payout_status'] ) && $p['payout_status'] === 'pending' ) {
				$pending_don += (float) ( isset( $p['donation_amount'] ) ? $p['donation_amount'] : 0 );
			}
		}

		ob_start();
		?>
		<?php if ( $dashboard_link ) : ?>
		<div style="margin-bottom:16px;">
			<a href="<?php echo esc_url( $dashboard_link ); ?>"
				target="_blank" rel="noopener"
				style="display:inline-flex;align-items:center;
				gap:8px;background:rgba(218,165,32,0.12);
				border:1px solid rgba(218,165,32,0.3);
				color:#DAA520;padding:8px 16px;
				border-radius:8px;font-size:13px;
				font-weight:600;text-decoration:none;
				transition:all 0.2s;">
				👁 Visit dashboard
				<span style="font-size:11px;opacity:0.7;">
					↗ new tab
				</span>
			</a>
		</div>
		<?php endif; ?>
		<div class="avi-np-modal-grid">
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Country</div>
				<div class="avi-np-modal-kpi-value" style="font-size:14px;color:#e2e8f0;">
					<?php echo esc_html( isset( $org['country_code'] ) ? (string) $org['country_code'] : '—' ); ?>
				</div>
			</div>
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Reg. Number</div>
				<div class="avi-np-modal-kpi-value" style="font-size:13px;font-family:monospace;color:#94a3b8;">
					<?php echo esc_html( isset( $org['org_number'] ) ? (string) $org['org_number'] : '—' ); ?>
				</div>
			</div>
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Total referrals</div>
				<div class="avi-np-modal-kpi-value"><?php echo (int) $total_ref; ?></div>
			</div>
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Total donations</div>
				<div class="avi-np-modal-kpi-value"><?php echo number_format( $total_don, 2 ); ?> €</div>
			</div>
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Pending payout</div>
				<div class="avi-np-modal-kpi-value" style="color:<?php echo $pending_don > 0 ? '#fbbf24' : '#34d399'; ?>">
					<?php echo number_format( $pending_don, 2 ); ?> €
				</div>
			</div>
			<div class="avi-np-modal-kpi">
				<div class="avi-np-modal-kpi-label">Premium discount</div>
				<div class="avi-np-modal-kpi-value">
					<?php echo (int) ( isset( $org['current_coupon_percent'] ) ? $org['current_coupon_percent'] : 50 ); ?>%
				</div>
			</div>
		</div>

		<?php if ( ! empty( $org['coupon_premium'] ) ) : ?>
		<div class="avi-np-modal-section">
			<div class="avi-np-modal-section-title">Premium coupon</div>
			<span class="avi-np-coupon-code"><?php echo esc_html( (string) $org['coupon_premium'] ); ?></span>
		</div>
		<?php endif; ?>

		<?php if ( ! empty( $referrals ) ) : ?>
		<div class="avi-np-modal-section">
			<div class="avi-np-modal-section-title">Last referrals</div>
			<table style="width:100%;border-collapse:collapse;">
				<thead>
					<tr>
						<th>Date</th>
						<th>Business</th>
						<th>Amount</th>
						<th>Donation</th>
						<th>Status</th>
					</tr>
				</thead>
				<tbody>
				<?php foreach ( $referrals as $r ) : ?>
				<tr>
					<td><?php echo esc_html( isset( $r['created_at'] ) ? date( 'd/m/Y', strtotime( $r['created_at'] ) ) : '' ); ?></td>
					<td><?php echo esc_html( isset( $r['business_name'] ) ? (string) $r['business_name'] : '—' ); ?></td>
					<td><?php echo number_format( (float) ( isset( $r['amount_paid'] ) ? $r['amount_paid'] : 0 ), 2 ); ?> €</td>
					<td style="color:#34d399;"><?php echo number_format( (float) ( isset( $r['donation_amount'] ) ? $r['donation_amount'] : 0 ), 2 ); ?> €</td>
					<td style="color:<?php echo ! empty( $r['paid_out'] ) ? '#34d399' : '#fbbf24'; ?>">
						<?php echo ! empty( $r['paid_out'] ) ? '✓ Paid' : '⏳ Pending'; ?>
					</td>
				</tr>
				<?php endforeach; ?>
				</tbody>
			</table>
		</div>
		<?php endif; ?>

		<?php if ( ! empty( $payouts ) ) : ?>
		<div class="avi-np-modal-section">
			<div class="avi-np-modal-section-title">Payout history</div>
			<table style="width:100%;border-collapse:collapse;">
				<thead>
					<tr>
						<th>Period</th>
						<th>Amount</th>
						<th>Method</th>
						<th>Status</th>
					</tr>
				</thead>
				<tbody>
				<?php foreach ( $payouts as $p ) : ?>
				<tr>
					<td><?php echo esc_html( isset( $p['period_month'] ) ? (string) $p['period_month'] : '' ); ?></td>
					<td style="color:#DAA520;"><?php echo number_format( (float) ( isset( $p['donation_amount'] ) ? $p['donation_amount'] : 0 ), 2 ); ?> €</td>
					<td><?php echo esc_html( isset( $p['payout_method'] ) ? (string) $p['payout_method'] : '' ); ?></td>
					<td style="color:<?php echo ( isset( $p['payout_status'] ) && $p['payout_status'] === 'paid' ) ? '#34d399' : '#fbbf24'; ?>">
						<?php echo esc_html( ucfirst( isset( $p['payout_status'] ) ? (string) $p['payout_status'] : '' ) ); ?>
					</td>
				</tr>
				<?php endforeach; ?>
				</tbody>
			</table>
		</div>
		<?php endif; ?>

		<div class="avi-np-modal-section" style="margin-top:16px;padding-top:16px;border-top:1px solid rgba(255,255,255,0.06);">
			<div class="avi-np-modal-section-title">Payout method</div>
			<p style="color:#94a3b8;font-size:13px;margin:0;">
				<?php echo esc_html( ucfirst( isset( $org['payout_method'] ) ? (string) $org['payout_method'] : 'stripe' ) ); ?>
				<?php if ( ! empty( $org['payout_details'] ) ) : ?>
				— <span style="font-family:monospace;font-size:12px;color:#64748b;">
					<?php
					$pd      = (string) $org['payout_details'];
					$decoded = json_decode( $pd, true );
					if ( is_array( $decoded ) ) {
						echo esc_html( implode( ' / ', array_filter( $decoded ) ) );
					} else {
						echo esc_html( $pd );
					}
					?>
				</span>
				<?php endif; ?>
			</p>
		</div>
		<?php
		$html = ob_get_clean();

		wp_send_json_success(
			array(
				'org_name' => isset( $org['org_name'] ) ? (string) $org['org_name'] : 'Details',
				'html'     => $html,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_login_as() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}

		$np_id = isset( $_POST['np_id'] ) ? (int) wp_unslash( $_POST['np_id'] ) : 0;
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid np_id' ) );
			return;
		}

		global $wpdb;
		$table = self::table_name();
		$org   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT user_id, profile_id FROM `{$table}` WHERE id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org || empty( $org['user_id'] ) ) {
			wp_send_json_error( array( 'message' => 'No user linked to this nonprofit' ) );
			return;
		}

		$target_user_id = (int) $org['user_id'];
		$user           = get_userdata( $target_user_id );
		if ( ! $user ) {
			wp_send_json_error( array( 'message' => 'User not found' ) );
			return;
		}

		$admin_user_id = get_current_user_id();
		update_user_meta( $target_user_id, 'avi_np_switched_from_admin', $admin_user_id );

		$profile_uuid = '';
		if ( ! empty( $org['profile_id'] ) ) {
			$profile_uuid = (string) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT uuid FROM {$wpdb->prefix}avi_saas_profiles WHERE id = %d LIMIT 1",
					(int) $org['profile_id']
				)
			);
		}

		$redirect = home_url( '/premium-dashboard/' );
		if ( $profile_uuid ) {
			$redirect = add_query_arg( 'pid', $profile_uuid, $redirect );
		}
		$redirect = add_query_arg( 'avi_admin_view', '1', $redirect );

		$login_url = add_query_arg(
			array(
				'avi_np_switch' => wp_create_nonce( 'avi_np_switch_' . $target_user_id ),
				'avi_np_uid'    => $target_user_id,
				'redirect_to'   => rawurlencode( $redirect ),
			),
			home_url( '/' )
		);

		wp_send_json_success(
			array(
				'login_url'  => $login_url,
				'user_email' => $user->user_email,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_validate_share() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id = (int) ( $_POST['np_id'] ?? 0 );
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid ID' ) );
			return;
		}
		global $wpdb;
		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array(
				'last_share_proof_at' => current_time( 'mysql' ),
				'last_activity_at'    => current_time( 'mysql' ),
				'share_proof_count'   => $wpdb->get_var(
					$wpdb->prepare(
						"SELECT share_proof_count + 1
						 FROM {$wpdb->prefix}avi_nonprofits
						 WHERE id = %d",
						$np_id
					)
				),
			),
			array( 'id' => $np_id ),
			array( '%s', '%s', '%d' ),
			array( '%d' )
		);

		// Supprimer l'option pending
		$pending_key = 'avi_np_share_pending_' . $np_id;
		delete_option( $pending_key );

		// Mettre à jour last_activity_at
		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array( 'last_activity_at' => current_time( 'mysql' ) ),
			array( 'id' => $np_id ),
			array( '%s' ),
			array( '%d' )
		);

		wp_send_json_success(
			array(
				'message' => 'Share proof validated',
				'np_id'   => $np_id,
			)
		);
	}

	/**
	 * Cron mensuel : reset coupon si preuve partage expirée (> 6 mois + 15j grâce).
	 *
	 * @return void
	 */
	public static function process_check_share_proofs() {
		global $wpdb;
		$table = $wpdb->prefix . 'avi_nonprofits';

		$threshold = gmdate( 'Y-m-d H:i:s', time() - ( ( 6 * 30 + 15 ) * 86400 ) );

		$orgs = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT n.id, n.org_name, n.user_id,
						n.current_coupon_percent,
						n.last_share_proof_at,
						u.user_email
				 FROM `{$table}` n
				 LEFT JOIN {$wpdb->users} u
				   ON u.ID = n.user_id
				 WHERE n.verification_status = 'verified'
				   AND n.ambassadrice_status = 'active'
				   AND (
					 n.last_share_proof_at IS NULL
					 OR n.last_share_proof_at < %s
				   )
				   AND n.current_coupon_percent > 50",
				$threshold
			),
			ARRAY_A
		);

		if ( empty( $orgs ) ) {
			return;
		}

		$reset_count = 0;

		foreach ( $orgs as $org ) {
			$np_id = (int) $org['id'];

			$wpdb->update(
				$table,
				array(
					'current_coupon_percent' => 50,
					'inactivity_warned_at'   => current_time( 'mysql' ),
				),
				array( 'id' => $np_id ),
				array( '%d', '%s' ),
				array( '%d' )
			);

			$reset_count++;

			if ( ! empty( $org['user_email'] ) ) {
				$lang = 'en';
				if ( $org['user_id'] > 0 ) {
					$profile_lang = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT language
							 FROM {$wpdb->prefix}avi_saas_profiles
							 WHERE user_id = %d LIMIT 1",
							(int) $org['user_id']
						)
					);
					if ( in_array( $profile_lang, array( 'fr', 'en', 'es' ), true ) ) {
						$lang = $profile_lang;
					}
				}

				$subjects = array(
					'en' => '[Avision] Your ambassador discount has been adjusted',
					'fr' => '[Avision] Votre réduction ambassadeur a été ajustée',
					'es' => '[Avision] Tu descuento embajador ha sido ajustado',
				);
				$bodies = array(
					'en' => "Hello,\n\nYour Avision Ambassador account has been inactive for more than 6 months without a proof of network sharing.\n\nYour premium discount has been reset to 50% (standard nonprofit rate).\n\nTo recover your higher discount, submit a proof of sharing from your dashboard:\n" . home_url( '/premium-dashboard/' ) . "\n\nThe Avision Team\ncontact@avision.org",
					'fr' => "Bonjour,\n\nVotre compte Ambassadeur Avision est inactif depuis plus de 6 mois sans preuve de partage réseau.\n\nVotre réduction premium a été réinitialisée à 50% (tarif standard association).\n\nPour récupérer votre réduction supérieure, soumettez une preuve de partage depuis votre dashboard :\n" . home_url( '/premium-dashboard/' ) . "\n\nL'équipe Avision\ncontact@avision.org",
					'es' => "Hola,\n\nTu cuenta de Embajador Avision ha estado inactiva durante más de 6 meses sin prueba de difusión en redes.\n\nTu descuento premium ha sido restablecido al 50% (tarifa estándar ONG).\n\nPara recuperar tu descuento mayor, envía una prueba de publicación desde tu dashboard:\n" . home_url( '/premium-dashboard/' ) . "\n\nEl equipo de Avision\ncontact@avision.org",
				);

				wp_mail(
					$org['user_email'],
					$subjects[ $lang ] ?? $subjects['en'],
					$bodies[ $lang ] ?? $bodies['en'],
					array( 'From: Avision <no-reply@avision.org>' )
				);
			}

			error_log(
				'[AVI NP] Coupon reset to 50% — np_id=' . $np_id
				. ' org=' . $org['org_name']
			);
		}

		if ( $reset_count > 0 ) {
			wp_mail(
				array(
					'contact@avision.org',
					'toniosbh@gmail.com',
				),
				'[Avision] ' . $reset_count . ' nonprofit discount(s) reset',
				$reset_count . " nonprofit organization(s) had their ambassador discount reset to 50% due to missing share proof.\n\nReview in WP-Admin:\n" . admin_url( 'admin.php?page=avi-nonprofits' ),
				array( 'From: Avision <no-reply@avision.org>' )
			);
		}
	}

	/**
	 * Helper email ambassador (reset / restore / set / inactivité / partage).
	 *
	 * @param string $user_email Email destinataire.
	 * @param string $org_name   Nom organisation.
	 * @param string $lang       Code langue (fr, en, es).
	 * @param string $subject_key Clé sujet.
	 * @param string $body_key    Clé corps.
	 * @param array  $extra       Données optionnelles (percent, old_percent).
	 * @return void
	 */
	private static function send_ambassador_email( $user_email, $org_name, $lang, $subject_key, $body_key, $extra = array() ) {
		$dashboard_url = home_url( '/premium-dashboard/' );

		$subjects = array(
			'reset' => array(
				'en' => '[Avision] Your ambassador discount has been removed',
				'fr' => '[Avision] Votre réduction ambassador a été supprimée',
				'es' => '[Avision] Tu descuento embajador ha sido eliminado',
			),
			'restore' => array(
				'en' => '[Avision] Your ambassador discount has been restored',
				'fr' => '[Avision] Votre réduction ambassador a été restaurée',
				'es' => '[Avision] Tu descuento embajador ha sido restaurado',
			),
			'set' => array(
				'en' => '[Avision] Your ambassador discount has been updated',
				'fr' => '[Avision] Votre réduction ambassador a été mise à jour',
				'es' => '[Avision] Tu descuento embajador ha sido actualizado',
			),
			'inactivity_60' => array(
				'en' => '[Avision] Your nonprofit account — inactivity notice (60 days)',
				'fr' => '[Avision] Votre compte association — avertissement inactivité (60 jours)',
				'es' => '[Avision] Tu cuenta ONG — aviso de inactividad (60 días)',
			),
			'inactivity_90' => array(
				'en' => '[Avision] Last warning — ambassador discount expires in 48h',
				'fr' => '[Avision] Dernier avertissement — réduction ambassador expire dans 48h',
				'es' => '[Avision] Último aviso — descuento embajador expira en 48h',
			),
			'share_missing' => array(
				'en' => '[Avision] Your network sharing proof is required',
				'fr' => '[Avision] Votre preuve de partage réseau est requise',
				'es' => '[Avision] Tu prueba de difusión en redes es requerida',
			),
		);

		$pct = isset( $extra['percent'] ) ? (int) $extra['percent'] : 0;

		$bodies = array(
			'reset' => array(
				'en' => "Hello {$org_name},\n\nYour Avision Ambassador discount has been removed (0%) due to prolonged inactivity.\n\nTo recover your discount, contact us:\ncontact@avision.org\n\nYour dashboard:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nVotre réduction Ambassador Avision a été supprimée (0%) suite à une inactivité prolongée.\n\nPour récupérer votre réduction, contactez-nous :\ncontact@avision.org\n\nVotre dashboard :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\nTu descuento de Embajador Avision ha sido eliminado (0%) debido a inactividad prolongada.\n\nPara recuperar tu descuento, contáctanos:\ncontact@avision.org\n\nTu dashboard:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
			'restore' => array(
				'en' => "Hello {$org_name},\n\nGreat news! Your Ambassador discount has been restored to {$pct}%.\n\nYour dashboard:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nBonne nouvelle ! Votre réduction Ambassador a été restaurée à {$pct}%.\n\nVotre dashboard :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\n¡Buenas noticias! Tu descuento Embajador ha sido restaurado al {$pct}%.\n\nTu dashboard:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
			'set' => array(
				'en' => "Hello {$org_name},\n\nYour Ambassador discount has been updated to {$pct}%.\n\nYour dashboard:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nVotre réduction Ambassador a été mise à jour à {$pct}%.\n\nVotre dashboard :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\nTu descuento Embajador ha sido actualizado al {$pct}%.\n\nTu dashboard:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
			'inactivity_60' => array(
				'en' => "Hello {$org_name},\n\nWe haven't detected any activity on your Avision account for 60 days.\n\nTo keep your ambassador benefits, please stay active:\n- Log in to your dashboard\n- Refer a local business\n- Submit your network sharing proof (required every 6 months)\n\n⚠️ Without activity in the next 30 days, your ambassador discount will be removed (0%).\n\nYour dashboard:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nNous n'avons pas détecté d'activité sur votre compte Avision depuis 60 jours.\n\nPour conserver vos avantages ambassador, restez actif :\n- Connectez-vous à votre dashboard\n- Parrainez une entreprise locale\n- Soumettez votre preuve de partage réseau (tous les 6 mois)\n\n⚠️ Sans activité dans les 30 prochains jours, votre réduction ambassador sera supprimée (0%).\n\nVotre dashboard :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\nNo hemos detectado actividad en tu cuenta Avision durante 60 días.\n\nPara mantener tus beneficios de embajador, mantente activo:\n- Inicia sesión en tu dashboard\n- Refiere una empresa local\n- Envía tu prueba de difusión en redes (cada 6 meses)\n\n⚠️ Sin actividad en los próximos 30 días, tu descuento embajador será eliminado (0%).\n\nTu dashboard:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
			'inactivity_90' => array(
				'en' => "Hello {$org_name},\n\nDespite our message 30 days ago, no activity has been recorded.\n\nIn 48 hours, your ambassador discount will be automatically removed (0%).\n\nTo avoid this:\n1. Log in to your dashboard\n2. Refer a local business\n3. Or submit your network sharing proof\n\n👉 Act now:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nMalgré notre message il y a 30 jours, aucune activité n'a été enregistrée.\n\nDans 48 heures, votre réduction ambassador sera automatiquement supprimée (0%).\n\nPour éviter cela :\n1. Connectez-vous à votre dashboard\n2. Parrainez une entreprise locale\n3. Ou soumettez votre preuve de partage réseau\n\n👉 Agir maintenant :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\nA pesar de nuestro mensaje hace 30 días, no se ha registrado actividad.\n\nEn 48 horas, tu descuento embajador será eliminado automáticamente (0%).\n\nPara evitarlo:\n1. Inicia sesión en tu dashboard\n2. Refiere una empresa local\n3. O envía tu prueba de difusión en redes\n\n👉 Actúa ahora:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
			'share_missing' => array(
				'en' => "Hello {$org_name},\n\nIt has been more than 6 months since your last validated network sharing proof (or no proof has ever been submitted).\n\nTo maintain your active Ambassador status:\nShare 1 Avision post on your networks (Facebook, Instagram, LinkedIn or other) and submit proof from your dashboard.\n\nHow to submit:\nDashboard → Ambassador Program section → '📣 Network sharing proof' → form\n\nWithout proof in the next 15 days, your ambassador discount will be removed (0%).\n\n👉 Submit my proof:\n{$dashboard_url}\n\nThe Avision Team",
				'fr' => "Bonjour {$org_name},\n\nCela fait plus de 6 mois que votre dernière preuve de partage réseau a été validée (ou aucune preuve n'a jamais été soumise).\n\nPour maintenir votre statut Ambassador actif : partagez 1 publication Avision sur vos réseaux (Facebook, Instagram, LinkedIn ou autre) et soumettez la preuve depuis votre dashboard.\n\nComment soumettre :\nDashboard → Section \"Programme Ambassadeur\" → \"📣 Preuve de partage réseau\" → formulaire\n\nSans preuve dans les 15 jours, votre réduction ambassador sera supprimée (0%).\n\n👉 Soumettre ma preuve :\n{$dashboard_url}\n\nL'équipe Avision",
				'es' => "Hola {$org_name},\n\nHan pasado más de 6 meses desde tu última prueba de difusión en redes validada (o nunca se ha enviado ninguna prueba).\n\nPara mantener tu estado de Embajador activo: comparte 1 publicación de Avision en tus redes (Facebook, Instagram, LinkedIn u otras) y envía la prueba desde tu dashboard.\n\nCómo enviar:\nDashboard → Sección \"Programa Embajador\" → \"📣 Prueba de difusión en redes\" → formulario\n\nSin prueba en los próximos 15 días, tu descuento embajador será eliminado (0%).\n\n👉 Enviar mi prueba:\n{$dashboard_url}\n\nEl equipo de Avision",
			),
		);

		$subject = isset( $subjects[ $subject_key ][ $lang ] )
			? $subjects[ $subject_key ][ $lang ]
			: $subjects[ $subject_key ]['en'];
		$body = isset( $bodies[ $body_key ][ $lang ] )
			? $bodies[ $body_key ][ $lang ]
			: $bodies[ $body_key ]['en'];

		wp_mail(
			$user_email,
			$subject,
			$body,
			array( 'From: Avision <no-reply@avision.org>' )
		);
	}

	/**
	 * @return void
	 */
	public static function handle_reset_coupon() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id = (int) ( $_POST['np_id'] ?? 0 );
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid ID' ) );
			return;
		}
		global $wpdb;
		$org = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT n.org_name, n.current_coupon_percent,
						n.user_id, u.user_email,
						p.language
				 FROM {$wpdb->prefix}avi_nonprofits n
				 LEFT JOIN {$wpdb->users} u
				   ON u.ID = n.user_id
				 LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
				   ON p.id = n.profile_id
				 WHERE n.id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org ) {
			wp_send_json_error( array( 'message' => 'Not found' ) );
			return;
		}

		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array(
				'coupon_percent_before_reset' => (int) $org['current_coupon_percent'],
				'current_coupon_percent'      => 0,
				'coupon_reset_at'             => current_time( 'mysql' ),
				'ambassadrice_status'         => 'suspended',
			),
			array( 'id' => $np_id ),
			array( '%d', '%d', '%s', '%s' ),
			array( '%d' )
		);

		$lang = in_array( $org['language'] ?? '', array( 'fr', 'en', 'es' ), true )
			? $org['language']
			: 'en';

		if ( ! empty( $org['user_email'] ) ) {
			self::send_ambassador_email(
				$org['user_email'],
				$org['org_name'],
				$lang,
				'reset',
				'reset'
			);
		}

		wp_mail(
			array(
				'contact@avision.org',
				'toniosbh@gmail.com',
			),
			'[Avision Admin] Coupon reset 0% — ' . $org['org_name'],
			"Ambassador discount reset to 0%.\nOrg: " . $org['org_name'] . "\nPrevious: " . $org['current_coupon_percent'] . "%\n",
			array( 'From: Avision <no-reply@avision.org>' )
		);

		wp_send_json_success(
			array(
				'message' => 'Reset to 0% done',
				'np_id'   => $np_id,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_restore_coupon() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id = (int) ( $_POST['np_id'] ?? 0 );
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid ID' ) );
			return;
		}
		global $wpdb;
		$org = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT n.org_name,
						n.coupon_percent_before_reset,
						n.user_id, u.user_email,
						p.language
				 FROM {$wpdb->prefix}avi_nonprofits n
				 LEFT JOIN {$wpdb->users} u
				   ON u.ID = n.user_id
				 LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
				   ON p.id = n.profile_id
				 WHERE n.id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org ) {
			wp_send_json_error( array( 'message' => 'Not found' ) );
			return;
		}

		$restore_pct = (int) ( $org['coupon_percent_before_reset'] ?? 50 );

		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array(
				'current_coupon_percent' => $restore_pct,
				'coupon_reset_at'        => null,
				'ambassadrice_status'    => 'active',
				'last_activity_at'       => current_time( 'mysql' ),
			),
			array( 'id' => $np_id ),
			array( '%d', '%s', '%s', '%s' ),
			array( '%d' )
		);

		$lang = in_array( $org['language'] ?? '', array( 'fr', 'en', 'es' ), true )
			? $org['language']
			: 'en';

		if ( ! empty( $org['user_email'] ) ) {
			self::send_ambassador_email(
				$org['user_email'],
				$org['org_name'],
				$lang,
				'restore',
				'restore',
				array( 'percent' => $restore_pct )
			);
		}

		wp_send_json_success(
			array(
				'message' => 'Restored to ' . $restore_pct . '%',
				'percent' => $restore_pct,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_set_coupon() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id   = (int) ( $_POST['np_id'] ?? 0 );
		$pct     = (int) ( $_POST['percent'] ?? -1 );
		$allowed = array( 0, 50, 70, 80, 90, 100 );

		if ( $np_id <= 0 || ! in_array( $pct, $allowed, true ) ) {
			wp_send_json_error( array( 'message' => 'Invalid parameters' ) );
			return;
		}
		global $wpdb;
		$org = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT n.org_name,
						n.current_coupon_percent,
						n.user_id, u.user_email,
						p.language
				 FROM {$wpdb->prefix}avi_nonprofits n
				 LEFT JOIN {$wpdb->users} u
				   ON u.ID = n.user_id
				 LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
				   ON p.id = n.profile_id
				 WHERE n.id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org ) {
			wp_send_json_error( array( 'message' => 'Not found' ) );
			return;
		}

		$update = array(
			'current_coupon_percent' => $pct,
			'last_activity_at'       => current_time( 'mysql' ),
		);
		if ( $pct > 0 ) {
			$update['ambassadrice_status'] = 'active';
			$update['coupon_reset_at']     = null;
		}

		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			$update,
			array( 'id' => $np_id ),
			null,
			array( '%d' )
		);

		$lang = in_array( $org['language'] ?? '', array( 'fr', 'en', 'es' ), true )
			? $org['language']
			: 'en';

		if ( ! empty( $org['user_email'] ) ) {
			self::send_ambassador_email(
				$org['user_email'],
				$org['org_name'],
				$lang,
				'set',
				'set',
				array( 'percent' => $pct )
			);
		}

		wp_send_json_success(
			array(
				'message' => 'Set to ' . $pct . '%',
				'percent' => $pct,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_reset_gbp() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}

		$np_id      = (int) ( $_POST['np_id'] ?? 0 );
		$profile_id = (int) ( $_POST['profile_id'] ?? 0 );

		if ( $np_id <= 0 || $profile_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid parameters' ) );
			return;
		}

		global $wpdb;
		$np = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, profile_id, user_id
				 FROM {$wpdb->prefix}avi_nonprofits
				 WHERE id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $np || (int) $np['profile_id'] !== $profile_id ) {
			wp_send_json_error( array( 'message' => 'Profile mismatch' ) );
			return;
		}

		$updated = $wpdb->update(
			$wpdb->prefix . 'avi_saas_profiles',
			array(
				'google_location_name'     => null,
				'google_account_name'      => null,
				'pending_google_locations' => null,
			),
			array( 'id' => $profile_id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);

		if ( $updated === false ) {
			wp_send_json_error( array( 'message' => 'DB update failed' ) );
			return;
		}

		error_log(
			'[AVI NP] GBP reset — np_id=' . $np_id
			. ' profile_id=' . $profile_id
		);

		wp_send_json_success(
			array(
				'message'    => 'GBP reset OK',
				'np_id'      => $np_id,
				'profile_id' => $profile_id,
			)
		);
	}

	/**
	 * @return void
	 */
	public static function handle_reset_oauth() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}

		$np_id      = (int) ( $_POST['np_id'] ?? 0 );
		$profile_id = (int) ( $_POST['profile_id'] ?? 0 );

		if ( $np_id <= 0 || $profile_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid parameters' ) );
			return;
		}

		global $wpdb;
		$np = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, profile_id, user_id, org_name
				 FROM {$wpdb->prefix}avi_nonprofits
				 WHERE id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $np || (int) $np['profile_id'] !== $profile_id ) {
			wp_send_json_error( array( 'message' => 'Profile mismatch' ) );
			return;
		}

		$updated = $wpdb->update(
			$wpdb->prefix . 'avi_saas_profiles',
			array(
				'google_access_token'      => null,
				'google_refresh_token'     => null,
				'google_token_expires_at'  => null,
				'google_location_name'     => null,
				'google_account_name'      => null,
				'pending_google_locations' => null,
				'is_first_sync'            => 1,
			),
			array( 'id' => $profile_id ),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d' ),
			array( '%d' )
		);

		if ( $updated === false ) {
			wp_send_json_error( array( 'message' => 'DB update failed' ) );
			return;
		}

		$user_email = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT u.user_email
				 FROM {$wpdb->users} u
				 WHERE u.ID = %d LIMIT 1",
				(int) $np['user_id']
			)
		);

		if ( $user_email ) {
			$lang = (string) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT language
					 FROM {$wpdb->prefix}avi_saas_profiles
					 WHERE id = %d LIMIT 1",
					$profile_id
				)
			);
			if ( ! in_array( $lang, array( 'fr', 'en', 'es' ), true ) ) {
				$lang = 'en';
			}

			$subjects = array(
				'en' => '[Avision] Google reconnection required',
				'fr' => '[Avision] Reconnexion Google requise',
				'es' => '[Avision] Reconexión Google requerida',
			);
			$bodies = array(
				'en' => "Hello,\n\nYour Google connection has been reset by the Avision team.\n\nPlease reconnect your Google account from your dashboard to restore your Google Business Profile connection.\n\nYour dashboard:\n" . home_url( '/premium-dashboard/' ) . "\n\nThe Avision Team\ncontact@avision.org",
				'fr' => "Bonjour,\n\nVotre connexion Google a été réinitialisée par l'équipe Avision.\n\nVeuillez reconnecter votre compte Google depuis votre dashboard pour rétablir la connexion à votre fiche Google Business Profile.\n\nVotre dashboard :\n" . home_url( '/premium-dashboard/' ) . "\n\nL'équipe Avision\ncontact@avision.org",
				'es' => "Hola,\n\nTu conexión de Google ha sido restablecida por el equipo de Avision.\n\nPor favor, reconecta tu cuenta de Google desde tu dashboard para restaurar la conexión con tu ficha de Google Business Profile.\n\nTu dashboard:\n" . home_url( '/premium-dashboard/' ) . "\n\nEl equipo de Avision\ncontact@avision.org",
			);

			wp_mail(
				$user_email,
				$subjects[ $lang ] ?? $subjects['en'],
				$bodies[ $lang ] ?? $bodies['en'],
				array( 'From: Avision <no-reply@avision.org>' )
			);
		}

		error_log(
			'[AVI NP] OAuth FULL reset — np_id=' . $np_id
			. ' profile_id=' . $profile_id
			. ' org=' . ( $np['org_name'] ?? '' )
		);

		wp_send_json_success(
			array(
				'message'    => 'OAuth reset complete',
				'np_id'      => $np_id,
				'profile_id' => $profile_id,
			)
		);
	}

	/**
	 * Cron hebdomadaire : emails inactivité J+60 / J+90 + preuve partage manquante.
	 *
	 * @return void
	 */
	public static function process_weekly_inactivity_check() {
		global $wpdb;
		$table = $wpdb->prefix . 'avi_nonprofits';
		$now   = time();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$orgs = $wpdb->get_results(
			"SELECT n.id, n.org_name, n.user_id,
					n.current_coupon_percent,
					n.last_activity_at,
					n.last_referral_at,
					n.last_share_proof_at,
					n.inactivity_warned_at,
					n.coupon_reset_at,
					u.user_email,
					p.language,
					u.user_registered
			 FROM `{$table}` n
			 LEFT JOIN {$wpdb->users} u
			   ON u.ID = n.user_id
			 LEFT JOIN {$wpdb->prefix}avi_saas_profiles p
			   ON p.id = n.profile_id
			 WHERE n.verification_status = 'verified'
			   AND n.ambassadrice_status = 'active'
			   AND n.current_coupon_percent > 0",
			ARRAY_A
		);

		if ( empty( $orgs ) ) {
			return;
		}

		foreach ( $orgs as $org ) {
			if ( empty( $org['user_email'] ) ) {
				continue;
			}

			$np_id = (int) $org['id'];
			$lang  = in_array( $org['language'] ?? '', array( 'fr', 'en', 'es' ), true )
				? $org['language']
				: 'en';

			$dates = array();
			if ( ! empty( $org['last_activity_at'] ) ) {
				$dates[] = strtotime( $org['last_activity_at'] );
			}
			if ( ! empty( $org['last_referral_at'] ) ) {
				$dates[] = strtotime( $org['last_referral_at'] );
			}
			if ( ! empty( $org['last_share_proof_at'] ) ) {
				$dates[] = strtotime( $org['last_share_proof_at'] );
			}
			if ( ! empty( $org['user_registered'] ) ) {
				$dates[] = strtotime( $org['user_registered'] );
			}

			$last_active   = empty( $dates ) ? 0 : max( $dates );
			$days_inactive = (int) floor( ( $now - $last_active ) / 86400 );

			$warned_at = ! empty( $org['inactivity_warned_at'] )
				? strtotime( $org['inactivity_warned_at'] )
				: 0;

			if ( $days_inactive >= 60 && $days_inactive < 90 && $warned_at === 0 ) {
				self::send_ambassador_email(
					$org['user_email'],
					$org['org_name'],
					$lang,
					'inactivity_60',
					'inactivity_60'
				);
				$wpdb->update(
					$table,
					array( 'inactivity_warned_at' => current_time( 'mysql' ) ),
					array( 'id' => $np_id ),
					array( '%s' ),
					array( '%d' )
				);
			}

			if ( $days_inactive >= 90 && $warned_at > 0 && ( $now - $warned_at ) >= ( 25 * 86400 ) ) {
				self::send_ambassador_email(
					$org['user_email'],
					$org['org_name'],
					$lang,
					'inactivity_90',
					'inactivity_90'
				);
				if ( $days_inactive >= 92 ) {
					$wpdb->update(
						$table,
						array(
							'coupon_percent_before_reset' => (int) $org['current_coupon_percent'],
							'current_coupon_percent'      => 0,
							'coupon_reset_at'             => current_time( 'mysql' ),
							'ambassadrice_status'         => 'suspended',
						),
						array( 'id' => $np_id ),
						array( '%d', '%d', '%s', '%s' ),
						array( '%d' )
					);
				}
			}

			$last_share = ! empty( $org['last_share_proof_at'] )
				? strtotime( $org['last_share_proof_at'] )
				: 0;
			$months_since_share = $last_share > 0
				? ( $now - $last_share ) / ( 30 * 86400 )
				: 999;

			$share_warned_key  = 'avi_np_share_warn_' . $np_id;
			$share_warned      = get_option( $share_warned_key, 0 );
			$months_since_warn = $share_warned > 0
				? ( $now - $share_warned ) / ( 30 * 86400 )
				: 999;

			if ( $months_since_share >= 6 && $months_since_warn >= 5 ) {
				self::send_ambassador_email(
					$org['user_email'],
					$org['org_name'],
					$lang,
					'share_missing',
					'share_missing'
				);
				update_option( $share_warned_key, $now, false );
			}
		}
	}

	/**
	 * @return void
	 */
	public static function handle_send_inactivity_warning() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id = (int) ( $_POST['np_id'] ?? 0 );
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid ID' ) );
			return;
		}
		global $wpdb;
		$org = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT n.*, u.user_email
				 FROM {$wpdb->prefix}avi_nonprofits n
				 LEFT JOIN {$wpdb->users} u
				   ON u.ID = n.user_id
				 WHERE n.id = %d LIMIT 1",
				$np_id
			),
			ARRAY_A
		);

		if ( ! $org || empty( $org['user_email'] ) ) {
			wp_send_json_error( array( 'message' => 'No email found' ) );
			return;
		}

		$lang = 'en';
		if ( ! empty( $org['profile_id'] ) ) {
			$lang = (string) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT language
					 FROM {$wpdb->prefix}avi_saas_profiles
					 WHERE id = %d LIMIT 1",
					(int) $org['profile_id']
				)
			) ?: 'en';
		}

		$subjects = array(
			'en' => '[Avision] Your nonprofit account — inactivity notice',
			'fr' => '[Avision] Votre compte association — avertissement inactivité',
			'es' => '[Avision] Tu cuenta ONG — aviso de inactividad',
		);
		$bodies = array(
			'en' => "Hello,\n\nWe noticed your Avision account has been inactive for a while.\n\nAs a reminder, premium discount benefits (50% and above) are reserved for active nonprofit organizations.\n\nIf no activity is recorded within the next 30 days, your premium discount will be reset to the standard rate.\n\nTo stay active:\n- Log in to your dashboard\n- Refer a local business\n- Share Avision on your networks (required twice a year)\n\nYour dashboard: " . home_url( '/premium-dashboard/' ) . "\n\nThe Avision Team\ncontact@avision.org",
			'fr' => "Bonjour,\n\nNous avons remarqué que votre compte Avision est inactif depuis un certain temps.\n\nRappel : les avantages de réduction premium (50% et plus) sont réservés aux associations actives.\n\nSi aucune activité n'est enregistrée dans les 30 prochains jours, votre réduction premium sera réinitialisée au tarif standard.\n\nPour rester actif :\n- Connectez-vous à votre dashboard\n- Référez une entreprise locale\n- Partagez Avision sur vos réseaux (requis 2 fois par an)\n\nVotre dashboard : " . home_url( '/premium-dashboard/' ) . "\n\nL'équipe Avision\ncontact@avision.org",
			'es' => "Hola,\n\nHemos notado que tu cuenta Avision ha estado inactiva durante un tiempo.\n\nRecordatorio: los beneficios de descuento premium (50% y más) están reservados para organizaciones activas.\n\nSi no se registra actividad en los próximos 30 días, tu descuento premium se restablecerá a la tarifa estándar.\n\nPara mantenerte activo:\n- Inicia sesión en tu dashboard\n- Refiere un negocio local\n- Comparte Avision en tus redes (requerido 2 veces al año)\n\nTu dashboard: " . home_url( '/premium-dashboard/' ) . "\n\nEl equipo de Avision\ncontact@avision.org",
		);

		$subject = $subjects[ $lang ] ?? $subjects['en'];
		$body    = $bodies[ $lang ] ?? $bodies['en'];

		wp_mail(
			$org['user_email'],
			$subject,
			$body,
			array( 'From: Avision <no-reply@avision.org>' )
		);

		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array( 'inactivity_warned_at' => current_time( 'mysql' ) ),
			array( 'id' => $np_id ),
			array( '%s' ),
			array( '%d' )
		);

		wp_send_json_success(
			array(
				'message' => 'Warning sent to ' . $org['user_email'],
			)
		);
	}

	/**
	 * @return void
	 */
	public static function maybe_switch_to_user() {
		if ( empty( $_GET['avi_np_switch'] ) || empty( $_GET['avi_np_uid'] ) ) {
			return;
		}

		if ( ! is_user_logged_in() || ! current_user_can( 'manage_options' ) ) {
			return;
		}

		$target_uid = isset( $_GET['avi_np_uid'] ) ? (int) wp_unslash( $_GET['avi_np_uid'] ) : 0;
		$nonce      = isset( $_GET['avi_np_switch'] ) ? sanitize_text_field( wp_unslash( $_GET['avi_np_switch'] ) ) : '';

		if ( ! wp_verify_nonce( $nonce, 'avi_np_switch_' . $target_uid ) ) {
			return;
		}

		$redirect = isset( $_GET['redirect_to'] )
			? esc_url_raw( urldecode( (string) wp_unslash( $_GET['redirect_to'] ) ) )
			: home_url( '/premium-dashboard/' );

		if ( ! session_id() ) {
			session_start();
		}
		$_SESSION['avi_admin_switched_from'] = get_current_user_id();

		wp_set_current_user( $target_uid );
		wp_set_auth_cookie( $target_uid, false );

		wp_safe_redirect( $redirect );
		exit;
	}

	/**
	 * @return void
	 */
	public static function maybe_switch_back() {
		if ( empty( $_GET['avi_np_switch_back'] ) || empty( $_GET['avi_admin_uid'] ) ) {
			return;
		}

		$admin_uid = isset( $_GET['avi_admin_uid'] ) ? (int) wp_unslash( $_GET['avi_admin_uid'] ) : 0;
		$nonce     = isset( $_GET['avi_np_switch_back'] ) ? sanitize_text_field( wp_unslash( $_GET['avi_np_switch_back'] ) ) : '';

		if ( ! wp_verify_nonce( $nonce, 'avi_np_switch_back_' . $admin_uid ) ) {
			return;
		}

		delete_user_meta( get_current_user_id(), 'avi_np_switched_from_admin' );

		wp_set_current_user( $admin_uid );
		wp_set_auth_cookie( $admin_uid, false );

		wp_safe_redirect( home_url( '/wp-admin/admin.php?page=avi-nonprofits' ) );
		exit;
	}

	/**
	 * @return void
	 */
	public static function handle_approve() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
		}

		$np_id = isset( $_POST['np_id'] ) ? (int) wp_unslash( $_POST['np_id'] ) : 0;
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid np_id' ) );
		}

		global $wpdb;
		$table = self::table_name();
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
			wp_send_json_error( array( 'message' => 'Table missing' ) );
		}

		$updated = $wpdb->update(
			$table,
			array(
				'verification_status' => 'verified',
				'verified_at'         => current_time( 'mysql', true ),
			),
			array( 'id' => $np_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( false === $updated ) {
			wp_send_json_error( array( 'message' => 'Update failed' ) );
		}

		$coupon_ok = avi_nonprofit_generate_coupons( $np_id );
		if ( is_wp_error( $coupon_ok ) ) {
			wp_send_json_error( array( 'message' => $coupon_ok->get_error_message() ) );
		}

		avi_nonprofit_send_approval_email( $np_id );
		wp_send_json_success( array( 'np_id' => $np_id ) );
	}

	/**
	 * @return void
	 */
	public static function handle_reject() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
		}

		$np_id = isset( $_POST['np_id'] ) ? (int) wp_unslash( $_POST['np_id'] ) : 0;
		$reason = isset( $_POST['reason'] ) ? sanitize_textarea_field( wp_unslash( (string) $_POST['reason'] ) ) : '';
		$redirect_btoc = isset( $_POST['redirect_btoc'] ) ? (int) wp_unslash( $_POST['redirect_btoc'] ) : 0;

		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid np_id' ) );
		}
		if ( $reason === '' ) {
			wp_send_json_error( array( 'message' => 'Reason required' ) );
		}

		global $wpdb;
		$table = self::table_name();
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
			wp_send_json_error( array( 'message' => 'Table missing' ) );
		}

		$updated = $wpdb->update(
			$table,
			array(
				'verification_status' => 'rejected',
				'rejection_reason'    => $reason,
				'redirect_to_btoc'    => $redirect_btoc ? 1 : 0,
			),
			array( 'id' => $np_id ),
			array( '%s', '%s', '%d' ),
			array( '%d' )
		);

		if ( false === $updated ) {
			wp_send_json_error( array( 'message' => 'Update failed' ) );
		}

		avi_nonprofit_send_rejection_email( $np_id, $reason, $redirect_btoc );
		wp_send_json_success( array( 'np_id' => $np_id ) );
	}

	/**
	 * Sauvegarde mode de versement nonprofit (dashboard).
	 *
	 * @return void
	 */
	public static function handle_save_payout() {
		check_ajax_referer( 'avision_np_payout', 'nonce' );
		$user_id = get_current_user_id();
		if ( $user_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ) );
			return;
		}
		$np_id = (int) get_user_meta( $user_id, 'avi_np_id', true );
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Not a nonprofit' ) );
			return;
		}
		$method  = isset( $_POST['method'] ) ? sanitize_text_field( wp_unslash( (string) $_POST['method'] ) ) : 'stripe';
		$details = isset( $_POST['details'] ) ? sanitize_text_field( wp_unslash( (string) $_POST['details'] ) ) : '';
		$allowed = array( 'stripe', 'paypal', 'bank' );
		if ( ! in_array( $method, $allowed, true ) ) {
			wp_send_json_error( array( 'message' => 'Invalid method' ) );
			return;
		}
		global $wpdb;
		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array(
				'payout_method'  => $method,
				'payout_details' => $details,
			),
			array( 'id' => $np_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		wp_send_json_success();
	}

	/**
	 * Suspend / restore compte ambassadeur (admin).
	 *
	 * @return void
	 */
	public static function handle_suspend() {
		check_ajax_referer( 'avision_nonprofit_nonce', 'nonce' );
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ), 403 );
			return;
		}
		$np_id  = isset( $_POST['np_id'] ) ? (int) wp_unslash( $_POST['np_id'] ) : 0;
		$action = isset( $_POST['suspend_action'] ) ? sanitize_text_field( wp_unslash( (string) $_POST['suspend_action'] ) ) : 'suspend';
		if ( $np_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid np_id' ) );
			return;
		}
		global $wpdb;
		$user_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT user_id FROM {$wpdb->prefix}avi_nonprofits WHERE id = %d LIMIT 1",
				$np_id
			)
		);
		if ( $action === 'suspend' && $user_id > 0 ) {
			$user = get_userdata( $user_id );
			if ( $user ) {
				$user->set_role( '' );
			}
			update_user_meta( $user_id, 'avi_np_suspended', 1 );
			$wpdb->update(
				$wpdb->prefix . 'avi_nonprofits',
				array( 'ambassadrice_status' => 'suspended' ),
				array( 'id' => $np_id ),
				array( '%s' ),
				array( '%d' )
			);
		} elseif ( $action === 'restore' && $user_id > 0 ) {
			$user = get_userdata( $user_id );
			if ( $user ) {
				$user->set_role( 'subscriber' );
			}
			delete_user_meta( $user_id, 'avi_np_suspended' );
			$wpdb->update(
				$wpdb->prefix . 'avi_nonprofits',
				array( 'ambassadrice_status' => 'active' ),
				array( 'id' => $np_id ),
				array( '%s' ),
				array( '%d' )
			);
		}
		wp_send_json_success();
	}

	/**
	 * Lie np_id au user créé après onboarding-save REST.
	 *
	 * @param WP_REST_Response $response Réponse REST.
	 * @param WP_REST_Server   $server   Serveur REST.
	 * @param WP_REST_Request  $request  Requête REST.
	 * @return WP_REST_Response
	 */
	public static function intercept_onboarding_save_response( $response, $server, $request ) {
		if ( strpos( $request->get_route(), 'onboarding-save' ) === false ) {
			return $response;
		}

		$np_id = (int) $request->get_param( 'np_id' );
		if ( $np_id <= 0 ) {
			return $response;
		}

		$data = $response->get_data();
		if ( empty( $data['success'] ) ) {
			return $response;
		}

		$email = sanitize_email( (string) $request->get_param( 'email' ) );
		if ( ! $email ) {
			return $response;
		}

		$wp_user = get_user_by( 'email', $email );
		if ( ! $wp_user ) {
			return $response;
		}

		$user_id = (int) $wp_user->ID;

		global $wpdb;
		$profile_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM `{$wpdb->prefix}avi_saas_profiles` WHERE user_id = %d ORDER BY id DESC LIMIT 1",
				$user_id
			)
		);

		update_user_meta( $user_id, 'avi_np_id', $np_id );

		$wpdb->update(
			$wpdb->prefix . 'avi_nonprofits',
			array(
				'user_id'    => $user_id,
				'profile_id' => $profile_id,
			),
			array( 'id' => $np_id ),
			array( '%d', '%d' ),
			array( '%d' )
		);

		return $response;
	}

	/**
	 * Vérifie la catégorie GBP nonprofit après first-sync REST.
	 *
	 * @param WP_REST_Response $response Réponse REST.
	 * @param WP_REST_Server   $server   Serveur REST.
	 * @param WP_REST_Request  $request  Requête REST.
	 * @return WP_REST_Response
	 */
	public static function intercept_first_sync_response( $response, $server, $request ) {
		if ( strpos( $request->get_route(), 'first-sync' ) === false ) {
			return $response;
		}

		$user_id = get_current_user_id();
		if ( $user_id <= 0 ) {
			return $response;
		}

		$np_id = (int) get_user_meta( $user_id, 'avi_np_id', true );

		if ( $np_id > 0 ) {
			global $wpdb;
			$profile_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$wpdb->prefix}avi_saas_profiles` WHERE user_id = %d ORDER BY id DESC LIMIT 1",
					$user_id
				)
			);
			if ( $profile_id > 0 ) {
				$gbp_category = (string) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT google_primary_category FROM `{$wpdb->prefix}avi_saas_profiles` WHERE id = %d LIMIT 1",
						$profile_id
					)
				);

				if ( ! empty( $gbp_category ) ) {
					$status = self::process_oauth_verification( $np_id, $gbp_category );

					update_user_meta( $user_id, 'avi_np_verification_status', $status );

					update_user_meta(
						$user_id,
						'avi_np_gbp_category',
						sanitize_text_field( $gbp_category )
					);

					if ( $status === 'category_verified' || $status === 'verified' ) {
						$wpdb->update(
							$wpdb->prefix . 'avi_saas_profiles',
							array( 'category' => 'nonprofit_community' ),
							array( 'id' => $profile_id ),
							array( '%s' ),
							array( '%d' )
						);
					}
				}
			}
		}

		// Tracking parrainage post-OAuth.
		$ref = get_user_meta( $user_id, 'avi_np_ref_pending', true );
		if ( $ref && strpos( $ref, 'np_' ) === 0 ) {
			global $wpdb;
			$ref_slug = substr( $ref, 3 );
			$ref_np   = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id, referral_count, current_coupon_percent FROM {$wpdb->prefix}avi_nonprofits WHERE slug = %s LIMIT 1",
					$ref_slug
				),
				ARRAY_A
			);

			if ( is_array( $ref_np ) && ! empty( $ref_np ) ) {
				$ref_np_id = (int) $ref_np['id'];

				$wpdb->insert(
					$wpdb->prefix . 'avi_nonprofit_referrals',
					array(
						'nonprofit_id'      => $ref_np_id,
						'referred_user_id'  => $user_id,
						'stripe_session_id' => '',
						'amount_paid'       => 0.00,
						'donation_amount'   => 0.00,
						'paid_out'          => 0,
						'created_at'        => current_time( 'mysql' ),
					),
					array( '%d', '%d', '%s', '%f', '%f', '%d', '%s' )
				);

				$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' ),
					),
					array( 'id' => $ref_np_id ),
					array( '%d', '%s' ),
					array( '%d' )
				);

				self::maybe_upgrade_referral_tier( $ref_np_id, $new_count );
				delete_user_meta( $user_id, 'avi_np_ref_pending' );
			}
		}

		return $response;
	}

	/**
	 * Compare la catégorie GBP OAuth à la whitelist nonprofit.
	 *
	 * @param int    $np_id        ID avi_nonprofits.
	 * @param string $gbp_category Catégorie Google primaire.
	 * @return string Statut verification_status.
	 */
	public static function process_oauth_verification( $np_id, $gbp_category ) {
		global $wpdb;
		$np_id        = (int) $np_id;
		$gbp_category = trim( (string) $gbp_category );
		$table        = self::table_name();

		$whitelist = array(
			'nonprofit',
			'non-profit',
			'charity',
			'charitable',
			'foundation',
			'association',
			'ngo',
			'ong',
			'social enterprise',
			'community organization',
			'religious organization',
			'church',
			'mosque',
			'synagogue',
			'temple',
			'food bank',
			'shelter',
			'volunteer',
		);

		$cat_lower = strtolower( $gbp_category );
		$matched   = false;
		foreach ( $whitelist as $keyword ) {
			if ( strpos( $cat_lower, $keyword ) !== false ) {
				$matched = true;
				break;
			}
		}

		$status = $matched ? 'pending' : 'category_mismatch';

		$wpdb->update(
			$table,
			array(
				'gbp_category_matched' => $gbp_category,
				'verification_status'  => $status,
			),
			array( 'id' => $np_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( ! $matched && function_exists( 'avi_nonprofit_send_admin_alert' ) ) {
			avi_nonprofit_send_admin_alert( $np_id, 'category_mismatch' );
		}

		return $status;
	}

	/**
	 * Planification cron mensuelle (intervalle monthly).
	 *
	 * @param array<string, mixed> $schedules Schedules WP-Cron.
	 * @return array<string, mixed>
	 */
	public static function add_monthly_cron_schedule( $schedules ) {
		if ( ! is_array( $schedules ) ) {
			$schedules = array();
		}
		if ( ! isset( $schedules['monthly'] ) ) {
			$schedules['monthly'] = array(
				'interval' => 30 * DAY_IN_SECONDS,
				'display'  => 'Once monthly',
			);
		}
		return $schedules;
	}

	/**
	 * Cookie parrainage ?ref=np_{slug} + redirect /local-business/.
	 *
	 * @return void
	 */
	public static function handle_referral_cookie() {
		if ( empty( $_GET['ref'] ) ) {
			return;
		}
		$ref = sanitize_text_field( wp_unslash( (string) $_GET['ref'] ) );
		if ( strpos( $ref, 'np_' ) !== 0 ) {
			return;
		}

		$slug = substr( $ref, 3 );
		if ( $slug === '' ) {
			return;
		}

		global $wpdb;
		$np_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}avi_nonprofits WHERE slug = %s AND verification_status IN ('verified','category_verified') LIMIT 1",
				$slug
			)
		);

		if ( $np_id <= 0 ) {
			return;
		}

		if ( ! headers_sent() ) {
			setcookie(
				'avi_np_ref',
				$ref,
				time() + ( 30 * DAY_IN_SECONDS ),
				'/',
				'',
				is_ssl(),
				false
			);
			$_COOKIE['avi_np_ref'] = $ref;
		}

		$current_url = isset( $_SERVER['REQUEST_URI'] ) ? (string) wp_unslash( $_SERVER['REQUEST_URI'] ) : '/';
		$is_app_page = (
			strpos( $current_url, '/premium-dashboard' ) !== false
			|| strpos( $current_url, '/agency-dashboard' ) !== false
			|| strpos( $current_url, '/local-audit' ) !== false
		);

		if ( ! $is_app_page ) {
			$params = $_GET;
			unset( $params['ref'] );
			$redirect = home_url( '/local-business/' );
			if ( ! empty( $params ) ) {
				$redirect = add_query_arg( $params, $redirect );
			}
			wp_safe_redirect( $redirect, 302 );
			exit;
		}
	}

	/**
	 * Stocke np_ref en user_meta avant checkout Stripe.
	 *
	 * @param string $report_id   Report ID metadata.
	 * @param int    $user_id     ID utilisateur.
	 * @param string $avi_product Produit AVI.
	 * @return string
	 */
	public static function inject_np_ref_in_metadata( $report_id, $user_id, $avi_product ) {
		$ref = isset( $_COOKIE['avi_np_ref'] )
			? sanitize_text_field( wp_unslash( (string) $_COOKIE['avi_np_ref'] ) )
			: '';

		if ( $ref !== '' && (int) $user_id > 0 ) {
			update_user_meta( (int) $user_id, 'avi_np_ref_pending', $ref );
		}

		return $report_id;
	}

	/**
	 * Upgrade coupon Stripe si palier parrainage franchi.
	 *
	 * @param int $np_id           ID nonprofit.
	 * @param int $referral_count  Nombre de parrainages.
	 * @return void
	 */
	public static function maybe_upgrade_referral_tier( $np_id, $referral_count ) {
		global $wpdb;
		$table = $wpdb->prefix . 'avi_nonprofits';
		$np_id = (int) $np_id;

		$org = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $np_id ),
			ARRAY_A
		);
		if ( ! is_array( $org ) ) {
			return;
		}

		$current_percent = isset( $org['current_coupon_percent'] ) ? (int) $org['current_coupon_percent'] : 50;

		$new_percent = 50;
		if ( $referral_count >= 30 ) {
			$new_percent = 100;
		} elseif ( $referral_count >= 21 ) {
			$new_percent = 90;
		} elseif ( $referral_count >= 11 ) {
			$new_percent = 80;
		} elseif ( $referral_count >= 1 ) {
			$new_percent = 70;
		}

		if ( $new_percent <= $current_percent ) {
			return;
		}

		$org_name = isset( $org['org_name'] ) ? (string) $org['org_name'] : 'ORG';
		$slug_raw = sanitize_title( $org_name );
		$slug     = strtoupper( substr( preg_replace( '/[^A-Z0-9]/', '', strtoupper( $slug_raw ) ), 0, 10 ) );
		if ( $slug === '' ) {
			$slug = 'NPORG';
		}
		$code          = strtoupper( wp_generate_password( 6, false, false ) );
		$new_coupon_id = 'NP-' . $slug . '-' . $code . '-' . $new_percent;

		if ( ! function_exists( 'avi_stripe_s10b_resolve_secret_key' ) && defined( 'AVISION_PATH' ) ) {
			$stripe_file = AVISION_PATH . 'includes/avi-stripe-s10b.php';
			if ( is_readable( $stripe_file ) ) {
				require_once $stripe_file;
			}
		}

		if ( ! function_exists( 'avi_stripe_s10b_resolve_secret_key' ) ) {
			return;
		}

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

		$result = avi_nonprofit_stripe_create_coupon( $secret_key, $new_coupon_id, $new_percent, 'forever' );
		if ( is_wp_error( $result ) ) {
			error_log( '[AVI NP] Coupon upgrade failed: ' . $result->get_error_message() );
			return;
		}

		$wpdb->update(
			$table,
			array(
				'coupon_premium'         => $new_coupon_id,
				'current_coupon_percent' => $new_percent,
			),
			array( 'id' => $np_id ),
			array( '%s', '%d' ),
			array( '%d' )
		);

		self::send_tier_upgrade_email( $np_id, $new_percent, $new_coupon_id );
	}

	/**
	 * Email notification nouveau palier parrainage.
	 *
	 * @param int    $np_id          ID nonprofit.
	 * @param int    $new_percent    Nouveau pourcentage.
	 * @param string $new_coupon_id  Nouveau coupon Stripe.
	 * @return void
	 */
	public static function send_tier_upgrade_email( $np_id, $new_percent, $new_coupon_id ) {
		global $wpdb;
		$np_id = (int) $np_id;

		$org = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}avi_nonprofits WHERE id = %d LIMIT 1", $np_id ),
			ARRAY_A
		);
		if ( ! is_array( $org ) ) {
			return;
		}

		$email = avi_nonprofit_resolve_user_email( $org );
		if ( $email === '' ) {
			return;
		}

		$lang = get_option( 'avi_np_applicant_lang_' . $np_id, 'en' );
		if ( ! in_array( $lang, array( 'en', 'fr', 'es' ), true ) ) {
			$lang = 'en';
		}
		$slug = isset( $org['slug'] ) ? (string) $org['slug'] : '';
		$ref_url = home_url( '/?ref=np_' . $slug );

		$subjects = array(
			'en' => '🎉 Your discount just increased to ' . $new_percent . '%!',
			'fr' => '🎉 Votre réduction passe à ' . $new_percent . '% !',
			'es' => '🎉 ¡Tu descuento sube al ' . $new_percent . '%!',
		);

		$milestone_en = ( $new_percent === 100 )
			? "You now have 100% free access to all premium options — thank you for your incredible support!\n\n"
			: "Keep referring local businesses to reach the next level!\n\n";
		$milestone_fr = ( $new_percent === 100 )
			? "Vous bénéficiez maintenant de 100% de réduction sur toutes les options premium — merci pour votre soutien extraordinaire !\n\n"
			: "Continuez à référer des entreprises locales pour atteindre le palier suivant !\n\n";
		$milestone_es = ( $new_percent === 100 )
			? "¡Ahora tienes acceso 100% gratuito a todas las opciones premium — gracias por tu increíble apoyo!\n\n"
			: "¡Sigue refiriendo empresas locales para alcanzar el siguiente nivel!\n\n";

		$bodies = array(
			'en' => "Hello,\n\nCongratulations! Your organization has reached a new referral milestone.\n\nYour new premium discount: {$new_percent}% off\nYour new code: {$new_coupon_id}\n\nUse this code for Guardian Mode, Growth Radar and Bundles in your dashboard.\n\n{$milestone_en}Your referral link:\n{$ref_url}\n\nThe Avision Team\ncontact@avision.org",
			'fr' => "Bonjour,\n\nFélicitations ! Votre organisation a atteint un nouveau palier de parrainage.\n\nVotre nouvelle réduction premium : {$new_percent}%\nVotre nouveau code : {$new_coupon_id}\n\nUtilisez ce code pour Guardian Mode, Growth Radar et Bundles dans votre dashboard.\n\n{$milestone_fr}Votre lien de parrainage :\n{$ref_url}\n\nL'équipe Avision\ncontact@avision.org",
			'es' => "Hola,\n\n¡Felicidades! Tu organización ha alcanzado un nuevo nivel de referidos.\n\nTu nuevo descuento premium: {$new_percent}%\nTu nuevo código: {$new_coupon_id}\n\nUsa este código para Guardian Mode, Growth Radar y Bundles en tu dashboard.\n\n{$milestone_es}Tu enlace de referido:\n{$ref_url}\n\nEl equipo de Avision\ncontact@avision.org",
		);

		wp_mail(
			$email,
			isset( $subjects[ $lang ] ) ? $subjects[ $lang ] : $subjects['en'],
			isset( $bodies[ $lang ] ) ? $bodies[ $lang ] : $bodies['en'],
			array( 'From: Avision <no-reply@avision.org>' )
		);
	}

	/**
	 * Cron mensuel — calcul et enregistrement des payouts dons.
	 *
	 * @return void
	 */
	public static function process_monthly_payouts() {
		global $wpdb;

		$period = date( 'Y-m', strtotime( 'last month' ) );

		$referrals = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT r.nonprofit_id,
					SUM(r.amount_paid) as total_revenue,
					SUM(r.donation_amount) as total_donations,
					n.payout_method,
					n.payout_details,
					n.org_name,
					n.slug
				FROM {$wpdb->prefix}avi_nonprofit_referrals r
				JOIN {$wpdb->prefix}avi_nonprofits n ON r.nonprofit_id = n.id
				WHERE DATE_FORMAT(r.created_at, '%%Y-%%m') = %s
					AND r.paid_out = 0
					AND r.amount_paid > 0
				GROUP BY r.nonprofit_id",
				$period
			),
			ARRAY_A
		);

		if ( empty( $referrals ) ) {
			return;
		}

		$total_donations_sum = 0.0;

		foreach ( $referrals as $row ) {
			$np_id     = (int) $row['nonprofit_id'];
			$total_rev = (float) $row['total_revenue'];
			$total_don = (float) $row['total_donations'];
			$method    = isset( $row['payout_method'] ) ? (string) $row['payout_method'] : 'stripe';

			if ( $total_don <= 0 ) {
				$total_don = round( $total_rev * 0.5, 2 );
			}

			if ( $total_don <= 0 ) {
				continue;
			}

			$total_donations_sum += $total_don;

			$wpdb->insert(
				$wpdb->prefix . 'avi_nonprofit_payouts',
				array(
					'nonprofit_id'           => $np_id,
					'period_month'           => $period,
					'total_referral_revenue' => $total_rev,
					'donation_amount'        => $total_don,
					'payout_method'          => $method,
					'payout_status'          => 'pending',
					'created_at'             => current_time( 'mysql' ),
				),
				array( '%d', '%s', '%f', '%f', '%s', '%s', '%s' )
			);

			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$wpdb->prefix}avi_nonprofit_referrals SET paid_out = 1 WHERE nonprofit_id = %d AND DATE_FORMAT(created_at, '%%Y-%%m') = %s AND paid_out = 0",
					$np_id,
					$period
				)
			);

			self::send_payout_recap_email( $np_id, $period, $total_don, $method );

			if ( $method !== 'stripe' ) {
				wp_mail(
					'toniosbh@gmail.com',
					'[Avision] Payout nonprofit à traiter — ' . $row['org_name'],
					"Organisation: " . $row['org_name']
						. "\nMéthode: " . $method
						. "\nMontant: " . $total_don . " EUR"
						. "\nPériode: " . $period
						. "\nDétails: " . ( isset( $row['payout_details'] ) ? (string) $row['payout_details'] : '' )
						. "\n\n" . admin_url( 'admin.php?page=' . self::PAGE_SLUG ),
					array( 'From: Avision <no-reply@avision.org>' )
				);
			}
		}

		wp_mail(
			'toniosbh@gmail.com',
			'[Avision] Récap payouts nonprofits — ' . $period,
			count( $referrals ) . " association(s) à payer ce mois.\n"
				. 'Total dons: ' . $total_donations_sum . " EUR\n"
				. 'Voir le détail: ' . admin_url( 'admin.php?page=' . self::PAGE_SLUG ),
			array( 'From: Avision <no-reply@avision.org>' )
		);
	}

	/**
	 * Email mensuel récap don à l'association.
	 *
	 * @param int    $np_id   ID nonprofit.
	 * @param string $period  Période Y-m.
	 * @param float  $amount  Montant don.
	 * @param string $method  Méthode payout.
	 * @return void
	 */
	public static function send_payout_recap_email( $np_id, $period, $amount, $method ) {
		global $wpdb;
		$np_id = (int) $np_id;

		$org = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}avi_nonprofits WHERE id = %d LIMIT 1", $np_id ),
			ARRAY_A
		);
		if ( ! is_array( $org ) ) {
			return;
		}

		$email = avi_nonprofit_resolve_user_email( $org );
		if ( $email === '' ) {
			return;
		}

		$lang = get_option( 'avi_np_applicant_lang_' . $np_id, 'en' );
		if ( ! in_array( $lang, array( 'en', 'fr', 'es' ), true ) ) {
			$lang = 'en';
		}

		$method_labels = array(
			'en' => array(
				'stripe' => '2-3 business days via Stripe',
				'paypal' => '7-10 business days via PayPal',
				'bank'   => '14-21 business days via bank transfer',
			),
			'fr' => array(
				'stripe' => '2-3 jours ouvrés via Stripe',
				'paypal' => '7-10 jours ouvrés via PayPal',
				'bank'   => '14-21 jours ouvrés par virement',
			),
			'es' => array(
				'stripe' => '2-3 días hábiles vía Stripe',
				'paypal' => '7-10 días hábiles vía PayPal',
				'bank'   => '14-21 días hábiles por transferencia',
			),
		);

		$method_label = $method;
		if ( isset( $method_labels[ $lang ][ $method ] ) ) {
			$method_label = $method_labels[ $lang ][ $method ];
		}

		$dashboard_url = trailingslashit( home_url( '/premium-dashboard/' ) );

		$subjects = array(
			'en' => 'Your Avision donation — ' . $period,
			'fr' => 'Votre don Avision — ' . $period,
			'es' => 'Tu donación Avision — ' . $period,
		);

		$bodies = array(
			'en' => "Hello,\n\nThank you for your ambassador work this month!\n\nDonation for {$period}: {$amount} EUR\nPayment method: {$method_label}\n\nThis amount represents 50% of the revenue generated by the businesses you referred to Avision.\n\nYour dashboard: {$dashboard_url}\n\nThe Avision Team\ncontact@avision.org",
			'fr' => "Bonjour,\n\nMerci pour votre travail d'ambassadeur ce mois-ci !\n\nDon pour {$period} : {$amount} EUR\nMode de versement : {$method_label}\n\nCe montant représente 50% des revenus générés par les entreprises que vous avez référées à Avision.\n\nVotre dashboard : {$dashboard_url}\n\nL'équipe Avision\ncontact@avision.org",
			'es' => "Hola,\n\n¡Gracias por tu trabajo de embajador este mes!\n\nDonación por {$period}: {$amount} EUR\nMétodo de pago: {$method_label}\n\nEste importe representa el 50% de los ingresos generados por las empresas que referiste a Avision.\n\nTu dashboard: {$dashboard_url}\n\nEl equipo de Avision\ncontact@avision.org",
		);

		wp_mail(
			$email,
			isset( $subjects[ $lang ] ) ? $subjects[ $lang ] : $subjects['en'],
			isset( $bodies[ $lang ] ) ? $bodies[ $lang ] : $bodies['en'],
			array( 'From: Avision <no-reply@avision.org>' )
		);
	}
}

/**
 * @param int $np_id ID avi_nonprofits.
 * @return string|WP_Error ID coupon premium Stripe.
 */
function avi_nonprofit_generate_coupons( $np_id ) {
	global $wpdb;
	$np_id = (int) $np_id;
	if ( $np_id <= 0 ) {
		return new WP_Error( 'invalid_np', 'Invalid nonprofit id' );
	}

	$table = $wpdb->prefix . 'avi_nonprofits';
	$org   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $np_id ), ARRAY_A );
	if ( ! is_array( $org ) ) {
		return new WP_Error( 'not_found', 'Nonprofit not found' );
	}

	$org_name = isset( $org['org_name'] ) ? (string) $org['org_name'] : 'ORG';
	$slug_raw = sanitize_title( $org_name );
	$slug     = strtoupper( substr( preg_replace( '/[^A-Z0-9]/', '', strtoupper( $slug_raw ) ), 0, 10 ) );
	if ( $slug === '' ) {
		$slug = 'NPORG';
	}
	$code = strtoupper( wp_generate_password( 6, false, false ) );

	$coupon_premium = 'NP-' . $slug . '-' . $code . '-50';

	if ( ! function_exists( 'avi_stripe_s10b_resolve_secret_key' ) && defined( 'AVISION_PATH' ) ) {
		$stripe_file = AVISION_PATH . 'includes/avi-stripe-s10b.php';
		if ( is_readable( $stripe_file ) ) {
			require_once $stripe_file;
		}
	}

	if ( ! function_exists( 'avi_stripe_s10b_resolve_secret_key' ) ) {
		return new WP_Error( 'stripe_missing', 'Stripe module not loaded' );
	}

	$secret_key = avi_stripe_s10b_resolve_secret_key();
	if ( $secret_key === '' ) {
		return new WP_Error( 'stripe_key', 'Stripe secret key not configured' );
	}

	$premium_ok = avi_nonprofit_stripe_create_coupon( $secret_key, $coupon_premium, 50, 'forever' );
	if ( is_wp_error( $premium_ok ) ) {
		return $premium_ok;
	}

	$wpdb->update(
		$table,
		array(
			'coupon_premium' => $coupon_premium,
		),
		array( 'id' => $np_id ),
		array( '%s' ),
		array( '%d' )
	);

	return $coupon_premium;
}

/**
 * @param string $secret_key  Clé Stripe secrète.
 * @param string $coupon_id   ID coupon Stripe.
 * @param int    $percent_off Pourcentage de réduction.
 * @param string $duration    once|forever|repeating.
 * @return true|WP_Error
 */
function avi_nonprofit_stripe_create_coupon( $secret_key, $coupon_id, $percent_off, $duration = 'forever' ) {
	$coupon_id = preg_replace( '/[^A-Za-z0-9_-]/', '', (string) $coupon_id );
	if ( $coupon_id === '' ) {
		return new WP_Error( 'coupon_id', 'Invalid coupon id' );
	}

	$body_params = array(
		'id'          => $coupon_id,
		'percent_off' => (int) $percent_off,
		'duration'    => $duration,
		'name'        => 'Nonprofit ' . (int) $percent_off . '% — ' . $coupon_id,
	);
	if ( $duration === 'once' ) {
		$body_params['max_redemptions'] = 1;
	}

	$response = wp_remote_post(
		'https://api.stripe.com/v1/coupons',
		array(
			'timeout' => 25,
			'headers' => array(
				'Authorization' => 'Bearer ' . $secret_key,
			),
			'body'    => $body_params,
		)
	);

	if ( is_wp_error( $response ) ) {
		return $response;
	}

	$code = (int) wp_remote_retrieve_response_code( $response );
	$body = json_decode( (string) wp_remote_retrieve_body( $response ), true );
	if ( $code >= 200 && $code < 300 ) {
		return true;
	}

	$msg = is_array( $body ) && isset( $body['error']['message'] ) ? (string) $body['error']['message'] : ( 'HTTP ' . $code );
	return new WP_Error( 'stripe_coupon', $msg );
}

/**
 * @param int $np_id ID avi_nonprofits.
 * @return void
 */
function avi_nonprofit_send_approval_email( $np_id ) {
	global $wpdb;
	$np_id = (int) $np_id;
	$table = $wpdb->prefix . 'avi_nonprofits';
	$org   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $np_id ), ARRAY_A );
	if ( ! is_array( $org ) ) {
		return;
	}

	$email = avi_nonprofit_resolve_user_email( $org );
	if ( $email === '' ) {
		return;
	}

	$coupon_premium = isset( $org['coupon_premium'] ) ? (string) $org['coupon_premium'] : '';
	$slug           = isset( $org['slug'] ) ? (string) $org['slug'] : '';
	$lang           = get_option( 'avi_np_applicant_lang_' . $np_id, 'en' );
	if ( ! in_array( $lang, array( 'en', 'fr', 'es' ), true ) ) {
		$lang = 'en';
	}

	$np_user_id = (int) $wpdb->get_var(
		$wpdb->prepare(
			"SELECT user_id FROM {$wpdb->prefix}avi_nonprofits WHERE id = %d LIMIT 1",
			$np_id
		)
	);
	$profile_uuid = '';
	if ( $np_user_id > 0 ) {
		$profile_uuid = (string) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT uuid FROM {$wpdb->prefix}avi_saas_profiles WHERE user_id = %d ORDER BY id DESC LIMIT 1",
				$np_user_id
			)
		);
	}
	$dashboard_url = home_url( '/premium-dashboard/' );
	if ( $profile_uuid !== '' ) {
		$dashboard_url = add_query_arg( 'pid', $profile_uuid, $dashboard_url );
	}
	$reset_url = home_url( '/wp-login.php?action=lostpassword' );

	$subjects = array(
		'en' => 'Your Nonprofit Access — Welcome to Avision ✓',
		'fr' => 'Votre accès association — Bienvenue chez Avision ✓',
		'es' => 'Tu acceso ONG — Bienvenida a Avision ✓',
	);
	$bodies = array(
		'en' => "Hello,\n\nYour nonprofit status has been verified.\nWelcome to the Avision Nonprofit Program!\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nYOUR ACCOUNT ACCESS\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard: {dashboard_url}\nLogin: {email}\nPassword: the one you chose during registration\n\nForgot your password?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nYOUR PREMIUM DISCOUNT CODE\n━━━━━━━━━━━━━━━━━━━━━━━━\n\n{coupon_premium}\n\n50% off Guardian Mode, Growth Radar and Bundles.\nUse it in your dashboard → 'Boost your visibility'\nwhen Stripe opens at checkout.\n\nThis code is personal and unique to your organization. It can only be activated once.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nAMBASSADOR PROGRAM\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nYour referral link:\nhttps://avision.org/?ref=np_{slug}\n\nEvery business you refer earns your organization 50% of the revenue as a donation from Avision.\nYour discount grows with each referral:\n  1–10  → 70% off\n  11–20 → 80% off\n  21–29 → 90% off\n  30+   → 100% free\n\nThe Avision Team\ncontact@avision.org",
		'fr' => "Bonjour,\n\nVotre statut d'association a été vérifié.\nBienvenue dans le programme Avision pour associations !\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nACCÈS À VOTRE COMPTE\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard : {dashboard_url}\nIdentifiant : {email}\nMot de passe : celui que vous avez choisi lors de votre inscription\n\nMot de passe oublié ?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nVOTRE CODE DE RÉDUCTION PREMIUM\n━━━━━━━━━━━━━━━━━━━━━━━━\n\n{coupon_premium}\n\n50% de réduction sur Guardian Mode, Growth Radar et les Bundles.\nUtilisez-le dans votre dashboard → 'Boostez votre visibilité' à l'ouverture de Stripe.\n\nCe code est personnel et unique à votre organisation. Il ne peut être activé qu'une fois.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nPROGRAMME AMBASSADEUR\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nVotre lien de parrainage :\nhttps://avision.org/?ref=np_{slug}\n\nChaque entreprise que vous référez rapporte 50% du montant en dons à votre association.\nVotre réduction évolue avec vos parrainages :\n  1–10  → 70% de réduction\n  11–20 → 80% de réduction\n  21–29 → 90% de réduction\n  30+   → 100% offert\n\nL'équipe Avision\ncontact@avision.org",
		'es' => "Hola,\n\nTu estado de ONG ha sido verificado.\n¡Bienvenida al programa Avision para organizaciones!\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nACCESO A TU CUENTA\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nDashboard: {dashboard_url}\nUsuario: {email}\nContraseña: la que elegiste al registrarte\n\n¿Olvidaste tu contraseña?\n{reset_url}\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nTU CÓDIGO DE DESCUENTO PREMIUM\n━━━━━━━━━━━━━━━━━━━━━━━━\n\n{coupon_premium}\n\n50% de descuento en Guardian Mode, Growth Radar y Bundles.\nÚsalo en tu dashboard → 'Impulsa tu visibilidad' cuando se abra Stripe al pagar.\n\nEste código es personal y único para tu organización. Solo puede activarse una vez.\n\n━━━━━━━━━━━━━━━━━━━━━━━━\nPROGRAMA EMBAJADOR\n━━━━━━━━━━━━━━━━━━━━━━━━\n\nTu enlace de referido:\nhttps://avision.org/?ref=np_{slug}\n\nCada empresa que refieras genera una donación del 50% para tu organización.\nTu descuento crece con cada referido:\n  1–10  → 70% descuento\n  11–20 → 80% descuento\n  21–29 → 90% descuento\n  30+   → 100% gratuito\n\nEl equipo de Avision\ncontact@avision.org",
	);

	$subject = isset( $subjects[ $lang ] ) ? $subjects[ $lang ] : $subjects['en'];
	$body    = isset( $bodies[ $lang ] ) ? $bodies[ $lang ] : $bodies['en'];
	$body    = str_replace(
		array( '{dashboard_url}', '{email}', '{reset_url}', '{coupon_premium}', '{slug}' ),
		array( $dashboard_url, $email, $reset_url, $coupon_premium, $slug ),
		$body
	);

	wp_mail( $email, $subject, $body );
}

/**
 * @param int    $np_id ID avi_nonprofits.
 * @param string $reason Raison du rejet.
 * @param int    $redirect_btoc Rediriger vers B2C.
 * @return void
 */
function avi_nonprofit_send_rejection_email( $np_id, $reason, $redirect_btoc ) {
	global $wpdb;
	$np_id = (int) $np_id;
	$table = $wpdb->prefix . 'avi_nonprofits';
	$org   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $np_id ), ARRAY_A );
	if ( ! is_array( $org ) ) {
		return;
	}

	$email = avi_nonprofit_resolve_user_email( $org );
	if ( $email === '' ) {
		return;
	}

	$org_name = isset( $org['org_name'] ) ? (string) $org['org_name'] : '';
	$subject  = 'Your Nonprofit Application — Update';
	$body     = "Hello,\n\n"
		. "Thank you for applying to the Avision nonprofit program for " . $org_name . ".\n\n"
		. "We are unable to approve your application at this time:\n\n"
		. $reason . "\n\n";

	if ( (int) $redirect_btoc === 1 ) {
		$body .= "You may continue with our standard plans here: " . trailingslashit( home_url( '/pricing/' ) ) . "\n\n";
	}

	$body .= "— Avision Team";
	wp_mail( $email, $subject, $body );
}

/**
 * Vérifie automatiquement EIN / CRA / RNA via le backend FastAPI.
 *
 * @param int    $np_id      ID avi_nonprofits.
 * @param string $country    Code pays.
 * @param string $org_number Numéro d'enregistrement.
 * @param string $org_name   Nom organisation.
 * @return string Statut verification_status.
 */
function avi_nonprofit_auto_verify( $np_id, $country, $org_number, $org_name ) {
	global $wpdb;
	$table = $wpdb->prefix . 'avi_nonprofits';
	$np_id = (int) $np_id;

	$backend_url  = 'http://localhost:8001/api/v1/nonprofit/verify';
	$internal_key = defined( 'AVISION_INTERNAL_API_KEY' ) ? AVISION_INTERNAL_API_KEY : '';

	$response = wp_remote_post(
		$backend_url,
		array(
			'timeout' => 15,
			'headers' => array(
				'Content-Type'   => 'application/json',
				'X-Internal-Key' => $internal_key,
			),
			'body'    => wp_json_encode(
				array(
					'country'    => $country,
					'org_number' => $org_number,
					'org_name'   => $org_name,
				)
			),
		)
	);

	if ( is_wp_error( $response ) ) {
		$wpdb->update(
			$table,
			array(
				'verification_status' => 'manual',
				'verification_source' => 'api_unavailable',
			),
			array( 'id' => $np_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		$status = 'manual';
	} else {

		$body   = wp_remote_retrieve_body( $response );
		$result = json_decode( $body, true );
		$status = isset( $result['status'] ) ? (string) $result['status'] : 'manual';
		$source = isset( $result['source'] ) ? (string) $result['source'] : 'unknown';

		$wpdb->update(
			$table,
			array(
				'verification_status' => $status,
				'verification_source' => $source,
			),
			array( 'id' => $np_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	if ( $status === 'verified' ) {
		if ( function_exists( 'avi_nonprofit_generate_coupons' ) ) {
			avi_nonprofit_generate_coupons( $np_id );
		}
	}

	return $status;
}

/**
 * Traite une candidature nonprofit depuis le formulaire public.
 *
 * @param array<string, string> $data Champs formulaire.
 * @return array<string, int>|WP_Error
 */
function avi_nonprofit_handle_application( $data ) {
	$org_name   = isset( $data['org_name'] ) ? (string) $data['org_name'] : '';
	$country    = isset( $data['country'] ) ? (string) $data['country'] : '';
	$org_number = isset( $data['org_number'] ) ? (string) $data['org_number'] : '';
	$gbp_name   = isset( $data['gbp_name'] ) ? (string) $data['gbp_name'] : '';
	$website    = isset( $data['website'] ) ? (string) $data['website'] : '';
	$email      = isset( $data['email'] ) ? (string) $data['email'] : '';
	$lang       = isset( $data['lang'] ) ? (string) $data['lang'] : 'en';

	if ( ! $org_name || ! $country || ! $org_number || ! $gbp_name || ! $email ) {
		return new WP_Error( 'missing_fields', 'Missing required fields' );
	}
	if ( ! is_email( $email ) ) {
		return new WP_Error( 'invalid_email', 'Invalid email address' );
	}

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

	$existing = $wpdb->get_var(
		$wpdb->prepare(
			"SELECT id FROM `{$table}` WHERE org_name = %s AND country_code = %s LIMIT 1",
			$org_name,
			$country
		)
	);
	if ( $existing ) {
		return new WP_Error( 'duplicate', 'An application for this organization already exists.' );
	}

	$slug_base = sanitize_title( $org_name );
	$slug      = $slug_base;
	$i         = 1;
	while ( $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$table}` WHERE slug = %s LIMIT 1", $slug ) ) ) {
		$slug = $slug_base . '-' . $i;
		$i++;
	}

	$inserted = $wpdb->insert(
		$table,
		array(
			'org_name'             => $org_name,
			'org_number'           => $org_number,
			'country_code'         => $country,
			'verification_status'  => 'pending',
			'verification_source'  => 'form',
			'gbp_category_matched' => $gbp_name,
			'slug'                 => $slug,
			'coupon_access'        => '',
			'coupon_premium'       => '',
			'created_at'           => current_time( 'mysql' ),
		),
		array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
	);

	if ( ! $inserted ) {
		return new WP_Error( 'db_error', 'Database error. Please try again.' );
	}

	$np_id = (int) $wpdb->insert_id;

	update_option( 'avi_np_applicant_email_' . $np_id, $email, false );
	if ( $website !== '' ) {
		update_option( 'avi_np_applicant_website_' . $np_id, $website, false );
	}
	if ( $lang !== '' ) {
		update_option( 'avi_np_applicant_lang_' . $np_id, $lang, false );
	}

	$avi_np_verification = avi_nonprofit_auto_verify( $np_id, $country, $org_number, $org_name );

	if ( $avi_np_verification === 'manual' ) {
		if ( function_exists( 'avi_nonprofit_send_admin_alert' ) ) {
			avi_nonprofit_send_admin_alert( $np_id, 'new_application' );
		}
	}

	// Email confirmation applicant.
	$applicant_email = sanitize_email( $email );
	$lang_val        = sanitize_text_field( $lang );

	$subjects = array(
		'en' => 'Your Nonprofit Application — Received ✓',
		'fr' => 'Votre candidature association — Reçue ✓',
		'es' => 'Tu solicitud ONG — Recibida ✓',
	);
	$bodies = array(
		'en' => "Hello,\n\nThank you for applying to the Avision Nonprofit Program.\n\nWe have received your application for:\n{org_name}\n\nYour account is being created. You will receive shortly an email with your login credentials and your 50% discount code for premium options.\n\nThe Avision Team\ncontact@avision.org",
		'fr' => "Bonjour,\n\nMerci pour votre candidature au programme Avision pour associations.\n\nNous avons bien reçu votre dossier pour :\n{org_name}\n\nVotre compte est en cours de création. Vous recevrez sous peu un email avec vos identifiants de connexion et votre code de réduction de 50% sur les options premium.\n\nL'équipe Avision\ncontact@avision.org",
		'es' => "Hola,\n\nGracias por solicitar el programa Avision para ONG.\n\nHemos recibido tu solicitud para:\n{org_name}\n\nTu cuenta está siendo creada. Recibirás en breve un email con tus credenciales de acceso y tu código de descuento del 50% en opciones premium.\n\nEl equipo de Avision\ncontact@avision.org",
	);

	$subject = isset( $subjects[ $lang_val ] ) ? $subjects[ $lang_val ] : $subjects['en'];
	$body    = isset( $bodies[ $lang_val ] ) ? $bodies[ $lang_val ] : $bodies['en'];
	$body    = str_replace( '{org_name}', $org_name, $body );

	wp_mail(
		$applicant_email,
		$subject,
		$body,
		array( 'From: Avision <no-reply@avision.org>' )
	);

	return array( 'np_id' => $np_id );
}

/**
 * @param int    $np_id ID avi_nonprofits.
 * @param string $trigger new_application | api_unavailable | category_mismatch.
 * @return void
 */
function avi_nonprofit_send_admin_alert( $np_id, $trigger ) {
	global $wpdb;
	$np_id = (int) $np_id;
	$table = $wpdb->prefix . 'avi_nonprofits';
	$org   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $np_id ), ARRAY_A );
	if ( ! is_array( $org ) ) {
		return;
	}

	$admin_url = admin_url( 'admin.php?page=' . Avision_Nonprofit_Admin::PAGE_SLUG );
	$org_name  = isset( $org['org_name'] ) ? (string) $org['org_name'] : '';
	$country   = isset( $org['country_code'] ) ? (string) $org['country_code'] : '';
	$org_num   = isset( $org['org_number'] ) ? (string) $org['org_number'] : '';
	$gbp_cat   = isset( $org['gbp_category_matched'] ) ? (string) $org['gbp_category_matched'] : '';

	$subject = '[Avision Nonprofit] Action required — ' . sanitize_text_field( (string) $trigger );
	$body    = "Nonprofit admin action required.\n\n"
		. "Trigger: " . $trigger . "\n"
		. "Org: " . $org_name . "\n"
		. "Country: " . $country . "\n"
		. "Org number: " . $org_num . "\n"
		. "GBP category: " . $gbp_cat . "\n\n"
		. "Review: " . $admin_url . "\n";

	$recipients = array( 'toniosbh@gmail.com', 'contact@avision.org' );
	foreach ( $recipients as $to ) {
		if ( is_email( $to ) ) {
			wp_mail( $to, $subject, $body );
		}
	}
}

/**
 * @param array<string, mixed> $org Ligne DB.
 * @return string
 */
function avi_nonprofit_resolve_user_email( $org ) {
	$user_id = isset( $org['user_id'] ) ? (int) $org['user_id'] : 0;
	if ( $user_id > 0 ) {
		$user = get_userdata( $user_id );
		if ( $user instanceof WP_User && is_email( $user->user_email ) ) {
			return sanitize_email( $user->user_email );
		}
	}
	if ( isset( $org['id'] ) ) {
		$opt = get_option( 'avi_np_applicant_email_' . (int) $org['id'], '' );
		if ( is_email( $opt ) ) {
			return sanitize_email( $opt );
		}
	}
	return '';
}
