<?php

declare(strict_types=1);

namespace Sentinelo\Core;

/**
 * Lightweight, idempotent schema upgrades applied on normal boot.
 *
 * WordPress does NOT fire the activation hook when a plugin is updated by
 * uploading a new zip over the existing one (common on shared hosting), so we
 * cannot rely on the activator to add new columns. This runs the missing
 * migrations once per version bump, gated by an option to stay cheap.
 */
class Migrator
{
    public static function maybeUpgrade(): void
    {
        $current = (string) (defined('SENTINELO_VERSION') ? SENTINELO_VERSION : '0');
        $stored  = (string) get_option('SENTINELO_db_version', '');

        if ($stored === $current) {
            return;
        }

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

        // 1.0.1: per-step timeline storage on the backups row.
        $hasSteps = $wpdb->get_var(
            $wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'steps_json')
        );
        if (!$hasSteps) {
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN steps_json LONGTEXT NULL", $table));
            Logger::info('migration', 'Added steps_json column to backups table');
        }

        // 1.4.0 : complete ou differentielle, et sur quelle base.
        //
        // Deux colonnes, parce que le backend apprend l'issue d'une sauvegarde par
        // DEUX routes — le webhook et le sondage — et que les deux doivent porter
        // la meme information. Le webhook pourrait se contenter de ce que le
        // processus a en main ; le sondage, lui, relit cette ligne. Sans colonne,
        // la moitie des sauvegardes arriveraient sans leur mode, et une
        // restauration de differentiel se ferait sans sa base.
        $hasMode = $wpdb->get_var(
            $wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $table, 'mode')
        );
        if (!$hasMode) {
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN mode VARCHAR(16) NOT NULL DEFAULT 'full'", $table));
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN base_backup_id BIGINT UNSIGNED NULL", $table));
            Logger::info('migration', 'Added mode / base_backup_id columns to backups table');
        }

        // 1.0.3: allow the 'safety' backup type (taken before a restore).
        $wpdb->query($wpdb->prepare("ALTER TABLE %i MODIFY backup_type ENUM('daily','weekly','monthly','yearly','manual','safety') NOT NULL", $table));

        // 1.0.10: widen retention_tag — a 1st-of-January backup that also falls on a
        // Sunday is tagged "daily,weekly,monthly,yearly" (27 chars), which VARCHAR(20)
        // silently truncated, dropping the "yearly" tag and breaking the GFS guarantee
        // (the annual archive was then mis-counted and pruned). Idempotent MODIFY.
        $wpdb->query($wpdb->prepare("ALTER TABLE %i MODIFY retention_tag VARCHAR(64) NULL", $table));

        // 1.1.0: why a scan failed. Without this column the plugin had nowhere to
        // put the cause, so /security/report could not emit `error_message` and every
        // agent-side failure reached the operator as a generic "Scan échoué côté
        // plugin." — the backend has always read the field.
        $scans = $wpdb->prefix . 'SENTINELO_security_scans';
        $hasScanError = $wpdb->get_var(
            $wpdb->prepare("SHOW COLUMNS FROM %i LIKE %s", $scans, 'error_message')
        );
        if (!$hasScanError) {
            $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN error_message TEXT NULL", $scans));
            Logger::info('migration', 'Added error_message column to security_scans table');
        }

        // 1.1.0: purge des lignes de scan restées « running » sans jamais pouvoir
        // aboutir. La route /security/scan (supprimée) insérait une ligne puis
        // déléguait à wp-cron, où SecurityScanner ouvrait SA PROPRE ligne : celle de
        // la route n'était donc jamais complétée. Un scan interrompu par un fatal ou
        // un dépassement de temps laisse la même trace.
        //
        // Ces lignes sont vides par construction (aucun résultat n'y a jamais été
        // écrit) et personne ne les lit : findLatestTerminal() ne regarde que
        // 'completed'/'failed', HealthEndpoint que 'completed'. On les supprime plutôt
        // que de les passer à 'failed', justement pour ne pas rendre visible sur le
        // chemin du rapport une ligne qui n'a jamais rien porté.
        //
        // Le prédicat est borné pour ne pas toucher un scan réellement en cours : le
        // backend force l'échec vers 35 minutes et PHP ne tient pas une journée, donc
        // 7 jours laisse une marge très large. started_at est stocké en UTC
        // (Timestamps::store), d'où la comparaison à UTC_TIMESTAMP().
        $orphans = (int) $wpdb->query($wpdb->prepare(
            "DELETE FROM %i
             WHERE status = 'running'
               AND completed_at IS NULL
               AND score IS NULL
               AND details_json IS NULL
               AND started_at IS NOT NULL
               AND started_at < (UTC_TIMESTAMP() - INTERVAL 7 DAY)",
            $scans
        ));
        if ($orphans > 0) {
            Logger::info('migration', "Purged {$orphans} stale 'running' security scan rows");
        }

        // 1.0.3: restores (rollback) tracking table.
        $restores = $wpdb->prefix . 'SENTINELO_restores';
        $hasRestores = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $restores)) === $restores;
        if (!$hasRestores) {
            // dbDelta(), the core API for creating tables: the charset clause cannot
            // be a prepare() placeholder, and dbDelta needs its KEY / two-space syntax.
            $charset = $wpdb->get_charset_collate();
            self::requireDbDelta();
            dbDelta("CREATE TABLE {$restores} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                source_cloud_path VARCHAR(500) NOT NULL,
                scope ENUM('db','files','all') NOT NULL DEFAULT 'all',
                status ENUM('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
                safety_backup_id BIGINT UNSIGNED NULL,
                started_at DATETIME NULL,
                completed_at DATETIME NULL,
                error_message TEXT NULL,
                steps_json LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_status (status),
                KEY idx_created (created_at)
            ) {$charset};");
            Logger::info('migration', 'Created SENTINELO_restores table');
        }

        // 1.1.0: SEO audit results. Created here and NOT in the activator, because
        // WordPress does not fire the activation hook when a plugin is updated by
        // uploading a new zip over the old one — the common case on shared hosting.
        $audits = $wpdb->prefix . 'SENTINELO_audits';
        $hasAudits = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $audits)) === $audits;
        if (!$hasAudits) {
            $charset = $wpdb->get_charset_collate();
            self::requireDbDelta();
            dbDelta("CREATE TABLE {$audits} (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                audit_type ENUM('full','quick') NOT NULL DEFAULT 'full',
                status ENUM('pending','running','completed','failed') NOT NULL DEFAULT 'pending',
                score SMALLINT NULL,
                checks_passed SMALLINT NOT NULL DEFAULT 0,
                checks_warned SMALLINT NOT NULL DEFAULT 0,
                checks_failed SMALLINT NOT NULL DEFAULT 0,
                checks_skipped SMALLINT NOT NULL DEFAULT 0,
                critical_issues SMALLINT NOT NULL DEFAULT 0,
                high_issues SMALLINT NOT NULL DEFAULT 0,
                medium_issues SMALLINT NOT NULL DEFAULT 0,
                low_issues SMALLINT NOT NULL DEFAULT 0,
                pages_analyzed SMALLINT NOT NULL DEFAULT 0,
                checks_json LONGTEXT NULL,
                error_message TEXT NULL,
                webhook_sent TINYINT(1) NOT NULL DEFAULT 0,
                started_at DATETIME NULL,
                completed_at DATETIME NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_status (status),
                KEY idx_completed (completed_at)
            ) {$charset};");
            Logger::info('migration', 'Created SENTINELO_audits table');
        }

        // 1.5.7 : une reprise tardive marquait `failed` des sauvegardes réussies, que la
        // rétention ne voyait alors plus — leurs archives restaient chez Backblaze pour
        // toujours. On les lui rend ; la purge suivante applique la règle.
        $revived = (new \Sentinelo\Adapter\Wp\WpBackupRepository())->reviveCompletedMarkedFailed();
        if ($revived > 0) {
            Logger::info('migration', "Restored 'completed' on {$revived} successful backup(s) a late resume pass had marked failed");
        }

        update_option('SENTINELO_db_version', $current);
    }

    private static function requireDbDelta(): void
    {
        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
    }
}