<?php
/**
 * Schema Migrator
 *
 * Crée le schéma 1.3.2 pour les nouvelles installations et migre les installations
 * existantes par étapes reprenables (chaque étape est idempotente et bornée dans le temps),
 * afin de ne jamais bloquer un hébergement mutualisé sur une grosse table.
 *
 * @package   almaweb_ai_visitor_analytics
 * @since     1.3.2
 * @author    Yohan Ziri (AlmaWeb)
 * @link      https://almaweb.fr
 */

if (!defined('ABSPATH')) {
    exit;
}

class Almaweb_Schema_Migrator {

    const TARGET_VERSION = '1.3.2';
    const TOTAL_STEPS    = 6;
    const STATE_OPTION   = 'almaweb_migration_state';
    const LOCK_TRANSIENT = 'almaweb_migration_lock';
    const RETRY_TRANSIENT = 'almaweb_migration_retry_after';
    const TIME_BUDGET    = 20;    // secondes de travail par requête admin
    const DELETE_BATCH   = 20000;
    const UA_BATCH       = 50000;

    /** Colonnes qui disparaissent en 1.3.2 (y compris les reliquats que la 1.2.2 devait retirer). */
    private static $dead_columns = [
        'user_agent', 'http_accept', 'session_duration', 'pages_in_session', 'is_bounce',
        'is_return_visitor', 'has_conversion', 'conversion_type', 'conversion_value',
        'country', 'city', 'time_spent', 'session_id', 'http_accept_encoding', 'request_method',
    ];

    /** Index mono-colonne remplacés par des index composites ou inutiles. */
    private static $dead_indexes = [
        'ip_address', 'bot_name', 'bot_type', 'is_stealth', 'detection_method', 'ai_referrer_source',
        'ai_referrer_family', 'is_ai_bot', 'is_from_ai', 'bot_family', 'bot_intention', 'session_id', 'has_conversion',
    ];

    /** Types cibles des colonnes conservées : colonne => [définition SQL, longueur max]. */
    private static $final_columns = [
        'ip_address'           => ["varchar(45) NOT NULL DEFAULT ''", 45],
        'bot_name'             => ['varchar(100) DEFAULT NULL', 100],
        'bot_type'             => ['varchar(50) DEFAULT NULL', 50],
        'bot_family'           => ['varchar(50) DEFAULT NULL', 50],
        'bot_intention'        => ['varchar(50) DEFAULT NULL', 50],
        'detection_method'     => ["varchar(20) DEFAULT 'user_agent'", 20],
        'request_url'          => ["varchar(1200) NOT NULL DEFAULT ''", 1200],
        'referer'              => ['varchar(500) DEFAULT NULL', 500],
        'action_type'          => ["varchar(50) DEFAULT 'visit'", 50],
        'page_visited'         => ['varchar(500) DEFAULT NULL', 500],
        'http_accept_language' => ['varchar(100) DEFAULT NULL', 100],
        'ai_referrer_source'   => ['varchar(200) DEFAULT NULL', 200],
        'ai_referrer_type'     => ['varchar(50) DEFAULT NULL', 50],
        'ai_referrer_family'   => ['varchar(100) DEFAULT NULL', 100],
    ];

    /** Index finaux : nom => colonnes. */
    private static $final_indexes = [
        'visited_at'        => '`visited_at`',
        'ai_visited'        => '`is_ai_bot`,`visited_at`',
        'from_ai_visited'   => '`is_from_ai`,`visited_at`',
        'intention_visited' => '`bot_intention`,`visited_at`',
        'family_visited'    => '`bot_family`,`visited_at`',
        'is_blocked'        => '`is_blocked`',
    ];

    /* ------------------------------------------------------------------
     * SCHÉMA NEUF (activation)
     * ---------------------------------------------------------------- */

    /**
     * Crée les tables pour une nouvelle installation. Sur une installation existante,
     * ne crée que les tables annexes et laisse la migration pas à pas faire le reste
     * (ne touche ni au stade du schéma ni à la version DB).
     */
    public static function create_fresh_schema() {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $logs  = Almaweb_Database::logs_table();
        $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $logs)) === $logs;

        dbDelta(self::daily_table_sql());
        dbDelta(self::ua_table_sql());

        if ($exists) {
            return; // installation existante : run_upgrade_routine() prend le relais
        }

        dbDelta(self::logs_table_sql());
        update_option('almaweb_schema_stage', Almaweb_Database::STAGE_FINAL, true);
        update_option('almaweb_db_version', ALMAWEB_VERSION);
    }

    private static function logs_table_sql() {
        global $wpdb;
        $table = Almaweb_Database::logs_table();
        $charset_collate = $wpdb->get_charset_collate();
        return "CREATE TABLE {$table} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            ua_id int(10) UNSIGNED DEFAULT NULL,
            ip_address varchar(45) NOT NULL DEFAULT '',
            bot_name varchar(100) DEFAULT NULL,
            bot_type varchar(50) DEFAULT NULL,
            bot_family varchar(50) DEFAULT NULL,
            bot_intention varchar(50) DEFAULT NULL,
            is_ai_bot tinyint(1) DEFAULT 0,
            is_stealth tinyint(1) DEFAULT 0,
            detection_method varchar(20) DEFAULT 'user_agent',
            request_url varchar(1200) NOT NULL DEFAULT '',
            referer varchar(500) DEFAULT NULL,
            action_type varchar(50) DEFAULT 'visit',
            page_visited varchar(500) DEFAULT NULL,
            http_accept_language varchar(100) DEFAULT NULL,
            visited_at datetime DEFAULT NULL,
            ai_referrer_source varchar(200) DEFAULT NULL,
            ai_referrer_type varchar(50) DEFAULT NULL,
            ai_referrer_family varchar(100) DEFAULT NULL,
            is_from_ai tinyint(1) DEFAULT 0,
            is_blocked tinyint(1) DEFAULT 0,
            PRIMARY KEY  (id),
            KEY visited_at (visited_at),
            KEY ai_visited (is_ai_bot,visited_at),
            KEY from_ai_visited (is_from_ai,visited_at),
            KEY intention_visited (bot_intention,visited_at),
            KEY family_visited (bot_family,visited_at),
            KEY is_blocked (is_blocked)
        ) $charset_collate;";
    }

    private static function daily_table_sql() {
        global $wpdb;
        $table = Almaweb_Database::daily_table();
        $charset_collate = $wpdb->get_charset_collate();
        return "CREATE TABLE {$table} (
            visit_date date NOT NULL,
            total_visits int(10) UNSIGNED NOT NULL DEFAULT 0,
            ai_visits int(10) UNSIGNED NOT NULL DEFAULT 0,
            human_visits int(10) UNSIGNED NOT NULL DEFAULT 0,
            bot_visits int(10) UNSIGNED NOT NULL DEFAULT 0,
            PRIMARY KEY  (visit_date)
        ) $charset_collate;";
    }

    private static function ua_table_sql() {
        global $wpdb;
        $table = Almaweb_Database::ua_table();
        $charset_collate = $wpdb->get_charset_collate();
        return "CREATE TABLE {$table} (
            id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            ua_hash char(32) NOT NULL,
            user_agent varchar(500) NOT NULL DEFAULT '',
            PRIMARY KEY  (id),
            UNIQUE KEY ua_hash (ua_hash)
        ) $charset_collate;";
    }

    /* ------------------------------------------------------------------
     * MIGRATION PAS À PAS
     * ---------------------------------------------------------------- */

    public static function get_progress() {
        $state = get_option(self::STATE_OPTION, []);
        $step  = isset($state['step']) ? (int) $state['step'] : 0;
        return ['step' => min($step + 1, self::TOTAL_STEPS), 'total' => self::TOTAL_STEPS, 'started' => !empty($state)];
    }

    /**
     * Fait avancer la migration dans la limite du budget temps.
     *
     * @return bool true quand la migration est entièrement terminée
     */
    public static function run() {
        if (get_transient(self::RETRY_TRANSIENT)) {
            return false; // une erreur vient de se produire, on laisse souffler la base
        }
        if (get_transient(self::LOCK_TRANSIENT)) {
            return false; // une autre requête admin travaille déjà
        }
        set_transient(self::LOCK_TRANSIENT, 1, 2 * MINUTE_IN_SECONDS);

        if (function_exists('set_time_limit')) {
            @set_time_limit(300); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
        }
        if (function_exists('wp_raise_memory_limit')) {
            wp_raise_memory_limit('admin');
        }

        $deadline = microtime(true) + self::TIME_BUDGET;
        $state    = get_option(self::STATE_OPTION, []);
        if (empty($state)) {
            $state = ['step' => 0, 'cutoff_id' => 0, 'ua_cursor' => 0, 'rebuild_phase' => 0, 'started' => time()];
        }

        $steps = ['step_create_tables', 'step_backfill_counters', 'step_purge_non_ai', 'step_backfill_user_agents', 'step_rebuild_table', 'step_finalize'];
        $done  = false;

        try {
            while ($state['step'] < count($steps)) {
                $method = $steps[$state['step']];
                $result = self::$method($state, $deadline);
                if ($result === false) {
                    break; // budget épuisé : on reprendra ici
                }
                $state['step']++;
                update_option(self::STATE_OPTION, $state, false);
                if (microtime(true) > $deadline) {
                    break;
                }
            }
            $done = $state['step'] >= count($steps);
        } catch (Exception $e) {
            set_transient('almaweb_upgrade_error', 'Migration 1.3.2 (step ' . ($state['step'] + 1) . '): ' . $e->getMessage(), 10 * MINUTE_IN_SECONDS);
            set_transient(self::RETRY_TRANSIENT, 1, 2 * MINUTE_IN_SECONDS);
            update_option(self::STATE_OPTION, $state, false);
        }

        delete_transient(self::LOCK_TRANSIENT);

        if ($done) {
            delete_option(self::STATE_OPTION);
        }
        return $done;
    }

    /* ---------------- étapes ---------------- */

    /**
     * Étape 1 : tables annexes, colonnes ua_id / is_blocked, point de coupure, stade transition.
     */
    private static function step_create_tables(&$state, $deadline) {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $logs = Almaweb_Database::logs_table();

        dbDelta(self::daily_table_sql());
        dbDelta(self::ua_table_sql());

        $columns = self::columns($logs);
        if (!in_array('is_blocked', $columns, true)) {
            self::query("ALTER TABLE {$logs} ADD COLUMN `is_blocked` tinyint(1) DEFAULT 0");
        }
        if (!in_array('ua_id', $columns, true)) {
            self::query("ALTER TABLE {$logs} ADD COLUMN `ua_id` int(10) UNSIGNED DEFAULT NULL");
        }

        $state['cutoff_id'] = (int) $wpdb->get_var("SELECT COALESCE(MAX(id), 0) FROM {$logs}");
        update_option('almaweb_schema_stage', Almaweb_Database::STAGE_TRANSITION, true);
        return true;
    }

    /**
     * Étape 2 : compteurs journaliers reconstitués à partir des lignes existantes (une seule requête atomique).
     */
    private static function step_backfill_counters(&$state, $deadline) {
        global $wpdb;
        $logs   = Almaweb_Database::logs_table();
        $daily  = Almaweb_Database::daily_table();
        $cutoff = (int) $state['cutoff_id'];
        if ($cutoff <= 0) {
            return true;
        }
        $today  = current_time('Y-m-d');
        $select = "SELECT DATE(visited_at),
                    COUNT(*),
                    SUM(CASE WHEN is_ai_bot = 1 THEN 1 ELSE 0 END),
                    SUM(CASE WHEN is_ai_bot = 1 THEN 0 WHEN bot_type IS NULL OR bot_type = '' OR bot_type = 'human' THEN 1 ELSE 0 END),
                    SUM(CASE WHEN is_ai_bot = 1 THEN 0 WHEN bot_type IS NULL OR bot_type = '' OR bot_type = 'human' THEN 0 ELSE 1 END)
             FROM {$logs}
             WHERE id <= {$cutoff} AND visited_at IS NOT NULL AND visited_at > '1971-01-01 00:00:00'";

        // Jours passés : valeurs remplacées (rejouable sans double comptage)
        if (empty($state['counters_past_done'])) {
            self::query($wpdb->prepare(
                "INSERT INTO {$daily} (visit_date, total_visits, ai_visits, human_visits, bot_visits)
                 {$select} AND visited_at < %s
                 GROUP BY DATE(visited_at)
                 ON DUPLICATE KEY UPDATE
                    total_visits = VALUES(total_visits),
                    ai_visits    = VALUES(ai_visits),
                    human_visits = VALUES(human_visits),
                    bot_visits   = VALUES(bot_visits)",
                $today . ' 00:00:00'
            ));
            $state['counters_past_done'] = 1;
            update_option(self::STATE_OPTION, $state, false);
        }

        // Aujourd'hui : cumul avec les visites déjà comptées en direct depuis l'étape 1
        self::query($wpdb->prepare(
            "INSERT INTO {$daily} (visit_date, total_visits, ai_visits, human_visits, bot_visits)
             {$select} AND visited_at >= %s
             GROUP BY DATE(visited_at)
             ON DUPLICATE KEY UPDATE
                total_visits = total_visits + VALUES(total_visits),
                ai_visits    = ai_visits + VALUES(ai_visits),
                human_visits = human_visits + VALUES(human_visits),
                bot_visits   = bot_visits + VALUES(bot_visits)",
            $today . ' 00:00:00'
        ));
        return true;
    }

    /**
     * Étape 3 : suppression par lots des visites humaines / bots non identifiés (désormais comptées, plus stockées).
     */
    private static function step_purge_non_ai(&$state, $deadline) {
        global $wpdb;
        $logs   = Almaweb_Database::logs_table();
        $cutoff = (int) $state['cutoff_id'];
        $batch  = (int) self::DELETE_BATCH;
        do {
            $affected = self::query("DELETE FROM {$logs} WHERE is_ai_bot = 0 AND is_from_ai = 0 AND id <= {$cutoff} LIMIT {$batch}");
            if ($affected < $batch) {
                return true;
            }
        } while (microtime(true) < $deadline);
        return false;
    }

    /**
     * Étape 4 : table de référence des user-agents et remplissage de ua_id par tranches d'identifiants.
     */
    private static function step_backfill_user_agents(&$state, $deadline) {
        global $wpdb;
        $logs = Almaweb_Database::logs_table();
        $ua   = Almaweb_Database::ua_table();

        if (!in_array('user_agent', self::columns($logs), true)) {
            return true; // déjà migré
        }

        if (empty($state['ua_seeded'])) {
            self::query(
                "INSERT IGNORE INTO {$ua} (ua_hash, user_agent)
                 SELECT DISTINCT MD5(user_agent), LEFT(user_agent, 500)
                 FROM {$logs}
                 WHERE ua_id IS NULL AND user_agent IS NOT NULL AND user_agent <> ''"
            );
            $state['ua_seeded'] = 1;
            $state['ua_max_id'] = (int) $wpdb->get_var("SELECT COALESCE(MAX(id), 0) FROM {$logs}");
            update_option(self::STATE_OPTION, $state, false);
        }

        $batch = (int) self::UA_BATCH;
        while ($state['ua_cursor'] < $state['ua_max_id']) {
            $from = (int) $state['ua_cursor'];
            $to   = $from + $batch;
            self::query(
                "UPDATE {$logs} l INNER JOIN {$ua} u ON u.ua_hash = MD5(l.user_agent)
                 SET l.ua_id = u.id
                 WHERE l.ua_id IS NULL AND l.id > {$from} AND l.id <= {$to}"
            );
            $state['ua_cursor'] = $to;
            update_option(self::STATE_OPTION, $state, false);
            if (microtime(true) > $deadline) {
                return false;
            }
        }
        return true;
    }

    /**
     * Étape 5 : reconstruction de la table (colonnes mortes, types réduits, index composites).
     * Phase 0 : troncature préventive des valeurs trop longues.
     * Phase 1 : ALTER colonnes (une seule reconstruction).
     * Phase 2 : ALTER index.
     */
    private static function step_rebuild_table(&$state, $deadline) {
        global $wpdb;
        $logs = Almaweb_Database::logs_table();

        if ($state['rebuild_phase'] < 1) {
            $columns = self::columns($logs);
            foreach (self::$final_columns as $col => $def) {
                if (in_array($col, $columns, true)) {
                    $len = (int) $def[1];
                    self::query("UPDATE {$logs} SET `{$col}` = LEFT(`{$col}`, {$len}) WHERE CHAR_LENGTH(`{$col}`) > {$len}");
                }
            }
            $state['rebuild_phase'] = 1;
            update_option(self::STATE_OPTION, $state, false);
        }

        if ($state['rebuild_phase'] < 2) {
            // Si PHP est tué pendant l'ALTER, MySQL l'annule : on retente, mais pas indéfiniment.
            $state['rebuild_attempts'] = isset($state['rebuild_attempts']) ? (int) $state['rebuild_attempts'] + 1 : 1;
            update_option(self::STATE_OPTION, $state, false);
            if ($state['rebuild_attempts'] > 3) {
                set_transient(self::RETRY_TRANSIENT, 1, 12 * HOUR_IN_SECONDS);
                throw new Exception('the table rebuild was interrupted 3 times (PHP time limit?). Please retry later or increase max_execution_time.');
            }

            $columns = self::columns($logs);
            $parts   = [];
            foreach (self::$dead_columns as $col) {
                if (in_array($col, $columns, true)) {
                    $parts[] = "DROP COLUMN `{$col}`";
                }
            }
            foreach (self::$final_columns as $col => $def) {
                if (in_array($col, $columns, true)) {
                    $parts[] = "MODIFY COLUMN `{$col}` {$def[0]}";
                }
            }
            if (!empty($parts)) {
                self::query("ALTER TABLE {$logs} " . implode(', ', $parts));
            }
            // L'ancienne colonne user_agent n'existe plus : les insertions doivent passer au format final dès maintenant.
            update_option('almaweb_schema_stage', Almaweb_Database::STAGE_FINAL, true);
            $state['rebuild_phase'] = 2;
            update_option(self::STATE_OPTION, $state, false);
            if (microtime(true) > $deadline) {
                return false;
            }
        }

        $indexes = self::indexes($logs);
        $parts   = [];
        foreach (self::$dead_indexes as $idx) {
            if (in_array($idx, $indexes, true) && !isset(self::$final_indexes[$idx])) {
                $parts[] = "DROP INDEX `{$idx}`";
            }
        }
        foreach (self::$final_indexes as $idx => $cols) {
            if (!in_array($idx, $indexes, true)) {
                $parts[] = "ADD KEY `{$idx}` ({$cols})";
            }
        }
        if (!empty($parts)) {
            self::query("ALTER TABLE {$logs} " . implode(', ', $parts));
        }
        return true;
    }

    /**
     * Étape 6 : stade final, invalidation des caches du dashboard.
     */
    private static function step_finalize(&$state, $deadline) {
        update_option('almaweb_schema_stage', Almaweb_Database::STAGE_FINAL, true);
        update_option('almaweb_cache_version', Almaweb_Database::get_cache_version() + 1, false);
        update_option('almaweb_migration_done_at', time(), false);
        delete_transient('almaweb_upgrade_error');
        return true;
    }

    /* ---------------- utilitaires ---------------- */

    private static function columns($table) {
        global $wpdb;
        $rows = $wpdb->get_results("DESCRIBE {$table}");
        return $rows ? wp_list_pluck($rows, 'Field') : [];
    }

    private static function indexes($table) {
        global $wpdb;
        $rows = $wpdb->get_results("SHOW INDEX FROM {$table}", ARRAY_A);
        $names = [];
        foreach ((array) $rows as $row) {
            if (!empty($row['Key_name'])) {
                $names[$row['Key_name']] = true;
            }
        }
        return array_keys($names);
    }

    /**
     * Exécute une requête et lève une exception si MySQL renvoie une erreur.
     *
     * @return int Nombre de lignes affectées
     */
    private static function query($sql) {
        global $wpdb;
        $wpdb->last_error = '';
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- migration statements built from hardcoded identifiers only
        $result = $wpdb->query($sql);
        if ($result === false || !empty($wpdb->last_error)) {
            throw new Exception($wpdb->last_error ? $wpdb->last_error : 'unknown database error');
        }
        return (int) $result;
    }
}