<?php
/**
 * Sprint 10-C — Index DB performance (idempotent, vérif information_schema avant ALTER).
 *
 * @package Avision
 */

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

const AVI_S10C_PERF_VERSION = '1.0';

/**
 * Indique si un index nommé existe déjà sur une table.
 *
 * @param string $table      Nom complet de la table (avec préfixe WP).
 * @param string $index_name Nom de l'index.
 */
function avi_s10c_index_exists( string $table, string $index_name ): bool {
	global $wpdb;
	if ( ! isset( $wpdb ) || ! $wpdb || $table === '' || $index_name === '' ) {
		return false;
	}
	$db_name = defined( 'DB_NAME' ) ? DB_NAME : '';
	if ( $db_name === '' ) {
		return false;
	}
	$cnt = (int) $wpdb->get_var(
		$wpdb->prepare(
			'SELECT COUNT(1) FROM information_schema.statistics WHERE table_schema = %s AND table_name = %s AND index_name = %s',
			$db_name,
			$table,
			$index_name
		)
	);
	return $cnt > 0;
}

/**
 * Ajoute les index S10-C s'ils manquent ; option `avi_s10c_indexes_migrated` = version une fois tout présent.
 *
 * @return void
 */
function avi_s10c_maybe_add_performance_indexes(): void {
	global $wpdb;
	if ( ! isset( $wpdb ) || ! $wpdb ) {
		return;
	}
	$opt_key = 'avi_s10c_indexes_migrated';
	if ( get_option( $opt_key, '' ) === AVI_S10C_PERF_VERSION ) {
		return;
	}

	$t_kw   = $wpdb->prefix . 'avi_saas_keywords';
	$t_sec  = $wpdb->prefix . 'avi_security_logs';
	$t_prof = $wpdb->prefix . 'avi_saas_profiles';
	$t_hist = $wpdb->prefix . 'avi_keyword_history';

	$jobs = [
		[
			'table' => $t_kw,
			'index' => 'idx_scan_status_profile',
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DDL, noms contrôlés.
			'sql'   => "ALTER TABLE `{$t_kw}` ADD INDEX idx_scan_status_profile (scan_status(20), profile_id)",
		],
		[
			'table' => $t_sec,
			'index' => 'idx_user_created_type',
			'sql'   => "ALTER TABLE `{$t_sec}` ADD INDEX idx_user_created_type (user_id, created_at, event_type)",
		],
		[
			'table' => $t_prof,
			'index' => 'idx_agency_guardian',
			'sql'   => "ALTER TABLE `{$t_prof}` ADD INDEX idx_agency_guardian (agency_id, guardian_active)",
		],
		[
			'table' => $t_hist,
			'index' => 'idx_user_kw_date',
			'sql'   => "ALTER TABLE `{$t_hist}` ADD INDEX idx_user_kw_date (user_id, keyword_text(100), last_scan_date)",
		],
	];

	foreach ( $jobs as $job ) {
		$table = $job['table'];
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
			continue;
		}
		if ( avi_s10c_index_exists( $table, $job['index'] ) ) {
			continue;
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DDL idempotent après contrôle index.
		$wpdb->query( $job['sql'] );
		if ( $wpdb->last_error !== '' && function_exists( 'error_log' ) ) {
			error_log( '[AVI-S10C] Index ' . $job['index'] . ' : ' . $wpdb->last_error );
		}
	}

	// Option « terminé » uniquement si les 4 tables existent et portent déjà l’index attendu.
	$all_ok = true;
	foreach ( $jobs as $job ) {
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $job['table'] ) ) !== $job['table'] ) {
			$all_ok = false;
			break;
		}
		if ( ! avi_s10c_index_exists( $job['table'], $job['index'] ) ) {
			$all_ok = false;
			break;
		}
	}

	if ( $all_ok ) {
		update_option( $opt_key, AVI_S10C_PERF_VERSION, false );
	}
}

add_action( 'plugins_loaded', 'avi_s10c_maybe_add_performance_indexes', 6 );
