<?php
/**
 * Journal des interactions chat AVI + correctifs admin injectés dans le system prompt.
 *
 * @package Avision_Dashboard
 */

namespace Avision;

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

/**
 * Table : {$wpdb->prefix}avi_ai_interactions
 */
class Avision_AI_Interactions {

	const TABLE_SLUG = 'avi_ai_interactions';

	const AJAX_SAVE    = 'avi_save_ai_interaction_correction';
	const AJAX_IMPORT  = 'avi_import_chat_logs_interactions';
	const AJAX_DISMISS = 'avi_dismiss_ai_interaction';

	const PROMPT_MAX_ITEMS = 25;

	/** @var int 0 = à traiter (visible par défaut), 1 = ignoré / traité (masqué par défaut). */
	const STATUS_PENDING = 0;

	/** @var int */
	const STATUS_DONE = 1;

	/** @var int Lignes par page (admin Interactions). */
	const ADMIN_PER_PAGE = 15;

	/** @var string Filtre admin KC : uniquement les lignes à traiter (status = 0). */
	const ADMIN_FILTER_PENDING = 'pending';

	/** @var string Filtre admin KC : uniquement traitées (status = 1). */
	const ADMIN_FILTER_DONE = 'done';

	/** @var string Filtre admin KC : toutes les lignes. */
	const ADMIN_FILTER_ALL = 'all';

	/** @var string Contexte admin KC : interactions client premium (JSON sans marqueur agence). */
	const ADMIN_DASHBOARD_CONTEXT_B2C = 'b2c';

	/** @var string Contexte admin KC : interactions console agence / Fleet (JSON dashboard_type ou user_type b2b, ou fleet_console). */
	const ADMIN_DASHBOARD_CONTEXT_B2B = 'b2b';

	/**
	 * Motifs LIKE sur la colonne question : présence JSON B2B (clés dashboard_type / user_type, ou console flotte).
	 * Le contexte JSON est stocké dans question (pas de colonne séparée) ; parse_stored_question_for_admin n’altère pas ce filtrage SQL.
	 *
	 * @return array<int, string>
	 */
	private static function admin_dashboard_context_b2b_question_like_patterns() {
		return array(
			'%"dashboard_type":"b2b"%',
			'%"dashboard_type": "b2b"%',
			'%"user_type":"b2b"%',
			'%"user_type": "b2b"%',
			'%"fleet_console": true%',
		);
	}

	/**
	 * Bootstrap hooks admin / AJAX.
	 */
	public static function init() {
		add_action( 'wp_ajax_' . self::AJAX_SAVE, array( __CLASS__, 'ajax_save_correction' ) );
		add_action( 'wp_ajax_' . self::AJAX_IMPORT, array( __CLASS__, 'ajax_import_chat_logs' ) );
		add_action( 'wp_ajax_' . self::AJAX_DISMISS, array( __CLASS__, 'ajax_dismiss_interaction' ) );
	}

	/**
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_SLUG;
	}

	/**
	 * Crée la table si besoin (dbDelta).
	 */
	public static function ensure_table() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
		if ( $exists ) {
			self::migrate_add_status_column();
			return;
		}
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();
		$sql     = 'CREATE TABLE ' . $table . " (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			profile_id VARCHAR(64) DEFAULT NULL,
			level_context VARCHAR(50) DEFAULT NULL,
			question LONGTEXT NOT NULL,
			answer LONGTEXT NOT NULL,
			admin_correction LONGTEXT DEFAULT NULL,
			correction_updated_at DATETIME DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			chat_log_id BIGINT UNSIGNED DEFAULT NULL,
			status TINYINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY idx_ai_inter_created (created_at),
			KEY idx_ai_inter_user (user_id),
			KEY idx_ai_inter_profile (profile_id),
			KEY idx_ai_inter_chat_log (chat_log_id),
			KEY idx_ai_inter_status (status)
		) ENGINE=InnoDB $charset;";
		dbDelta( $sql );
		self::migrate_add_status_column();
	}

	/**
	 * Ajoute la colonne status aux installations existantes (avant cette version).
	 */
	private static function migrate_add_status_column() {
		global $wpdb;
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_var( "SHOW COLUMNS FROM `{$table}` LIKE 'status'" );
		if ( $col ) {
			return;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			"ALTER TABLE `{$table}` ADD COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER chat_log_id, ADD KEY idx_ai_inter_status (status)"
		);
	}

	/**
	 * Bloc texte à ajouter au system prompt (OpenRouter / FastAPI).
	 *
	 * @return string
	 */
	public static function get_prompt_addon() {
		global $wpdb;
		self::ensure_table();
		$table = self::table_name();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT id, admin_correction FROM `{$table}` WHERE COALESCE(status, 0) = %d AND admin_correction IS NOT NULL AND TRIM(admin_correction) <> '' ORDER BY correction_updated_at DESC, created_at DESC LIMIT %d",
			self::STATUS_PENDING,
			self::PROMPT_MAX_ITEMS
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) || $rows === array() ) {
			return '';
		}
		$parts = array();
		foreach ( $rows as $row ) {
			$id   = isset( $row['id'] ) ? (int) $row['id'] : 0;
			$note = isset( $row['admin_correction'] ) ? trim( (string) $row['admin_correction'] ) : '';
			if ( $note === '' ) {
				continue;
			}
			$parts[] = sprintf( "[Correctif admin #%d]\n%s", $id, $note );
		}
		if ( $parts === array() ) {
			return '';
		}
		return "--- CORRECTIFS ADMIN AVISION (à respecter strictement) ---\n" . implode( "\n\n", $parts );
	}

	/**
	 * Enregistre un échange après réponse FastAPI réussie.
	 *
	 * @param int         $user_id   ID WP (0 si invité).
	 * @param string|null $profile_id Identifiant profil SaaS si connu.
	 * @param string      $question Message utilisateur.
	 * @param string      $answer   Réponse AVI.
	 * @param string|null $level    Contexte niveau.
	 */
	public static function log_exchange( $user_id, $profile_id, $question, $answer, $level = null ) {
		global $wpdb;
		$question = is_string( $question ) ? $question : '';
		$answer   = is_string( $answer ) ? $answer : '';
		if ( $question === '' || $answer === '' ) {
			return;
		}
		self::ensure_table();
		$table = self::table_name();
		if ( strlen( $question ) > 65535 ) {
			$question = substr( $question, 0, 65500 ) . '…';
		}
		if ( strlen( $answer ) > 65535 ) {
			$answer = substr( $answer, 0, 65500 ) . '…';
		}
		$pid = is_string( $profile_id ) && $profile_id !== '' ? substr( sanitize_text_field( $profile_id ), 0, 64 ) : null;
		$lvl = is_string( $level ) && $level !== '' ? substr( sanitize_text_field( $level ), 0, 50 ) : null;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$table,
			array(
				'user_id'    => max( 0, (int) $user_id ),
				'profile_id' => $pid,
				'level_context' => $lvl,
				'question'   => $question,
				'answer'     => $answer,
			),
			array( '%d', '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * @param int $limit Nombre max de lignes.
	 * @return array<int, array<string, mixed>>
	 */
	public static function fetch_recent( $limit = 50 ) {
		global $wpdb;
		self::ensure_table();
		$table = self::table_name();
		$limit = max( 1, min( 200, (int) $limit ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"SELECT id, user_id, profile_id, level_context, question, answer, admin_correction, correction_updated_at, created_at, status FROM `{$table}` ORDER BY created_at DESC LIMIT %d",
			$limit
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Filtre statut pour la liste admin (paramètre $_GET `avi_inter_status` ou legacy `avi_inter_all=1`).
	 *
	 * @return string self::ADMIN_FILTER_PENDING|ADMIN_FILTER_DONE|ADMIN_FILTER_ALL
	 */
	public static function normalize_admin_status_filter_from_request() {
		if ( isset( $_GET['avi_inter_status'] ) ) {
			$s = sanitize_key( wp_unslash( (string) $_GET['avi_inter_status'] ) );
			if ( in_array( $s, array( self::ADMIN_FILTER_PENDING, self::ADMIN_FILTER_DONE, self::ADMIN_FILTER_ALL ), true ) ) {
				return $s;
			}
			return self::ADMIN_FILTER_PENDING;
		}
		if ( isset( $_GET['avi_inter_all'] ) && (string) wp_unslash( $_GET['avi_inter_all'] ) === '1' ) {
			return self::ADMIN_FILTER_ALL;
		}
		return self::ADMIN_FILTER_PENDING;
	}

	/**
	 * Filtre optionnel par ID utilisateur WordPress (colonne user_id de la table).
	 *
	 * @return int >0 si filtre actif, 0 pour toutes les lignes.
	 */
	public static function normalize_admin_user_id_filter_from_request() {
		if ( ! isset( $_GET['avi_inter_user'] ) ) {
			return 0;
		}
		$raw = wp_unslash( (string) $_GET['avi_inter_user'] );
		$raw = trim( $raw );
		if ( $raw === '' || $raw === '0' || strtolower( $raw ) === 'all' ) {
			return 0;
		}
		$uid = absint( $raw );
		return $uid > 0 ? $uid : 0;
	}

	/**
	 * Filtre texte : nom / login / e-mail client ou extrait du JSON (colonne question).
	 *
	 * @return string Chaîne tronquée (max 200 car.).
	 */
	public static function normalize_admin_user_text_filter_from_request() {
		if ( ! isset( $_GET['avi_user_filter'] ) ) {
			return '';
		}
		$s = sanitize_text_field( wp_unslash( (string) $_GET['avi_user_filter'] ) );
		$s = trim( $s );
		if ( strlen( $s ) > 200 ) {
			$s = substr( $s, 0, 200 );
		}
		return $s;
	}

	/**
	 * Paramètre GET d’onglet interactions (pagination / dernier volet actif).
	 * Priorité : `avi_inter_context`, puis legacy `avi_inter_ctx`.
	 *
	 * @return string '' | self::ADMIN_DASHBOARD_CONTEXT_B2C | self::ADMIN_DASHBOARD_CONTEXT_B2B
	 */
	public static function get_inter_context_query_from_request() {
		if ( isset( $_GET['avi_inter_context'] ) ) {
			$c = sanitize_key( wp_unslash( (string) $_GET['avi_inter_context'] ) );
			if ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $c || self::ADMIN_DASHBOARD_CONTEXT_B2C === $c ) {
				return $c;
			}
		}
		if ( isset( $_GET['avi_inter_ctx'] ) ) {
			$c = sanitize_key( wp_unslash( (string) $_GET['avi_inter_ctx'] ) );
			if ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $c || self::ADMIN_DASHBOARD_CONTEXT_B2C === $c ) {
				return $c;
			}
		}
		return '';
	}

	/**
	 * @return bool
	 */
	private static function admin_has_suffixed_inter_status_params() {
		return isset( $_GET['avi_inter_status_b2c'] ) || isset( $_GET['avi_inter_status_b2b'] );
	}

	/**
	 * @return bool
	 */
	private static function admin_has_suffixed_inter_paged_params() {
		return isset( $_GET['avi_inter_paged_b2c'] ) || isset( $_GET['avi_inter_paged_b2b'] );
	}

	/**
	 * @return bool
	 */
	private static function admin_has_suffixed_avi_search_params() {
		return isset( $_GET['avi_search_b2c'] ) || isset( $_GET['avi_search_b2b'] );
	}

	/**
	 * @return bool
	 */
	private static function admin_has_suffixed_avi_inter_user_params() {
		return isset( $_GET['avi_inter_user_b2c'] ) || isset( $_GET['avi_inter_user_b2b'] );
	}

	/**
	 * @return bool
	 */
	private static function admin_has_suffixed_avi_user_filter_params() {
		return isset( $_GET['avi_user_filter_b2c'] ) || isset( $_GET['avi_user_filter_b2b'] );
	}

	/**
	 * Filtre statut pour un onglet B2C / B2B (clés `avi_inter_status_b2c` / `_b2b`, sinon legacy unique).
	 *
	 * @param string $dashboard_context self::ADMIN_DASHBOARD_CONTEXT_B2C | self::ADMIN_DASHBOARD_CONTEXT_B2B
	 * @return string self::ADMIN_FILTER_*
	 */
	public static function normalize_admin_status_for_dashboard_context( $dashboard_context ) {
		$ctx = ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$key = 'avi_inter_status_' . $ctx;
		if ( isset( $_GET[ $key ] ) ) {
			$s = sanitize_key( wp_unslash( (string) $_GET[ $key ] ) );
			if ( in_array( $s, array( self::ADMIN_FILTER_PENDING, self::ADMIN_FILTER_DONE, self::ADMIN_FILTER_ALL ), true ) ) {
				return $s;
			}
			return self::ADMIN_FILTER_PENDING;
		}
		if ( ! self::admin_has_suffixed_inter_status_params() ) {
			return self::normalize_admin_status_filter_from_request();
		}
		return self::ADMIN_FILTER_PENDING;
	}

	/**
	 * Recherche question/réponse pour un onglet (`avi_search_b2c` / `_b2b`).
	 *
	 * @param string $dashboard_context b2c | b2b
	 * @return string
	 */
	public static function normalize_admin_search_for_dashboard_context( $dashboard_context ) {
		$ctx = ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$key = 'avi_search_' . $ctx;
		if ( isset( $_GET[ $key ] ) ) {
			return sanitize_text_field( wp_unslash( (string) $_GET[ $key ] ) );
		}
		if ( ! self::admin_has_suffixed_avi_search_params() && isset( $_GET['avi_search'] ) ) {
			return sanitize_text_field( wp_unslash( (string) $_GET['avi_search'] ) );
		}
		return '';
	}

	/**
	 * Filtre user_id WP pour un onglet.
	 *
	 * @param string $dashboard_context b2c | b2b
	 * @return int
	 */
	public static function normalize_admin_user_id_for_dashboard_context( $dashboard_context ) {
		$ctx = ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$key = 'avi_inter_user_' . $ctx;
		if ( isset( $_GET[ $key ] ) ) {
			$raw = wp_unslash( (string) $_GET[ $key ] );
			$raw = trim( $raw );
			if ( $raw === '' || $raw === '0' || strtolower( $raw ) === 'all' ) {
				return 0;
			}
			$uid = absint( $raw );
			return $uid > 0 ? $uid : 0;
		}
		if ( ! self::admin_has_suffixed_avi_inter_user_params() ) {
			return self::normalize_admin_user_id_filter_from_request();
		}
		return 0;
	}

	/**
	 * Filtre texte client / JSON pour un onglet.
	 *
	 * @param string $dashboard_context b2c | b2b
	 * @return string
	 */
	public static function normalize_admin_user_text_for_dashboard_context( $dashboard_context ) {
		$ctx = ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$key = 'avi_user_filter_' . $ctx;
		if ( isset( $_GET[ $key ] ) ) {
			$s = sanitize_text_field( wp_unslash( (string) $_GET[ $key ] ) );
			$s = trim( $s );
			if ( strlen( $s ) > 200 ) {
				$s = substr( $s, 0, 200 );
			}
			return $s;
		}
		if ( ! self::admin_has_suffixed_avi_user_filter_params() ) {
			return self::normalize_admin_user_text_filter_from_request();
		}
		return '';
	}

	/**
	 * Numéro de page admin pour un onglet (`avi_inter_paged_b2c` / `_b2b`, ou legacy `avi_inter_paged` + `avi_inter_context`).
	 *
	 * @param string $dashboard_context b2c | b2b
	 * @return int >= 1
	 */
	public static function admin_inter_paged_from_request_for_dashboard_context( $dashboard_context ) {
		$ctx = ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$key = 'avi_inter_paged_' . $ctx;
		if ( isset( $_GET[ $key ] ) ) {
			return max( 1, absint( $_GET[ $key ] ) );
		}
		if ( ! self::admin_has_suffixed_inter_paged_params() && isset( $_GET['avi_inter_paged'] ) ) {
			$raw_ctx = self::get_inter_context_query_from_request();
			$p       = max( 1, absint( $_GET['avi_inter_paged'] ) );
			if ( '' === $raw_ctx && self::ADMIN_DASHBOARD_CONTEXT_B2C === $ctx ) {
				return $p;
			}
			if ( $raw_ctx === $ctx ) {
				return $p;
			}
		}
		return 1;
	}

	/**
	 * Contexte B2C / B2B pour la liste admin (`avi_inter_context`, legacy `avi_inter_ctx`).
	 *
	 * @param string|null $default Si non null, utilisé quand le paramètre GET est absent ou invalide.
	 * @return string self::ADMIN_DASHBOARD_CONTEXT_B2C | self::ADMIN_DASHBOARD_CONTEXT_B2B
	 */
	public static function normalize_admin_dashboard_context_from_request( $default = null ) {
		$def = ( $default === self::ADMIN_DASHBOARD_CONTEXT_B2B ) ? self::ADMIN_DASHBOARD_CONTEXT_B2B : self::ADMIN_DASHBOARD_CONTEXT_B2C;
		$raw = self::get_inter_context_query_from_request();
		if ( $raw !== '' ) {
			return $raw;
		}
		return $def;
	}

	/**
	 * Filtre SQL B2C / B2B sur la colonne question (JSON embarqué).
	 * B2B : au moins un marqueur parmi dashboard_type b2b, user_type b2b, fleet_console.
	 * B2C : aucun de ces marqueurs.
	 *
	 * @return array{0:string,1:array<int,string>} Fragment SQL (parenthèse) et placeholders LIKE.
	 */
	private static function admin_dashboard_context_sql( $dashboard_context ) {
		$patterns = self::admin_dashboard_context_b2b_question_like_patterns();
		$n        = count( $patterns );
		if ( $n < 1 ) {
			return array( '', array() );
		}
		if ( self::ADMIN_DASHBOARD_CONTEXT_B2B === $dashboard_context ) {
			$parts = array();
			for ( $i = 0; $i < $n; $i++ ) {
				$parts[] = 'question LIKE %s';
			}
			return array( '(' . implode( ' OR ', $parts ) . ')', $patterns );
		}
		if ( self::ADMIN_DASHBOARD_CONTEXT_B2C === $dashboard_context ) {
			$parts = array();
			for ( $i = 0; $i < $n; $i++ ) {
				$parts[] = 'question NOT LIKE %s';
			}
			return array( '(' . implode( ' AND ', $parts ) . ')', $patterns );
		}
		return array( '', array() );
	}

	/**
	 * ID auteur de la session : colonne user_id, sinon user_id de avi_chat_logs via chat_log_id.
	 *
	 * @param int $user_id     Colonne user_id de avi_ai_interactions.
	 * @param int $chat_log_id Colonne chat_log_id (0 si absent).
	 * @return int
	 */
	public static function resolve_interaction_author_user_id( $user_id, $chat_log_id ) {
		$uid = (int) $user_id;
		if ( $uid > 0 ) {
			return $uid;
		}
		$clid = (int) $chat_log_id;
		if ( $clid < 1 ) {
			return 0;
		}
		global $wpdb;
		$logs = $wpdb->prefix . 'avi_chat_logs';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_var( $wpdb->prepare( "SELECT user_id FROM `{$logs}` WHERE id = %d LIMIT 1", $clid ) );
		return $row ? (int) $row : 0;
	}

	/**
	 * Libellé quand business_name (JSON) est vide : admin, test interne, ou nom d’affichage.
	 *
	 * @param int $user_id ID WordPress effectif.
	 * @return string
	 */
	public static function get_interaction_session_user_display_fallback( $user_id ) {
		$uid = (int) $user_id;
		if ( $uid < 1 ) {
			return '';
		}
		$u = get_userdata( $uid );
		if ( ! $u ) {
			return '';
		}
		$login = (string) $u->user_login;
		$dn    = trim( (string) $u->display_name );
		if ( user_can( $uid, 'manage_options' ) ) {
			if ( $dn !== '' && preg_match( '/test|demo|staging|interne|internal/i', $login . ' ' . $dn ) ) {
				return sprintf(
					/* translators: %s: display name */
					__( 'Test interne — %s', 'avision-dashboard' ),
					$dn
				);
			}
			if ( $dn !== '' ) {
				return sprintf(
					/* translators: %s: WordPress display name */
					__( 'Admin — %s', 'avision-dashboard' ),
					$dn
				);
			}
			return __( 'Admin', 'avision-dashboard' );
		}
		if ( $dn !== '' ) {
			return $dn;
		}
		return $login;
	}

	/**
	 * Clause WHERE + valeurs pour prepare() : statut + recherche question/réponse.
	 *
	 * @param string $status_filter pending|done|all
	 * @param string $search_query  Texte brut (trim + esc_like côté appelant via $wpdb).
	 * @param int    $user_id_filter  ID WP strictement positif = filtrer sur user_id ; 0 = ignoré.
	 * @param string $user_text_filter LIKE sur question (JSON) + display_name / login / email (session ou chat_log).
	 * @param string $dashboard_context self::ADMIN_DASHBOARD_CONTEXT_B2C | self::ADMIN_DASHBOARD_CONTEXT_B2B
	 * @return array{0:string,1:array<int,mixed>} Fragment « WHERE … » ou chaîne vide, puis placeholders.
	 */
	private static function admin_list_where_and_values( $status_filter, $search_query, $user_id_filter = 0, $user_text_filter = '', $dashboard_context = self::ADMIN_DASHBOARD_CONTEXT_B2C ) {
		global $wpdb;
		$conditions = array();
		$values     = array();

		list( $ctx_sql, $ctx_vals ) = self::admin_dashboard_context_sql( $dashboard_context );
		if ( $ctx_sql !== '' && $ctx_vals !== array() ) {
			$conditions[] = $ctx_sql;
			foreach ( $ctx_vals as $cv ) {
				$values[] = $cv;
			}
		}

		if ( self::ADMIN_FILTER_DONE === $status_filter ) {
			$conditions[] = 'COALESCE(status, 0) = 1';
		} elseif ( self::ADMIN_FILTER_ALL !== $status_filter ) {
			$conditions[] = 'COALESCE(status, 0) = 0';
		}

		$uid_f = (int) $user_id_filter;
		if ( $uid_f > 0 ) {
			$conditions[] = 'user_id = %d';
			$values[]       = $uid_f;
		}

		$search = trim( (string) $search_query );
		if ( $search !== '' ) {
			$like         = '%' . $wpdb->esc_like( $search ) . '%';
			$conditions[] = '(question LIKE %s OR answer LIKE %s)';
			$values[]     = $like;
			$values[]     = $like;
		}

		$user_txt = trim( (string) $user_text_filter );
		if ( $user_txt !== '' ) {
			$table       = self::table_name();
			$users_table = $wpdb->users;
			$logs_table  = $wpdb->prefix . 'avi_chat_logs';
			$like_u      = '%' . $wpdb->esc_like( $user_txt ) . '%';
			// JSON dans question + auteur session + auteur résolu via chat_log_id.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- noms de tables issus de $wpdb.
			$conditions[] = "(
				`{$table}`.question LIKE %s
				OR ( `{$table}`.profile_id IS NOT NULL AND `{$table}`.profile_id <> '' AND `{$table}`.profile_id LIKE %s )
				OR EXISTS (
					SELECT 1 FROM `{$users_table}` u_sess
					WHERE u_sess.ID = `{$table}`.user_id
					AND ( u_sess.display_name LIKE %s OR u_sess.user_login LIKE %s OR u_sess.user_email LIKE %s )
				)
				OR (
					`{$table}`.chat_log_id IS NOT NULL AND `{$table}`.chat_log_id > 0
					AND EXISTS (
						SELECT 1 FROM `{$logs_table}` cl2
						INNER JOIN `{$users_table}` u_log ON u_log.ID = cl2.user_id
						WHERE cl2.id = `{$table}`.chat_log_id
						AND ( u_log.display_name LIKE %s OR u_log.user_login LIKE %s OR u_log.user_email LIKE %s )
					)
				)
			)";
			for ( $i = 0; $i < 8; $i++ ) {
				$values[] = $like_u;
			}
		}

		if ( empty( $conditions ) ) {
			return array( '', array() );
		}

		return array( 'WHERE ' . implode( ' AND ', $conditions ), $values );
	}

	/**
	 * Nombre de lignes pour la liste admin (pagination).
	 *
	 * @param string $status_filter self::ADMIN_FILTER_PENDING | self::ADMIN_FILTER_DONE | self::ADMIN_FILTER_ALL
	 * @param string $search_query  Filtre LIKE sur question / answer (vide = ignoré).
	 * @param int    $user_id_filter ID utilisateur WP (>0) ou 0.
	 * @param string $user_text_filter Filtre nom / JSON (voir normalize_admin_user_text_filter_from_request).
	 * @param string $dashboard_context b2c | b2b (voir ADMIN_DASHBOARD_CONTEXT_*).
	 * @return int
	 */
	public static function count_for_admin_list( $status_filter = self::ADMIN_FILTER_PENDING, $search_query = '', $user_id_filter = 0, $user_text_filter = '', $dashboard_context = self::ADMIN_DASHBOARD_CONTEXT_B2C ) {
		global $wpdb;
		self::ensure_table();
		$table = self::table_name();
		list( $where, $vals ) = self::admin_list_where_and_values( $status_filter, $search_query, $user_id_filter, $user_text_filter, $dashboard_context );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "SELECT COUNT(*) FROM `{$table}` {$where}";
		if ( ! empty( $vals ) ) {
			$sql = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $vals ) );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$n = $wpdb->get_var( $sql );
		return (int) $n;
	}

	/**
	 * Page admin Interactions (tri date décroissante).
	 *
	 * @param int    $page           1-based.
	 * @param int    $per_page       Taille de page.
	 * @param string $status_filter  pending | done | all
	 * @param string $search_query   Filtre LIKE sur question / answer (vide = ignoré).
	 * @param int    $user_id_filter ID utilisateur WP (>0) ou 0.
	 * @param string $user_text_filter Filtre nom / JSON.
	 * @param string $dashboard_context b2c | b2b.
	 * @return array<int, array<string, mixed>>
	 */
	public static function fetch_admin_page( $page = 1, $per_page = null, $status_filter = self::ADMIN_FILTER_PENDING, $search_query = '', $user_id_filter = 0, $user_text_filter = '', $dashboard_context = self::ADMIN_DASHBOARD_CONTEXT_B2C ) {
		global $wpdb;
		self::ensure_table();
		$table    = self::table_name();
		$page     = max( 1, (int) $page );
		$per_page = $per_page !== null ? (int) $per_page : self::ADMIN_PER_PAGE;
		$per_page = max( 1, min( 100, $per_page ) );
		$offset   = ( $page - 1 ) * $per_page;
		list( $where, $vals ) = self::admin_list_where_and_values( $status_filter, $search_query, $user_id_filter, $user_text_filter, $dashboard_context );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "SELECT id, user_id, profile_id, level_context, question, answer, admin_correction, correction_updated_at, created_at, status, chat_log_id FROM `{$table}` {$where} ORDER BY created_at DESC LIMIT %d OFFSET %d";
		$all = array_merge( $vals, array( $per_page, $offset ) );
		$sql = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $all ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Sépare le message stocké (Contexte Technique Dashboard + JSON + « Question utilisateur : ») pour l’admin.
	 *
	 * @param string $raw Valeur brute du champ question (log exchange).
	 * @return array{question: string, json_context: string|null} question = texte après « Question utilisateur : » ; json_context = tout le préfixe (JSON / contexte) pour &lt;details&gt;. Détection : preg_match avec motif « Question utilisateur », espaces optionnels, « : » (modificateurs PCRE i + s), sinon repli strpos( …, 'Question utilisateur :' ).
	 */
	public static function parse_stored_question_for_admin( $raw ) {
		$raw = is_string( $raw ) ? $raw : '';
		$out = array(
			'question'     => $raw,
			'json_context' => null,
		);
		if ( $raw === '' ) {
			return $out;
		}

		$question = $raw;
		$before   = '';

		// Parsing agressif : première occurrence de « Question utilisateur » + espaces + « : » (voir premium-dashboard.js).
		if ( preg_match( '/Question utilisateur\s*:\s*/is', $raw, $md, PREG_OFFSET_CAPTURE ) ) {
			$off = isset( $md[0][1] ) ? (int) $md[0][1] : 0;
			$len = isset( $md[0][0] ) ? strlen( (string) $md[0][0] ) : 0;
			$before   = $off > 0 ? substr( $raw, 0, $off ) : '';
			$question = substr( $raw, $off + $len );
		} else {
			// Repli : position stricte de la chaîne littérale (sans regex sur les espaces).
			$needle = 'Question utilisateur :';
			$pos    = strpos( $raw, $needle );
			if ( false !== $pos ) {
				$before   = substr( $raw, 0, $pos );
				$question = substr( $raw, $pos + strlen( $needle ) );
			}
		}

		$question = ltrim( (string) $question, " \t" );
		if ( $question === '' ) {
			$question = $raw;
			$before   = '';
		}

		if ( trim( $before ) !== '' ) {
			$prefix      = 'Contexte Technique Dashboard :';
			$before_trim = ltrim( $before );
			$prefix_len  = strlen( $prefix );
			if ( $prefix_len > 0 && strncmp( $before_trim, $prefix, $prefix_len ) === 0 ) {
				$json = trim( substr( $before_trim, $prefix_len ) );
				if ( $json !== '' ) {
					$out['json_context'] = $json;
				}
			} else {
				$out['json_context'] = trim( $before );
			}
		}

		$out['question'] = $question;
		return $out;
	}

	/**
	 * Extrait business_name et activeLevel (id, title) depuis le bloc JSON du contexte dashboard.
	 * Essaie d’abord json_decode ; en échec, motifs regex sur le préfixe technique (chaîne brute).
	 *
	 * @param string|null $json_raw Contenu retourné par parse_stored_question_for_admin()['json_context'].
	 * @return array{business_name: string, active_level_id: string, active_level_title: string}
	 */
	public static function extract_context_meta_from_json( $json_raw ) {
		$out = array(
			'business_name'       => '',
			'active_level_id'     => '',
			'active_level_title'  => '',
		);
		if ( ! is_string( $json_raw ) ) {
			return $out;
		}
		$trim = trim( $json_raw );
		if ( $trim === '' ) {
			return $out;
		}

		$json_flags = 0;
		if ( defined( 'JSON_INVALID_UTF8_IGNORE' ) ) {
			$json_flags |= JSON_INVALID_UTF8_IGNORE;
		}
		$data = json_decode( $trim, true, 512, $json_flags );
		if ( JSON_ERROR_NONE === json_last_error() && is_array( $data ) ) {
			if ( isset( $data['business_name'] ) && is_scalar( $data['business_name'] ) ) {
				$out['business_name'] = trim( (string) $data['business_name'] );
			} elseif ( isset( $data['business'] ) && is_array( $data['business'] ) ) {
				if ( isset( $data['business']['business_name'] ) && is_scalar( $data['business']['business_name'] ) ) {
					$out['business_name'] = trim( (string) $data['business']['business_name'] );
				}
			}
			if ( isset( $data['activeLevel'] ) && is_array( $data['activeLevel'] ) ) {
				if ( isset( $data['activeLevel']['id'] ) && is_scalar( $data['activeLevel']['id'] ) ) {
					$out['active_level_id'] = trim( (string) $data['activeLevel']['id'] );
				}
				if ( isset( $data['activeLevel']['title'] ) && is_scalar( $data['activeLevel']['title'] ) ) {
					$out['active_level_title'] = trim( (string) $data['activeLevel']['title'] );
				}
			}
			return $out;
		}

		// Repli : JSON tronqué ou invalide — extraire par regex (structure premium-dashboard.js).
		if ( preg_match( '/"business_name"\s*:\s*"((?:\\\\.|[^"\\\\])*)"/s', $trim, $bm ) ) {
			$out['business_name'] = self::_avi_inter_unescape_json_string( $bm[1] );
		}
		if ( preg_match( '/"activeLevel"\s*:\s*\{/s', $trim, $am, PREG_OFFSET_CAPTURE ) ) {
			$start = isset( $am[0][1] ) ? (int) $am[0][1] : 0;
			$slice = substr( $trim, $start, 4000 );
			if ( preg_match( '/"id"\s*:\s*"((?:\\\\.|[^"\\\\])*)"/s', $slice, $idm ) ) {
				$out['active_level_id'] = self::_avi_inter_unescape_json_string( $idm[1] );
			}
			if ( preg_match( '/"title"\s*:\s*"((?:\\\\.|[^"\\\\])*)"/s', $slice, $tm ) ) {
				$out['active_level_title'] = self::_avi_inter_unescape_json_string( $tm[1] );
			}
		}

		return $out;
	}

	/**
	 * @param string $inner Contenu capturé entre guillemets JSON (séquences \", \\, \n…).
	 * @return string
	 */
	private static function _avi_inter_unescape_json_string( $inner ) {
		$s = (string) $inner;
		if ( $s === '' ) {
			return '';
		}
		return (string) preg_replace_callback(
			'/\\\\(u[0-9a-fA-F]{4}|.)/',
			static function ( $m ) {
				$tail = isset( $m[1] ) ? $m[1] : '';
				if ( strlen( $tail ) === 5 && 'u' === $tail[0] ) {
					$cp = hexdec( substr( $tail, 1, 4 ) );
					if ( $cp > 0 ) {
						return function_exists( 'mb_chr' ) ? mb_chr( $cp, 'UTF-8' ) : chr( min( 255, $cp ) );
					}
				}
				if ( 'n' === $tail ) {
					return "\n";
				}
				if ( 'r' === $tail ) {
					return "\r";
				}
				if ( 't' === $tail ) {
					return "\t";
				}
				return $tail;
			},
			$s
		);
	}

	/**
	 * Libellé colonne « Niveau » : titre + id si dispo, sinon id seul, sinon repli level_context SQL.
	 *
	 * @param array{business_name?:string,active_level_id?:string,active_level_title?:string} $meta
	 * @param string                                                                           $level_context_fallback Valeur DB level_context.
	 * @return string
	 */
	/**
	 * Libellé colonne « Client » : compte WordPress + établissement (JSON) + identifiant profil si présent.
	 *
	 * @param int         $user_id      ID WP (0 = inconnu / invité).
	 * @param string      $business_name Nom établissement extrait du contexte JSON.
	 * @param string|null $profile_id   Identifiant profil SaaS stocké en base.
	 * @param int         $chat_log_id  Lien vers avi_chat_logs pour résoudre user_id si 0.
	 * @return string Texte (séparateurs ·).
	 */
	public static function format_interaction_client_column( $user_id, $business_name, $profile_id = null, $chat_log_id = 0 ) {
		$effective_uid = self::resolve_interaction_author_user_id( (int) $user_id, (int) $chat_log_id );
		$bn            = is_string( $business_name ) ? trim( $business_name ) : '';
		$chunks        = array();

		if ( $bn !== '' ) {
			$chunks[] = $bn;
			if ( $effective_uid > 0 ) {
				$u = get_userdata( $effective_uid );
				if ( $u ) {
					$who = trim( (string) $u->display_name );
					if ( $who === '' ) {
						$who = (string) $u->user_login;
					}
					$chunks[] = sprintf(
						/* translators: 1: display name or login, 2: user ID */
						__( 'Compte : %1$s (WP #%2$d)', 'avision-dashboard' ),
						$who,
						$effective_uid
					);
				}
			}
		} else {
			$fb = self::get_interaction_session_user_display_fallback( $effective_uid );
			if ( $fb !== '' ) {
				$chunks[] = $fb;
			} elseif ( $effective_uid > 0 ) {
				$chunks[] = sprintf(
					/* translators: %d: WordPress user ID */
					__( 'Compte #%d', 'avision-dashboard' ),
					$effective_uid
				);
			}
			if ( $effective_uid > 0 ) {
				$u = get_userdata( $effective_uid );
				if ( $u ) {
					$email = trim( (string) $u->user_email );
					if ( $email !== '' ) {
						$chunks[] = $email;
					}
				}
			}
		}

		$pid = is_string( $profile_id ) ? trim( $profile_id ) : '';
		if ( $pid !== '' && $pid !== '0' ) {
			$chunks[] = __( 'Profil', 'avision-dashboard' ) . ' ' . $pid;
		}

		$chunks = array_filter( array_map( 'trim', $chunks ) );
		if ( $chunks === array() ) {
			return '';
		}
		return implode( ' · ', $chunks );
	}

	public static function format_interaction_level_column( array $meta, $level_context_fallback = '' ) {
		$id    = isset( $meta['active_level_id'] ) ? trim( (string) $meta['active_level_id'] ) : '';
		$title = isset( $meta['active_level_title'] ) ? trim( (string) $meta['active_level_title'] ) : '';
		$fb    = is_string( $level_context_fallback ) ? trim( $level_context_fallback ) : '';

		if ( $title !== '' && $id !== '' ) {
			return $title . ' (' . $id . ')';
		}
		if ( $title !== '' ) {
			return $title;
		}
		if ( $id !== '' ) {
			return $id;
		}
		return $fb;
	}

	/**
	 * Marque une interaction comme ignorée / traitée (hors vue par défaut, hors prompt).
	 *
	 * @param int $id PK.
	 * @return bool
	 */
	public static function set_status_done( $id ) {
		global $wpdb;
		self::ensure_table();
		$table = self::table_name();
		$id    = (int) $id;
		if ( $id < 1 ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$ok = $wpdb->update(
			$table,
			array( 'status' => self::STATUS_DONE ),
			array( 'id' => $id ),
			array( '%d' ),
			array( '%d' )
		);
		return false !== $ok;
	}

	/**
	 * @param int    $id    PK.
	 * @param string $note  Correction / note.
	 * @return bool
	 */
	public static function update_correction( $id, $note ) {
		global $wpdb;
		self::ensure_table();
		$table = self::table_name();
		$id    = (int) $id;
		if ( $id < 1 ) {
			return false;
		}
		$note = is_string( $note ) ? sanitize_textarea_field( $note ) : '';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$ok = $wpdb->update(
			$table,
			array(
				'admin_correction'       => $note,
				'correction_updated_at' => current_time( 'mysql', true ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		return false !== $ok;
	}

	/**
	 * AJAX : enregistrer une correction.
	 */
	public static function ajax_save_correction() {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'forbidden' ), 403 );
		}
		check_ajax_referer( 'avi_kc_interactions', 'nonce' );
		$id   = isset( $_POST['id'] ) ? (int) $_POST['id'] : 0;
		$note = isset( $_POST['correction'] ) ? wp_unslash( (string) $_POST['correction'] ) : '';
		if ( $id < 1 ) {
			wp_send_json_error( array( 'message' => 'bad_id' ), 400 );
		}
		$ok = self::update_correction( $id, $note );
		if ( ! $ok ) {
			wp_send_json_error( array( 'message' => 'update_failed' ), 500 );
		}
		wp_send_json_success( array( 'id' => $id ) );
	}

	/**
	 * AJAX : marquer une ligne comme traitée / ignorée.
	 */
	public static function ajax_dismiss_interaction() {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'forbidden' ), 403 );
		}
		check_ajax_referer( 'avi_kc_interactions', 'nonce' );
		$id = isset( $_POST['id'] ) ? (int) $_POST['id'] : 0;
		if ( $id < 1 ) {
			wp_send_json_error( array( 'message' => 'bad_id' ), 400 );
		}
		$ok = self::set_status_done( $id );
		if ( ! $ok ) {
			wp_send_json_error( array( 'message' => 'update_failed' ), 500 );
		}
		wp_send_json_success( array( 'id' => $id ) );
	}

	/**
	 * AJAX : copier les N derniers avi_chat_logs vers avi_ai_interactions.
	 */
	public static function ajax_import_chat_logs() {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'forbidden' ), 403 );
		}
		check_ajax_referer( 'avi_kc_interactions', 'nonce' );
		global $wpdb;
		self::ensure_table();
		$logs = $wpdb->prefix . 'avi_chat_logs';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $logs ) ) !== $logs ) {
			wp_send_json_error( array( 'message' => 'no_chat_logs_table' ), 400 );
		}
		$limit = 50;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$old = $wpdb->get_results( "SELECT id, user_id, query, response, level_context FROM `{$logs}` ORDER BY timestamp DESC LIMIT " . (int) $limit, ARRAY_A );
		if ( ! is_array( $old ) || $old === array() ) {
			wp_send_json_success( array( 'imported' => 0 ) );
		}
		$imported = 0;
		foreach ( $old as $row ) {
			$q = isset( $row['query'] ) ? (string) $row['query'] : '';
			$a = isset( $row['response'] ) ? (string) $row['response'] : '';
			if ( $q === '' || $a === '' ) {
				continue;
			}
			$uid = isset( $row['user_id'] ) ? (int) $row['user_id'] : 0;
			$lvl = isset( $row['level_context'] ) ? (string) $row['level_context'] : null;
			$clid = isset( $row['id'] ) ? (int) $row['id'] : 0;
			$table = self::table_name();
			if ( $clid > 0 ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$dup = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$table}` WHERE chat_log_id = %d LIMIT 1", $clid ) );
				if ( $dup ) {
					continue;
				}
			}
			$insert_row = array(
				'user_id'       => $uid,
				'profile_id'    => '',
				'level_context' => $lvl ? substr( sanitize_text_field( $lvl ), 0, 50 ) : '',
				'question'      => $q,
				'answer'        => $a,
			);
			$insert_fmt = array( '%d', '%s', '%s', '%s', '%s' );
			if ( $clid > 0 ) {
				$insert_row['chat_log_id'] = $clid;
				$insert_fmt[]              = '%d';
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert( $table, $insert_row, $insert_fmt );
			if ( $wpdb->insert_id ) {
				$imported++;
			}
		}
		wp_send_json_success( array( 'imported' => $imported ) );
	}
}
