<?php
// +-------------------------------------------------+
// 2002-2004 PMB Services / www.sigb.net pmb@sigb.net et contributeurs (voir www.sigb.net)
// +-------------------------------------------------+
// $Id: addon.inc.php,v 1.8.2.32.2.4.2.11.2.65 2026/07/17 07:33:08 dgoron Exp $

if (stristr($_SERVER['REQUEST_URI'], ".inc.php")) {
    die("no access");
}

global $pmb_bdd_subversion, $pmb_bdd_version, $pmb_version_brut, $pmb_version_patch;
global $pmb_subversion_database_as_it_shouldbe;
global $pmb_bdd_subversion_error;

function traite_addon_rqt($requete = "", $message = "")
{
    global $charset, $pmb_bdd_subversion;
    global $db_update_log_version, $db_update_step, $db_update_error;

    $retour = "Successful";
    if ($charset == "utf-8") {
        $requete = encoding_normalize::utf8_encode($requete);
        $message = encoding_normalize::convert_encoding($message);
    }
    pmb_mysql_query($requete);
    $erreur_no = pmb_mysql_errno();
    $erreur_msg = pmb_mysql_error();

    if ($erreur_no) {
        switch ($erreur_no) {
            case "1060":
                $retour = "Field already exists, no problem.";
                break;
            case "1061":
                $retour = "Key already exists, no problem.";
                break;
            case "1091":
                $retour = "Object already deleted, no problem.";
                break;
            default:
                if (! $db_update_error) {
                    $db_update_error = $db_update_step;
                    $query_error = "UPDATE parametres SET valeur_param='" . $db_update_error . "' WHERE type_param='pmb' AND sstype_param='bdd_subversion_error' AND valeur_param=0";
                    pmb_mysql_query($query_error);
                }
                $retour = "<font color=\"#FF0000\">Error may be fatal : <i>" . $erreur_msg . "<i></font>";
                break;
        }
    }
    $result = pmb_mysql_query("SHOW TABLES LIKE 'db_update_logs'");
    if (pmb_mysql_num_rows($result)) {
        $query_log = " INSERT INTO db_update_logs (db_update_log_type, db_update_log_version, db_update_log_query, db_update_log_message, db_update_log_error, db_update_log_result)
        VALUES ('addon', '" . $db_update_log_version . $db_update_step . "', '" . addslashes($requete) . "', '" . addslashes($message) . "', $erreur_no, '" . addslashes(strip_tags($retour)) . "')";
        pmb_mysql_query($query_log);
    }
    return "<tr><td><font size='1'><span data-alter='action'>" . $message ."</span></font></td><td><font size='1'><span data-alter='result'>" . $retour . "</span></font></td></tr>";
}
echo "<table>";

/**
 * ****************** AJOUTER ICI LES MODIFICATIONS ******************************
 */

// Formate et verifie que $pmb_bdd_subversion est <= à $pmb_subversion_database_as_it_shouldbe
$pmb_bdd_subversion = intval($pmb_bdd_subversion);
$pmb_subversion_database_as_it_shouldbe = intval($pmb_subversion_database_as_it_shouldbe);
if($pmb_bdd_subversion > $pmb_subversion_database_as_it_shouldbe ) {
    $pmb_bdd_subversion =  $pmb_subversion_database_as_it_shouldbe;
}
$pmb_bdd_subversion_error = intval($pmb_bdd_subversion_error);

$db_update_log_version = "PMB : " . $pmb_version_brut . "." . $pmb_version_patch . "; BDD : " . $pmb_bdd_version . "_";
$db_update_step = $pmb_bdd_subversion;

//Raz erreur $pmb_bdd_subversion_error
if($pmb_bdd_subversion <= $pmb_bdd_subversion_error) {
    $query_raz = "UPDATE parametres SET valeur_param=0 WHERE type_param='pmb' AND sstype_param='bdd_subversion_error'";
    pmb_mysql_query($query_raz);
}

while ($db_update_step < $pmb_subversion_database_as_it_shouldbe) {
    switch ($db_update_step) {

        case 0:
            // DG - Texte apparaissant avant la liste des ouvrages dans le courrier ou mail d'invalidation
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pdflettreresa' AND sstype_param='before_list_unconfirmed'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
            VALUES ('pdflettreresa', 'before_list_unconfirmed', 'Suite à votre demande de réservation, nous vous informons que nous avons annulé la confirmation du ou des ouvrages ci-dessous car ils ne sont pas disponibles à ces dates.', 'Texte apparaissant avant la liste des ouvrages dans le courrier ou mail d\'invalidation', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT pdflettreresa_before_list_unconfirmed INTO parametres');
            }

            // DG - Texte apparaissant avant la liste des ouvrages dans le courrier ou mail de suppression
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pdflettreresa' AND sstype_param='before_list_deleted'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
            VALUES ('pdflettreresa', 'before_list_deleted', 'Suite à votre demande de réservation, nous sommes dans le regret de vous informer que le ou les ouvrages ci-dessous ne sont pas disponibles.', 'Texte apparaissant avant la liste des ouvrages dans le courrier ou mail de suppression', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT pdflettreresa_before_list_deleted INTO parametres');
            }

            // DG - Texte apparaissant après la liste des ouvrages
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pdflettreresa' AND sstype_param='after_list_unconfirmed'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
            VALUES ('pdflettreresa', 'after_list_unconfirmed', '', 'Texte apparaissant après la liste des ouvrages dans le courrier ou mail d\'invalidation', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT pdflettreresa_after_list_unconfirmed INTO parametres');
            }

            // DG - Texte apparaissant après la liste des ouvrages
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pdflettreresa' AND sstype_param='after_list_deleted'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
            VALUES ('pdflettreresa', 'after_list_deleted', '', 'Texte apparaissant après la liste des ouvrages dans le courrier ou mail de suppression', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT pdflettreresa_after_list_deleted INTO parametres');
            }
            break;
        case 1:
            // DG - Gestion des groupes de champs personnalisés
            $rqt = "create table if not exists custom_fields_groups (
				id_group int(10) unsigned NOT NULL AUTO_INCREMENT,
				group_type varchar(255) not null default '',
				group_name varchar(255) not null default '',
				group_title varchar(255) not null default '',
                group_fields mediumtext,
	    		primary key (id_group)) ";
            echo traite_addon_rqt($rqt,"create table if not exists custom_fields_groups");
            break;
        case 2:
            // TS - ajout d'une option rgaa pour le parametre rmc_responsive
            $rqt = "UPDATE parametres SET comment_param = 'Affichage de la recherche multi-critères responsive .\n 0 : Non\n 1 : Oui\n 2 : Oui (compatible RGAA)' WHERE type_param = 'opac' AND sstype_param = 'rmc_responsive'  ";
            echo traite_addon_rqt($rqt, "UPDATE opac_rmc_responsive INTO parametres");
            break;
        case 3:
            // GN - Ajout d'une taille maximum pour le stockage des fichiers pour les listes de lecture avec la recherche sémantique
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='ai' AND sstype_param='empr_total_upload_max_size'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
                    VALUES ('ai', 'empr_total_upload_max_size', '10', 'Poids total des fichiers que le lecteur peut uploader en Mo', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT empr_total_upload_max_size INTO parametres artificial_intelligence');
            }

            // GN - Ajout dans la sous-session "Listes de lecture partagées" dans "Intelligence artificielle"
            $rqt = "UPDATE parametres SET section_param = 'shared_lists' WHERE type_param = 'ai' AND sstype_param = 'empr_total_upload_max_size' AND section_param != 'shared_lists';";
            echo traite_addon_rqt($rqt, "UPDATE empr_total_upload_max_size SET section_param='shared_lists'");
            break;
        case 4:
            // JP - Déplacement du paramètre opac_meta_keywords dans les paramètres inutilisés
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param = 'opac' AND sstype_param = 'meta_keywords'")) != 0) {
                $rqt = "UPDATE parametres SET section_param = 'z_unused' WHERE type_param = 'opac' AND sstype_param = 'meta_keywords'";
                echo traite_addon_rqt($rqt, "UPDATE parametres SET section_param = 'z_unused' WHERE type_param = 'opac' AND sstype_param = 'meta_keywords'");
            }
            break;
        case 5:
            //DG - Ajout d'une colonne "num_increment_day_of_month" pour le jour de départ du cycle (numéro)
            if (pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM custom_fields_groups LIKE 'group_title_button_add'")) == 0) {
                $rqt = "ALTER TABLE custom_fields_groups ADD group_title_button_add varchar(255) not null default '' AFTER group_title";
                echo traite_addon_rqt($rqt, "ALTER TABLE custom_fields_groups ADD group_title_button_add");
            }
            break;
        case 6:
            // DG - Liste des adresses pot de miel présentes sur les pages OPAC
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='security' AND sstype_param='honey_pot_email'")) == 0) {
                $rqt = "INSERT INTO parametres (type_param, sstype_param, valeur_param, comment_param, section_param, gestion)
            VALUES ('security', 'honey_pot_email', '', 'Liste des adresses pot de miel présentes sur les pages OPAC pour les \"robots\", à séparer par des points-virgules', '', 0)";
                echo traite_addon_rqt($rqt, 'INSERT security_honey_pot_email INTO parametres');
            }
            break;
        case 7:
            // DB - Modification du commentaire du paramètre opac_resa_popup
            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='opac' AND sstype_param='resa_popup'"))) {
                $comment_param = "Mode d'affichage des réservations et suggestions :\r\n\r\n 0 : Dans la page\n 1 : Dans un popup\r\n 2 : Dans un nouvel onglet / une nouvelle fenêtre";
                $rqt = "UPDATE parametres SET comment_param='" . addslashes($comment_param) . "' WHERE type_param='opac' AND sstype_param='resa_popup'";
                echo traite_addon_rqt($rqt, "UPDATE comment_param opac_resa_popup");
            }
            break;
        case 8:
        	// JP - Ajout d'un paramètre pour définir une URL de redirection de la page 404 à l'OPAC
        	if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='opac' and sstype_param='redirection_url_404'")) == 0){
        		$rqt = "INSERT INTO parametres (id_param, type_param, sstype_param, valeur_param, gestion, comment_param)
            		VALUES (0, 'opac', 'redirection_url_404', '', '0', 'URL de redirection de l\'erreur 404 à l\'OPAC')";
        		echo traite_addon_rqt($rqt, "INSERT opac_redirection_url_404 INTO parametres");
        	}

        	// RT - JP - Implémentation du module SEO
        	$rqt = "CREATE TABLE IF NOT EXISTS seo_page_type(
	            id_page_type INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	            type INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            indexable TINYINT(1) NOT NULL DEFAULT 0,
	            followable TINYINT(1) NOT NULL DEFAULT 0,
	            PRIMARY KEY(id_page_type))";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS seo_page_type");

        	$rqt = "CREATE TABLE IF NOT EXISTS seo_subpage_type(
	            id_subpage_type INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	            type INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            indexable TINYINT(1) NOT NULL DEFAULT 0,
	            followable TINYINT(1) NOT NULL DEFAULT 0,
	            num_page_type INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            PRIMARY KEY(id_subpage_type))";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS seo_subpage_type");

        	$rqt = "CREATE TABLE IF NOT EXISTS seo_sitemap_entry(
	            id_sitemap_entry INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	            type VARCHAR(100) NOT NULL DEFAULT '',
	            in_sitemap TINYINT(1) NOT NULL DEFAULT 0,
	            priority FLOAT(3, 2) NOT NULL DEFAULT 0,
	            frequency_update VARCHAR(100) NOT NULL DEFAULT '',
	            sitemap_order INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            settings MEDIUMBLOB NOT NULL DEFAULT '{}',
	            num_parent INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            PRIMARY KEY(id_sitemap_entry))";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS seo_sitemap_entry");
        	break;
        case 9:
            // DG - Ajout du champ expl_custom_fields permettant de préciser les valeurs par défaut des CP expl en bulletinage
            $rqt = "ALTER TABLE abts_abts ADD expl_custom_fields MEDIUMTEXT";
            echo traite_addon_rqt($rqt,"ALTER TABLE abts_abts ADD expl_custom_fields");
            break;
        case 10:
            // DB - Verification compatibilite WebP
            if( (imagetypes() & IMG_WEBP) == 0 ) {
                $rqt="UPDATE IGNORE parametres set valeur_param='png' where type_param='opac' and sstype_param ='img_cache_type'";
                echo traite_addon_rqt($rqt,"change opac_img_cache_type to png into parametres");
            }
            break;
        case 11:
            // GN - Modification de la tables certificates
            $rqt ="ALTER TABLE certificates DROP COLUMN private_key";
            echo traite_addon_rqt($rqt,"ALTER TABLE certificates DROP COLUMN private_key");
            $rqt ="ALTER TABLE certificates DROP COLUMN name";
            echo traite_addon_rqt($rqt,"ALTER TABLE certificates DROP COLUMN name");
            $rqt ="ALTER TABLE certificates DROP COLUMN cert";
            echo traite_addon_rqt($rqt,"ALTER TABLE certificates DROP COLUMN cert");
            $rqt="ALTER TABLE certificates ADD settings MEDIUMBLOB NOT NULL DEFAULT ''";
            echo traite_addon_rqt($rqt,"ALTER TABLE certificates ADD settings");
            break;
        case 12:
            // QV - [IA] Template pour l'indexation des entites afin de calculer les embeddings
            $rqt = "CREATE TABLE IF NOT EXISTS embeddings_template (
                entity_type int(10) unsigned NOT NULL,
                template text NOT NULL DEFAULT '',
                PRIMARY KEY (entity_type)
            )";
            echo traite_addon_rqt($rqt, "CREATE TABLE embeddings_template");

            if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM embeddings_template WHERE entity_type=" . TYPE_NOTICE)) == 0) {
                global $base_path;
                $template = file_get_contents($base_path . '/includes/templates/embeddings_template/record.tpl.html');

                $rqt = "INSERT INTO embeddings_template (entity_type, template) VALUES (" . TYPE_NOTICE . ", '" . addslashes($template) . "')";
                echo traite_addon_rqt($rqt, "INSERT entity_type=" . TYPE_NOTICE . " INTO embeddings_template");
            }
            break;
        case 13:
            // DB & RT - Lien entre types et themes de demandes
            if (pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM demandes_theme LIKE 'allowed_types'")) == 0) {
                $rqt = "ALTER TABLE demandes_theme ADD allowed_types TEXT NOT NULL";
                echo traite_addon_rqt($rqt, "ALTER TABLE demandes_theme ADD allowed_types");
            }
            break;
        case 14:
            // JP & DG - Pouvoir forcer la remise en HT ou TTC
            //ROLLBACK
            break;
        case 15:
            // GN - Ajout d'un paramètre utilisateur pour les animations (Localisation par défaut)
            $rqt = "ALTER TABLE users ADD deflt_animation_location TINYINT UNSIGNED DEFAULT 0 NOT NULL ";
            echo traite_addon_rqt($rqt, "ALTER TABLE users ADD deflt_animation_location");
            break;
        case 16:
            // QV - Ajout du type de public dans les animations
            $rqt = "CREATE TABLE IF NOT EXISTS anim_public_type (
                id_public_type int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                label varchar(255) NOT NULL DEFAULT ''
            )";
            echo traite_addon_rqt($rqt, "CREATE TABLE anim_public_type");

            if (pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM anim_animations LIKE 'num_public_type'")) == 0) {
                $rqt = "ALTER TABLE anim_animations ADD num_public_type int(11) DEFAULT 0 NOT NULL";
                echo traite_addon_rqt($rqt, "ALTER TABLE anim_animations ADD num_public_type");
            }
            break;
        case 17:
        	// QV - Blocage avec le commentaire d'exemplaire (pret)
        	if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param = 'selfservice' and sstype_param='loan_expl_note_block'")) == 0) {
        		$rqt = "INSERT INTO parametres (id_param, type_param, sstype_param, valeur_param, gestion, comment_param)
                    VALUES (0, 'selfservice', 'loan_expl_note_block', '1', '1', 'Bloquer le document si un message exemplaire est défini') ";
        		echo traite_addon_rqt($rqt, "INSERT selfservice_loan_expl_note_block='1' INTO parametres") ;
        	}
        	break;
        case 18:
            // TS & DB - nettoyage des sources de vignettes comprenant gamannecy
            $query_ext_links = "SELECT *  FROM `thumbnail_sources` WHERE `class` LIKE '%RecordExternallinksThumbnailSource' AND settings LIKE '%gamannecy%'";
            $result_ext_links = pmb_mysql_query($query_ext_links);
            if (pmb_mysql_num_rows($result_ext_links)) {
                $row_ext_links = pmb_mysql_fetch_assoc($result_ext_links);
                $settings = json_decode($row_ext_links['settings'], true);

                //settings contient un tableau "links"
                if(!empty($settings['links']) && is_countable($settings['links'])) {

                    for ($i = 0; $i <= count($settings['links']); $i++) {
                        if ( ($settings['links'][$i]['name'] == 'gamannecy') || (stripos($settings['links'][$i]['url'], 'gamannecy') !== false) ) {
                            unset($settings['links'][$i]);
                        }
                    }

                    $settings['links'] = array_values($settings['links']);
                    $new_settings = json_encode($settings);
                    $q = "UPDATE thumbnail_sources SET settings = '" . addslashes($new_settings) . "' WHERE id = " . $row_ext_links['id'];
                    echo traite_addon_rqt($q, "UPDATE thumbnail_sources");

                //settings ne contient pas de tableau "links"
                } elseif (empty($settings['links']) && is_countable($settings)) {
                    for ($i = 0; $i <= count($settings); $i++) {
                        if ( ($settings[$i]['name'] == 'gamannecy') || (stripos($settings[$i]['url'], 'gamannecy') !== false) ) {
                            unset($settings[$i]);
                        }
                    }
                    $settings = array_values($settings);
                    $new_settings = json_encode($settings);
                    $q = "UPDATE thumbnail_sources SET settings = '" . addslashes($new_settings) . "' WHERE id = " . $row_ext_links['id'];
                    echo traite_addon_rqt($q, "UPDATE thumbnail_sources");
                }
            }
            break;
        case 19:
            //TS - Nettoyage et rechargement de l'autoload
            // nouvelle classe searcher_items.class.php
            netbase_cache::clean_autoload_files();
            break;
        case 20:
            // DB- Statut de notice par défaut en sélection de ligne commande
            if (!pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM users LIKE 'deflt3sel_notice_statut'"))){
                $rqt = "ALTER TABLE users ADD deflt3sel_notice_statut INT(3) UNSIGNED DEFAULT 0 NOT NULL" ;
                echo traite_addon_rqt($rqt,"ALTER users ADD deflt3sel_notice_statut");
            }
            break;
        case 21:
        	//RT - Suppression de la colonne dsi_tag.type pour les bases l'ayant encore
        	$query_test = "SHOW COLUMNS FROM dsi_tag LIKE 'type'";
        	$result = pmb_mysql_query($query_test);
        	if (pmb_mysql_num_rows($result) > 0) {
        		$q = "ALTER TABLE dsi_tag DROP COLUMN type";
        		echo traite_addon_rqt($q, "ALTER TABLE dsi_tag DROP COLUMN type");
        	}
        	break;
        case 22:
            // GN - Nouvelle indexation IA
            $rqt = "
                CREATE TABLE IF NOT EXISTS ai_clustering (
                id_clustering int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                type_object int(10) NOT NULL
            )";
            echo traite_addon_rqt($rqt, "CREATE TABLE ai_clustering");

            $rqt = "
                CREATE TABLE IF NOT EXISTS ai_clusters (
                id_cluster int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                type_object int(10) NOT NULL,
                num_clustering int(10) NOT NULL,
                embeddings mediumblob DEFAULT NULL
            )";
            echo traite_addon_rqt($rqt, "CREATE TABLE ai_clusters");

            $rqt = "
                CREATE TABLE IF NOT EXISTS ai_embeddings (
                id_embedding int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                sort int(11) NOT NULL DEFAULT 0,
                extra tinyblob DEFAULT NULL,
                num_object int(10) NOT NULL,
                num_type int(10) NOT NULL,
                num_cluster int(10) NOT NULL,
                embedding mediumblob DEFAULT NULL
            )";
            echo traite_addon_rqt($rqt, "CREATE TABLE ai_embeddings");

            // GN - Suppression des anciennes tables
            $rqt = "
                DROP TABLE IF EXISTS clusters";
            echo traite_addon_rqt($rqt, "DROP TABLE clusters");
            $rqt = "
                DROP TABLE IF EXISTS cluster_contents";
            echo traite_addon_rqt($rqt, "DROP TABLE cluster_contents");

            // GN - Suppression des colonnes d'indexation IA
            $query_test = "SHOW COLUMNS FROM notices LIKE 'embeddings'";
            $result = pmb_mysql_query($query_test);
            if (pmb_mysql_num_rows($result) > 0) {
                $q = "ALTER TABLE notices DROP COLUMN embeddings";
               echo traite_addon_rqt($q, "ALTER TABLE notices DROP COLUMN embeddings");
            }

            $query_test = "SHOW COLUMNS FROM explnum LIKE 'embeddings'";
            $result = pmb_mysql_query($query_test);
            if (pmb_mysql_num_rows($result) > 0) {
                $q = "ALTER TABLE explnum DROP COLUMN explnum_embeddings";
                echo traite_addon_rqt($q, "ALTER TABLE explnum DROP COLUMN explnum_embeddings");
            }

            // GN - Ajout d'un index sur ai_embeddings pour les colonnes num_object, num_type, num_cluster
            $rqt = "ALTER TABLE ai_embeddings ADD index i_num_object(num_type, num_object)";
            echo traite_addon_rqt($rqt, "ALTER TABLE ai_embeddings ADD index (num_type, num_object)");
            $rqt = "ALTER TABLE ai_embeddings ADD index i_num_cluster(num_cluster)";
            echo traite_addon_rqt($rqt, "ALTER TABLE ai_embeddings ADD index num_cluster");

            // GN - Ajout d'un index sur ai_cluster pour les colonnes type_object, num_clustering
            $rqt = "ALTER TABLE ai_clusters ADD index i_type_object(type_object)";
            echo traite_addon_rqt($rqt, "ALTER TABLE ai_clusters ADD index type_object");
            $rqt = "ALTER TABLE ai_clusters ADD index i_num_clustering(num_clustering)";
            echo traite_addon_rqt($rqt, "ALTER TABLE ai_clusters ADD index num_clustering");

            // GN - Ajout d'un index sur ai_clustering pour les colonnes type_object
            $rqt = "ALTER TABLE ai_clustering ADD index i_type_object(type_object)";
            echo traite_addon_rqt($rqt, "ALTER TABLE ai_clustering ADD index type_object");
            break;

        case 23:
            //DB - Ajout colonne shorturl_create_date + shorturl_is_valid sur table shorturls
            $query_test = "SHOW COLUMNS FROM shorturls LIKE 'shorturl_create_date'";
            $result = pmb_mysql_query($query_test);
            if (!pmb_mysql_num_rows($result)) {
                $q = "ALTER TABLE shorturls ADD shorturl_create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD INDEX i_shorturl_create_date (shorturl_create_date)";
                echo traite_addon_rqt($q, "ALTER TABLE shorturls ADD shorturl_create_date");
            }
            $query_test = "SHOW COLUMNS FROM shorturls LIKE 'shorturl_is_valid'";
            $result = pmb_mysql_query($query_test);
            if (!pmb_mysql_num_rows($result)) {
                $q = "ALTER TABLE shorturls ADD shorturl_is_valid TINYINT(1) NOT NULL DEFAULT '1' ";
                echo traite_addon_rqt($q, "ALTER TABLE shorturls ADD shorturl_is_valid");
            }
            break;
        case 24:
            // DG - ajout d'une option rgaa pour le parametre show_onglet_perio_a2z
            $rqt = "UPDATE parametres SET comment_param = 'Activer l\'onglet du navigateur de périodiques en OPAC.\n 0 : Non.\n 1 : Oui.\n 2 : Oui (compatible RGAA).' WHERE type_param = 'opac' AND sstype_param = 'show_onglet_perio_a2z'  ";
            echo traite_addon_rqt($rqt, "UPDATE opac_show_onglet_perio_a2z INTO parametres");
            break;
        case 25:
        	// RT - Structures de données import/export
        	$rqt = "CREATE TABLE IF NOT EXISTS import_export_data_structures (
	            id_data_structure int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	            data_structure_name varchar(255) NOT NULL DEFAULT '',
	            data_structure_comment text NOT NULL,
	            data_structure_format varchar(50) NOT NULL DEFAULT '',
	            data_structure_store_name varchar(100) NOT NULL DEFAULT ''
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS import_export_data_structures");

        	//RT - Conversion universelle import/export
        	$rqt = "CREATE TABLE IF NOT EXISTS import_export_conversions (
	            id_conversion int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	            conversion_name varchar(255) NOT NULL DEFAULT '',
	            conversion_comment text NOT NULL DEFAULT '',
	            source_schema_type varchar(50) NOT NULL DEFAULT 'datastructure',
	            source_entry_ref varchar(255) NOT NULL DEFAULT '',
	            target_schema_type varchar(50) NOT NULL DEFAULT 'ontology_pmb',
	            target_entry_ref varchar(255) NOT NULL DEFAULT '',
	            conversion_settings mediumblob NOT NULL
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS import_export_conversions");
        	break;
        case 26:
        	// DB & QV - Ajout droits de version des thésaurus
        	$query_test = "SHOW COLUMNS FROM thesaurus LIKE 'version_enabled'";
        	$result = pmb_mysql_query($query_test);
        	if (!pmb_mysql_num_rows($result)) {
        		$rqt = "ALTER TABLE thesaurus ADD version_enabled INT(1) NOT NULL DEFAULT 0 ";
        		echo traite_addon_rqt($rqt, "ALTER TABLE thesaurus ADD version_enabled");
        	}
        	$query_test = "SHOW COLUMNS FROM thesaurus LIKE 'num_thesaurus_version'";
        	$result = pmb_mysql_query($query_test);
        	if (!pmb_mysql_num_rows($result)) {
        		$rqt = "ALTER TABLE thesaurus ADD num_thesaurus_version INT(1) NOT NULL DEFAULT 0 ";
        		echo traite_addon_rqt($rqt, "ALTER TABLE thesaurus ADD num_thesaurus_version");
        	}
        	$query_test = "SHOW COLUMNS FROM thesaurus LIKE 'version_rights'";
        	$result = pmb_mysql_query($query_test);
        	if (!pmb_mysql_num_rows($result)) {
        		$rqt = "ALTER TABLE thesaurus ADD version_rights MEDIUMTEXT NOT NULL DEFAULT ' 1 ' ";
        		echo traite_addon_rqt($rqt, "ALTER TABLE thesaurus ADD version_rights");
        	}
        	$query_test = "SHOW COLUMNS FROM thesaurus LIKE 'version_rights_all'";
        	$result = pmb_mysql_query($query_test);
        	if (!pmb_mysql_num_rows($result)) {
        		$rqt = "ALTER TABLE thesaurus ADD version_rights_all INT(1) NOT NULL DEFAULT 1";
        		echo traite_addon_rqt($rqt, "ALTER TABLE thesaurus ADD version_rights_all");
        	}
        	// DB & QV - Creation des tables pour le versionnage des thesaurus
        	$rqt = "CREATE TABLE IF NOT EXISTS thesaurus_version (
                id_version INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                num_thesaurus INT(10) UNSIGNED NOT NULL DEFAULT 0,
                num_parent_version INT(10) UNSIGNED NOT NULL DEFAULT 0,
                name VARCHAR(255) NOT NULL DEFAULT '',
                num VARCHAR(50) NOT NULL DEFAULT '',
                skos MEDIUMTEXT,
                is_current TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX i_num_thesaurus (num_thesaurus),
                INDEX i_num_parent_version (num_parent_version)
            )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE thesaurus_version");
        	$rqt = "CREATE TABLE IF NOT EXISTS thesaurus_changelog (
	            id_changelog INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	            user_name varchar(20) NOT NULL DEFAULT '',
	            num_version INT(10) UNSIGNED NOT NULL DEFAULT 0,
	            command ENUM('CREATE', 'UPDATE', 'ADD', 'REMOVE', 'REPLACE', 'DELETE') NOT NULL DEFAULT 'CREATE',
	            type ENUM('label', 'comment', 'note', 'see', 'see_also', 'parent', 'not_use_in_indexation', 'autorite') DEFAULT NULL,
	            options MEDIUMTEXT,
	            rollback MEDIUMTEXT,
	            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	            INDEX i_num_version (num_version)
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE thesaurus_changelog");
        	break;
        case 27:
        	// QV & DB - Nettoyage des noeuds TOP incorrectement inseres dans authorities
        	// Bug corrige dans authority::object_exists() : condition "autorite != 'TOP'" manquante
        	$query_count_top = "SELECT COUNT(*) AS cnt
            FROM authorities
            INNER JOIN noeuds ON noeuds.id_noeud = authorities.num_object
            WHERE authorities.type_object = " . AUT_TABLE_CATEG . "
            AND noeuds.autorite = 'TOP'";
        	$result_count_top = pmb_mysql_query($query_count_top);
        	$row_count_top = pmb_mysql_fetch_assoc($result_count_top);
        	$nb_top = intval($row_count_top['cnt']);

        	if ($nb_top > 0) {
        		$query_top = "SELECT authorities.id_authority, authorities.num_object
                FROM authorities
                INNER JOIN noeuds ON noeuds.id_noeud = authorities.num_object
                WHERE authorities.type_object = " . AUT_TABLE_CATEG . "
                AND noeuds.autorite = 'TOP'";
        		$result_top = pmb_mysql_query($query_top);

        		$ids_authority  = array();
        		$ids_num_object = array();
        		while ($row_top = pmb_mysql_fetch_assoc($result_top)) {
        			$ids_authority[]  = intval($row_top['id_authority']);
        			$ids_num_object[] = intval($row_top['num_object']);
        		}
        		pmb_mysql_free_result($result_top);

        		$ids_authority_sql  = implode(',', $ids_authority);
        		$ids_num_object_sql = implode(',', $ids_num_object);

        		// Suppression des paniers
        		pmb_mysql_query("DELETE FROM authorities_caddie_content WHERE object_id IN (" . $ids_authority_sql . ")");

        		// Suppression de l'indexation
        		pmb_mysql_query("DELETE FROM authorities_fields_global_index WHERE id_authority IN (" . $ids_authority_sql . ")");
        		pmb_mysql_query("DELETE FROM authorities_words_global_index WHERE id_authority IN (" . $ids_authority_sql . ")");

        		// Suppression des champs perso (type categ => prefixe categ)
        		pmb_mysql_query("DELETE FROM categ_custom_values WHERE categ_custom_origine IN (" . $ids_num_object_sql . ")");
        		pmb_mysql_query("DELETE FROM categ_custom_dates WHERE categ_custom_origine IN (" . $ids_num_object_sql . ")");

        		// Suppression ARK si module active
        		global $pmb_ark_activate;
        		if ($pmb_ark_activate) {
        			$result_arks = pmb_mysql_query("SELECT num_ark FROM ark_entity_pmb WHERE entity_id IN (" . $ids_authority_sql . ") AND entity_type = " . TYPE_AUTHORITY);
        			$ids_ark = array();
        			while ($row_ark = pmb_mysql_fetch_assoc($result_arks)) {
        				$ids_ark[] = intval($row_ark['num_ark']);
        			}
        			pmb_mysql_free_result($result_arks);
        			pmb_mysql_query("DELETE FROM ark_entity_pmb WHERE entity_id IN (" . $ids_authority_sql . ") AND entity_type = " . TYPE_AUTHORITY);
        			if (!empty($ids_ark)) {
        				pmb_mysql_query("DELETE FROM ark WHERE id IN (" . implode(',', $ids_ark) . ")");
        			}
        		}

        		// Suppression des entrees authorities incorrectes
        		pmb_mysql_query("DELETE FROM authorities WHERE id_authority IN (" . $ids_authority_sql . ")");

        		$rqt = "SELECT 1";
        		echo traite_addon_rqt($rqt, "Nettoyage de " . $nb_top . " noeud(s) TOP incorrectement inséré(s) dans authorities");
            }
            break;
        case 28 :
            // DB - Ajout index sur la table ark
            $add_index = true;
            $req = "SHOW INDEX FROM ark WHERE Key_name='i_identifier' ";
            $res = pmb_mysql_query($req);
            if($res && pmb_mysql_num_rows($res)){
                $add_index=false;
            }
            if($add_index){
                $rqt = "ALTER TABLE ark ADD INDEX i_identifier (identifier)";
                echo traite_addon_rqt($rqt, "ALTER TABLE ark ADD INDEX i_identifier");
            }
            // DB - Ajout index sur la table ark_entity_pmb
            $add_index = true;
            $req = "SHOW INDEX FROM ark_entity_pmb WHERE Key_name='i_entity_id_entity_type' ";
            $res = pmb_mysql_query($req);
            if($res && pmb_mysql_num_rows($res)){
                $add_index=false;
            }
            if($add_index){
                $rqt = "ALTER TABLE ark_entity_pmb ADD INDEX i_entity_id_entity_type (entity_id, entity_type)";
                echo traite_addon_rqt($rqt, "ALTER TABLE ark_entity_pmb ADD INDEX i_entity_id_entity_type");
            }
            break;
        case 29 :
        	//RT - Imports / Exports - Ajout d'une table pour gérer la progression de l'exécution d'un scénario
        	$rqt = "CREATE TABLE IF NOT EXISTS import_export_execution_progress (
	            id_progress  int(11)     NOT NULL NOT NULL AUTO_INCREMENT PRIMARY KEY,
	            run_token    varchar(64) NOT NULL DEFAULT '',
	            num_scenario int(11)     NOT NULL DEFAULT 0,
	            input        mediumblob  NOT NULL,
	            status       varchar(20) NOT NULL DEFAULT 'running',
	            pid          int(11)     NOT NULL DEFAULT 0,
	            payload      mediumblob  NOT NULL,
	            created      datetime    NOT NULL,
	            updated      datetime    NOT NULL,
	            id_user      int(11)     NOT NULL DEFAULT 0,
	            UNIQUE KEY idx_token (run_token)
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS import_export_execution_progress");
        	break;
        case 30:
        	if (pmb_mysql_num_rows(pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param= 'selfservice' and sstype_param='retour_expl_inconnu_msg'"))==0){
        		$rqtPret = 'SELECT valeur_param FROM parametres WHERE type_param = "selfservice" AND sstype_param = "pret_expl_inconnu_msg"';
        		$resPret = pmb_mysql_query($rqtPret);
        		if ($resPret) {
        			$rowPret = pmb_mysql_fetch_assoc($resPret);
        			$valeurPret = $rowPret['valeur_param'];
        		} else {
        			$valeurPret = 'Ce document est inconnu';
        		}

        		$rqt = "INSERT INTO parametres (id_param, type_param, sstype_param, valeur_param, gestion, comment_param)
                    VALUES (0, 'selfservice', 'retour_expl_inconnu_msg', '". addslashes($valeurPret) ."', '1', 'Message borne de prêt: Ce document est inconnu') ";
        		echo traite_addon_rqt($rqt,"INSERT selfservice_retour_expl_inconnu_msg INTO parametres") ;
        	}
        	break;
        case 31:
        	// RT - Imports / Exports - Ajout d'un commentaire de lancement (tracabilite) sur les executions de scenario
        	if (pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM import_export_execution_progress LIKE 'run_comment'")) == 0) {
        		$rqt = "ALTER TABLE import_export_execution_progress ADD run_comment text NOT NULL AFTER id_user";
        		echo traite_addon_rqt($rqt, "ALTER TABLE import_export_execution_progress ADD run_comment");
        	}
        	
        	// RT - Imports / Exports - Ajout d'une table pour journaliser les actions de conception (creation/modification/suppression) sur les elements du module
        	$rqt = "CREATE TABLE IF NOT EXISTS import_export_activity_log (
	            id_log       int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
	            id_user      int(11)      NOT NULL DEFAULT 0,
	            action       varchar(50)  NOT NULL DEFAULT '',
	            object_type  varchar(50)  NOT NULL DEFAULT '',
	            object_id    int(11)      NOT NULL DEFAULT 0,
	            object_label varchar(255) NOT NULL DEFAULT '',
	            comment      text         NOT NULL,
	            created      datetime     NOT NULL,
	            KEY idx_object (object_type, object_id)
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS import_export_activity_log");
        	
        	// RT - Imports / Exports - Ajout d'une table generique de verrouillage optimiste (par version), independante des tables metier, extensible a tout element du module par simple valeur d'object_type
        	$rqt = "CREATE TABLE IF NOT EXISTS import_export_object_locks (
	            object_type varchar(50) NOT NULL DEFAULT '',
	            object_id   int(11)     NOT NULL DEFAULT 0,
	            version     int(11)     NOT NULL DEFAULT 0,
	            updated     datetime    NOT NULL,
	            updated_by  int(11)     NOT NULL DEFAULT 0,
	            PRIMARY KEY (object_type, object_id)
	        )";
        	echo traite_addon_rqt($rqt, "CREATE TABLE IF NOT EXISTS import_export_object_locks");
        	break;
        case 32:
            // DG - Consentement RGPD/CNIL au tracage des ouvertures et des clics dans les mails de campagnes
            if (!pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM empr LIKE 'empr_mail_tracking_open'"))){
                $rqt = "ALTER TABLE empr ADD empr_mail_tracking_open INT(1) UNSIGNED DEFAULT 0 NOT NULL AFTER empr_mail" ;
                echo traite_addon_rqt($rqt,"ALTER empr ADD empr_mail_tracking_open");
                
                // On passe le consentement sur les ouvertures à Oui par défaut
                $rqt = "UPDATE empr SET empr_mail_tracking_open = 1" ;
                echo traite_addon_rqt($rqt,"UPDATE empr SET empr_mail_tracking_open = 1");
            }
            if (!pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM empr LIKE 'empr_mail_tracking_click'"))){
                $rqt = "ALTER TABLE empr ADD empr_mail_tracking_click INT(1) UNSIGNED DEFAULT 0 NOT NULL AFTER empr_mail_tracking_open" ;
                echo traite_addon_rqt($rqt,"ALTER empr ADD empr_mail_tracking_click");
                
                // On passe le consentement sur les clics à Oui par défaut
                $rqt = "UPDATE empr SET empr_mail_tracking_click = 1" ;
                echo traite_addon_rqt($rqt,"UPDATE empr SET empr_mail_tracking_click = 1");
            }
            
            // DG - Consentement RGPD/CNIL au tracage des ouvertures et des clics dans les mails de campagnes
            if (!pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM campaigns_recipients LIKE 'campaign_recipient_empr_mail_tracking_open'"))){
                $rqt = "ALTER TABLE campaigns_recipients ADD campaign_recipient_empr_mail_tracking_open INT(1) UNSIGNED DEFAULT 0 NOT NULL" ;
                echo traite_addon_rqt($rqt,"ALTER campaigns_recipients ADD campaign_recipient_empr_mail_tracking_open");
                
                // On passe le consentement sur l'historique des ouvertures à Oui par défaut
                $rqt = "UPDATE campaigns_recipients SET campaign_recipient_empr_mail_tracking_open = 1" ;
                echo traite_addon_rqt($rqt,"UPDATE campaigns_recipients SET campaign_recipient_empr_mail_tracking_open = 1");
            }
            if (!pmb_mysql_num_rows(pmb_mysql_query("SHOW COLUMNS FROM campaigns_recipients LIKE 'campaign_recipient_empr_mail_tracking_click'"))){
                $rqt = "ALTER TABLE campaigns_recipients ADD campaign_recipient_empr_mail_tracking_click INT(1) UNSIGNED DEFAULT 0 NOT NULL" ;
                echo traite_addon_rqt($rqt,"ALTER campaigns_recipients ADD campaign_recipient_empr_mail_tracking_click");
                
                // On passe le consentement sur l'historique des clics à Oui par défaut
                $rqt = "UPDATE campaigns_recipients SET campaign_recipient_empr_mail_tracking_click = 1" ;
                echo traite_addon_rqt($rqt,"UPDATE campaigns_recipients SET campaign_recipient_empr_mail_tracking_click = 1");
            }
            
            // DG - Parametre pour cocher par defaut les 2 cases de consentement RGPD/CNIL a la creation d'un lecteur (gestion et OPAC)
            if (pmb_mysql_num_rows(pmb_mysql_query("select 1 from parametres where type_param= 'empr' and sstype_param='mail_tracking_consent_default' "))==0){
                $rqt = "INSERT INTO parametres (id_param, type_param, sstype_param, valeur_param, comment_param, gestion)
                VALUES (0, 'empr', 'mail_tracking_consent_default', 0, 'Cocher par défaut les cases de consentement au traçage des mails de campagne (ouverture et clics) à la création d\'un compte lecteur, en gestion et à l\'OPAC : 0 : non 1 : oui', 0)";
                echo traite_addon_rqt($rqt,"insert empr_mail_tracking_consent_default into parametres");
            }
            break;

    }
    $db_update_step ++;
}

/**
 * ****************** JUSQU'ICI *************************************************
 */
/* PENSER à faire +1 au paramètre $pmb_subversion_database_as_it_shouldbe dans includes/config.inc.php */
/* COMMITER les deux fichiers addon.inc.php ET config.inc.php en même temps */
echo traite_addon_rqt("update parametres set valeur_param='" . $db_update_step . "' where type_param='pmb' and sstype_param='bdd_subversion'", "Update to $db_update_step database subversion.");
echo "<table>";
