<?php

namespace App\Services;

use App\Core\Database;

/**
 * ITSWEBER CMS – BackupService (Phase 6e Hardening)
 *
 * Handles site backup creation, backup validation & restore,
 * and CMS update package validation & application.
 *
 * Backup format:
 *   ZIP archive containing:
 *     manifest.json        – metadata (type, cms_version, created_at, …)
 *     database.sql         – full SQL dump of all iw_* tables
 *     uploads/             – copy of public/uploads/ directory tree
 *
 * Update package format:
 *   ZIP archive containing:
 *     update_manifest.json – metadata (type, target_version, min_version, …)
 *     files/               – files to copy into CMS root (mapped 1:1)
 *     migrations/          – optional .sql migration files (executed sorted alphabetically)
 *
 * Protected paths during update (never overwritten):
 *   public/uploads/
 *   storage/
 *   app/Config/database.php
 *   app/Config/app.php
 *
 * Maintenance mode:
 *   A file storage/.maintenance is written before restore/update and removed after.
 *   public/index.php checks this file and shows a maintenance page for non-admin requests.
 *
 * Path traversal protection:
 *   - Restore: realpath-based check after mkdir; entries with ".." or NUL rejected
 *   - Update: string-based pre-check + realpath-based post-mkdir check; entries with ".." or NUL rejected as hard errors
 *
 * Migration order:
 *   SQL files in migrations/ are collected, sorted alphabetically by basename, then executed in order.
 *   ZIP internal ordering is NOT relied upon.
 */
class BackupService
{
    // ── Format identifiers ────────────────────────────────────────────────────
    public const BACKUP_TYPE           = 'iw_backup';
    public const BACKUP_FORMAT_VERSION = '1';

    public const UPDATE_TYPE           = 'iw_update';
    public const UPDATE_FORMAT_VERSION = '1';

    /**
     * Maximum ZIP file size for restore/update uploads (app-side default).
     * 200 MB allows realistic backup restores on most hosting setups.
     * Operators may override via app config key 'backup_max_zip_mb' (integer, in MB).
     * Note: The effective limit is always min(app-limit, PHP upload_max_filesize, PHP post_max_size).
     */
    public const MAX_ZIP_SIZE_DEFAULT_MB = 200;

    // ── Paths that MUST NOT be overwritten by an update ───────────────────────
    // Entries are relative to CMS root, forward-slashes, no leading slash.
    // Both exact matches and prefix-matches (path starts with entry + '/') are blocked.
    private const PROTECTED_PATHS = [
        'public/uploads',
        'storage',
        'app/Config/database.php',
        'app/Config/app.php',
    ];

    // ── Required manifest fields ──────────────────────────────────────────────
    private const BACKUP_MANIFEST_REQUIRED = [
        'type', 'backup_format', 'cms_version', 'created_at', 'site_name',
    ];

    private const UPDATE_MANIFEST_REQUIRED = [
        'type', 'format_version', 'target_version',
    ];

    // =========================================================================
    // MAINTENANCE MODE
    // =========================================================================

    /**
     * Enable maintenance mode by writing storage/.maintenance.
     * Returns true on success, false if the file could not be written.
     */
    public static function enableMaintenanceMode(string $basePath): bool
    {
        $storageDir = $basePath . '/storage';
        if (!is_dir($storageDir)) {
            return false;
        }
        $flag = [
            'enabled_at' => date('c'),
            'reason'     => 'backup_restore_update',
        ];
        return file_put_contents(
            $storageDir . '/.maintenance',
            json_encode($flag, JSON_PRETTY_PRINT)
        ) !== false;
    }

    /**
     * Disable maintenance mode by removing storage/.maintenance.
     */
    public static function disableMaintenanceMode(string $basePath): void
    {
        $file = $basePath . '/storage/.maintenance';
        if (is_file($file)) {
            @unlink($file);
        }
    }

    /**
     * Check whether maintenance mode is currently active.
     */
    public static function isMaintenanceMode(string $basePath): bool
    {
        return is_file($basePath . '/storage/.maintenance');
    }

    // =========================================================================
    // BACKUP
    // =========================================================================

    /**
     * Create a full site backup ZIP.
     *
     * Returns the absolute path to the ZIP file inside storage/temp/.
     * Throws \RuntimeException on failure.
     *
     * @param  Database $db
     * @param  string   $basePath  Absolute CMS root path (no trailing slash)
     * @return string              Absolute path to the created ZIP
     */
    public static function createBackup(Database $db, string $basePath): string
    {
        if (!class_exists('ZipArchive')) {
            throw new \RuntimeException(
                'PHP extension "zip" is required for backup. Please enable it on your server.'
            );
        }

        $tempDir = $basePath . '/storage/temp';
        if (!is_dir($tempDir) && !mkdir($tempDir, 0755, true)) {
            throw new \RuntimeException('Cannot create temp directory: ' . $tempDir);
        }

        $filename = 'iw_backup_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.zip';
        $zipPath  = $tempDir . '/' . $filename;

        $zip = new \ZipArchive();
        if ($zip->open($zipPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
            throw new \RuntimeException('Cannot create backup ZIP at: ' . $zipPath);
        }

        // 1. Write manifest
        $manifest = [
            'type'          => self::BACKUP_TYPE,
            'backup_format' => self::BACKUP_FORMAT_VERSION,
            'cms_version'   => self::getCmsVersion($basePath),
            'created_at'    => date('c'),
            'site_name'     => self::getSiteName($db),
            'php_version'   => PHP_VERSION,
        ];
        $zip->addFromString('manifest.json', json_encode($manifest, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));

        // 2. Write SQL dump
        $sql = self::generateSqlDump($db);
        $zip->addFromString('database.sql', $sql);

        // 3. Add uploads directory
        $uploadsDir = $basePath . '/public/uploads';
        if (is_dir($uploadsDir)) {
            self::addDirectoryToZip($zip, $uploadsDir, $uploadsDir, 'uploads');
        }

        $zip->close();

        return $zipPath;
    }

    /**
     * Stream a backup ZIP file to the browser for download, then delete it.
     *
     * Call this AFTER creating the backup. Exits after sending.
     */
    public static function streamDownload(string $zipPath, string $downloadName): void
    {
        if (!is_file($zipPath)) {
            throw new \RuntimeException('Backup file not found: ' . $zipPath);
        }

        $size = filesize($zipPath);

        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . $downloadName . '"');
        header('Content-Length: ' . $size);
        header('Cache-Control: no-cache, must-revalidate');
        header('Pragma: no-cache');
        header('Expires: 0');

        readfile($zipPath);
        @unlink($zipPath);
        exit;
    }

    // =========================================================================
    // RESTORE
    // =========================================================================

    /**
     * Validate an uploaded backup ZIP.
     *
     * Checks:
     *  - ZIP is openable and not too large
     *  - manifest.json is present and valid JSON
     *  - All required manifest fields are present
     *  - type === BACKUP_TYPE
     *  - backup_format === BACKUP_FORMAT_VERSION (hard reject for unknown versions)
     *  - database.sql is present in the archive
     *
     * Returns an array:
     *   ['valid' => true,  'manifest' => array]
     *   ['valid' => false, 'error' => string]
     */
    public static function validateBackup(string $zipPath): array
    {
        if (!class_exists('ZipArchive')) {
            return ['valid' => false, 'error' => 'PHP extension "zip" is required.'];
        }

        if (!is_file($zipPath)) {
            return ['valid' => false, 'error' => 'Upload file not found.'];
        }

        $maxSize = self::getMaxZipSize();
        if (filesize($zipPath) > $maxSize) {
            return ['valid' => false, 'error' => sprintf(
                'Backup file exceeds the maximum allowed size of %d MB.',
                (int) ($maxSize / 1024 / 1024)
            )];
        }

        $zip = new \ZipArchive();
        if ($zip->open($zipPath) !== true) {
            return ['valid' => false, 'error' => 'Cannot open ZIP file. The file may be corrupt or is not a valid ZIP archive.'];
        }

        $manifestJson = $zip->getFromName('manifest.json');
        $hasSql       = ($zip->locateName('database.sql') !== false);
        $zip->close();

        // --- manifest.json presence ---
        if ($manifestJson === false) {
            return ['valid' => false, 'error' => 'Missing manifest.json – this is not a valid ITSWEBER CMS backup.'];
        }

        // --- manifest.json parseable ---
        $manifest = json_decode($manifestJson, true);
        if (!is_array($manifest)) {
            return ['valid' => false, 'error' => 'Corrupt manifest.json – cannot parse JSON.'];
        }

        // --- type check ---
        if (($manifest['type'] ?? '') !== self::BACKUP_TYPE) {
            return ['valid' => false, 'error' => sprintf(
                'Wrong package type "%s". This is not an ITSWEBER CMS backup (expected type: "%s").',
                $manifest['type'] ?? '(none)',
                self::BACKUP_TYPE
            )];
        }

        // --- format version check (hard reject on mismatch) ---
        $formatVersion = (string) ($manifest['backup_format'] ?? '');
        if ($formatVersion !== self::BACKUP_FORMAT_VERSION) {
            return ['valid' => false, 'error' => sprintf(
                'Unsupported backup format version "%s". Only version "%s" is accepted by this CMS installation.',
                $formatVersion !== '' ? $formatVersion : '(missing)',
                self::BACKUP_FORMAT_VERSION
            )];
        }

        // --- required manifest fields ---
        foreach (self::BACKUP_MANIFEST_REQUIRED as $field) {
            if (!isset($manifest[$field]) || $manifest[$field] === '') {
                return ['valid' => false, 'error' => 'Backup manifest is incomplete: missing required field "' . $field . '".'];
            }
        }

        // --- database.sql presence in archive ---
        if (!$hasSql) {
            return ['valid' => false, 'error' => 'database.sql is missing from the backup archive. The backup file may be incomplete or corrupt.'];
        }

        return ['valid' => true, 'manifest' => $manifest];
    }

    /**
     * Restore from a valid backup ZIP.
     *
     * Replaces the database (all iw_* tables) and uploads directory.
     * Enables maintenance mode for the duration of the restore.
     *
     * Upload file behaviour:
     *   - Entries from uploads/ in the ZIP are extracted to public/uploads/.
     *   - Files already present are overwritten.
     *   - New files are added.
     *   - Files NOT in the backup remain on disk (no mirror/deletion of existing files).
     *   - This is intentionally conservative to avoid accidental deletion on partial restores.
     *   - The behaviour is documented and consistent (not a silent assumption).
     *
     * Path traversal protection:
     *   - Entries with ".." sequences are rejected.
     *   - Entries with NUL bytes are rejected.
     *   - After directory creation, realpath() is verified to be inside public/uploads/.
     *
     * Throws \RuntimeException on failure.
     *
     * @param  string   $zipPath   Path to the uploaded/temp backup ZIP
     * @param  Database $db
     * @param  string   $basePath  Absolute CMS root path
     */
    public static function restore(string $zipPath, Database $db, string $basePath): void
    {
        if (!class_exists('ZipArchive')) {
            throw new \RuntimeException('PHP extension "zip" is required for restore.');
        }

        // Enable maintenance mode
        self::enableMaintenanceMode($basePath);

        try {
            $zip = new \ZipArchive();
            if ($zip->open($zipPath) !== true) {
                throw new \RuntimeException('Cannot open backup ZIP.');
            }

            // 1. Restore database
            $sql = $zip->getFromName('database.sql');
            if ($sql === false) {
                $zip->close();
                throw new \RuntimeException('database.sql missing from backup archive.');
            }

            // Detect pre-PageBuilder backup by checking if the SQL dump
            // references iw_page_sections. Backups from v3.1.x don't have
            // this table, so the current install's stale data would survive.
            $isPrePageBuilder = strpos($sql, 'iw_page_sections') === false;

            self::importSqlDump($db, $sql);

            // If the backup predates the Page Builder, drop leftover tables
            // so migrations can recreate them and migrate data from
            // iw_homepage_sections cleanly (migration 026).
            // Also remove their migration records so they actually re-run.
            if ($isPrePageBuilder) {
                $pdo = $db->pdo();
                $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
                $pdo->exec("DROP TABLE IF EXISTS iw_section_revisions");
                $pdo->exec("DROP TABLE IF EXISTS iw_page_sections");
                $pdo->exec("DROP TABLE IF EXISTS iw_section_types");
                $pdo->exec("DROP TABLE IF EXISTS iw_plugin_settings");
                $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");

                // Clear migration records for 022+ so they re-run.
                // All migrations are idempotent, so re-running is safe.
                try {
                    $pdo->exec("DELETE FROM iw_migrations WHERE migration LIKE '02%' OR migration LIKE '03%'");
                } catch (\PDOException $e) {
                    // iw_migrations might not exist – Migrator will create it
                }
            }

            // 2. Restore uploads directory
            //    Ensure the target directory exists before realpath()
            $uploadsTarget = $basePath . '/public/uploads';
            if (!is_dir($uploadsTarget)) {
                mkdir($uploadsTarget, 0755, true);
            }
            $uploadsBase = realpath($uploadsTarget);
            if ($uploadsBase === false) {
                $zip->close();
                throw new \RuntimeException('Cannot resolve uploads directory path: ' . $uploadsTarget);
            }

            $skipped = 0;

            for ($i = 0; $i < $zip->numFiles; $i++) {
                $name = $zip->getNameIndex($i);

                // Only process entries inside uploads/
                if (strpos($name, 'uploads/') !== 0) {
                    continue;
                }

                // Skip directory entries
                if (substr($name, -1) === '/') {
                    continue;
                }

                // Derive relative path after 'uploads/'
                $relative = substr($name, strlen('uploads/'));

                // Reject empty, traversal, or NUL-containing paths (hard security check)
                if ($relative === ''
                    || strpos($relative, '..') !== false
                    || strpos($relative, "\0") !== false
                ) {
                    $skipped++;
                    continue;
                }

                // Normalize path separators
                $relative = str_replace('\\', '/', $relative);

                // Build the target path and ensure its directory exists
                $targetPath = $uploadsBase . '/' . $relative;
                $dir        = dirname($targetPath);

                if (!is_dir($dir)) {
                    mkdir($dir, 0755, true);
                }

                // Security: after mkdir, verify real path is still inside uploadsBase
                $realDir = realpath($dir);
                if ($realDir === false
                    || strpos($realDir . '/', $uploadsBase . '/') !== 0
                ) {
                    // Path escaped the allowed target directory – reject silently
                    $skipped++;
                    continue;
                }

                // Write file
                $content = $zip->getFromIndex($i);
                if ($content !== false) {
                    file_put_contents($targetPath, $content);
                }
            }

            $zip->close();

            if ($skipped > 0) {
                // Non-fatal: log but don't abort. Skipped entries were rejected for safety.
                error_log('ITSWEBER Restore: ' . $skipped . ' ZIP entries skipped due to path validation.');
            }

            // 3. Run pending migrations after restore
            // Backups from older versions (e.g. 3.1.3) may lack newer tables.
            // Running migrations ensures the database schema is up-to-date.
            $migrationsPath = $basePath . '/database/migrations';
            if (is_dir($migrationsPath)) {
                $migrator = new \App\Database\Migrator($db, $migrationsPath);
                $migrator->runAll();
            }

        } finally {
            // Always disable maintenance mode, even on failure
            self::disableMaintenanceMode($basePath);
        }
    }

    // =========================================================================
    // UPDATE PACKAGE
    // =========================================================================

    /**
     * Validate a CMS update package ZIP.
     *
     * Checks:
     *  - ZIP is openable and not too large
     *  - update_manifest.json is present and valid JSON
     *  - All required manifest fields are present
     *  - type === UPDATE_TYPE
     *  - format_version === UPDATE_FORMAT_VERSION (hard reject)
     *  - target_version is set
     *  - min_version compatibility (if set)
     *  - Not a downgrade
     *
     * Returns:
     *   ['valid' => true,  'manifest' => array]
     *   ['valid' => false, 'error' => string]
     *
     * @param  string $zipPath        Path to the uploaded update ZIP
     * @param  string $currentVersion Current CMS version (e.g. "1.0.0")
     */
    public static function validateUpdate(string $zipPath, string $currentVersion): array
    {
        if (!class_exists('ZipArchive')) {
            return ['valid' => false, 'error' => 'PHP extension "zip" is required.'];
        }

        if (!is_file($zipPath)) {
            return ['valid' => false, 'error' => 'Upload file not found.'];
        }

        $maxSize = self::getMaxZipSize();
        if (filesize($zipPath) > $maxSize) {
            return ['valid' => false, 'error' => sprintf(
                'Update file exceeds the maximum allowed size of %d MB.',
                (int) ($maxSize / 1024 / 1024)
            )];
        }

        $zip = new \ZipArchive();
        if ($zip->open($zipPath) !== true) {
            return ['valid' => false, 'error' => 'Cannot open ZIP file. The file may be corrupt or is not a valid ZIP archive.'];
        }

        $manifestJson = $zip->getFromName('update_manifest.json');
        $zip->close();

        if ($manifestJson === false) {
            return ['valid' => false, 'error' => 'Missing update_manifest.json – this is not a valid ITSWEBER CMS update package.'];
        }

        $manifest = json_decode($manifestJson, true);
        if (!is_array($manifest)) {
            return ['valid' => false, 'error' => 'Corrupt update_manifest.json – cannot parse JSON.'];
        }

        // --- type check ---
        if (($manifest['type'] ?? '') !== self::UPDATE_TYPE) {
            return ['valid' => false, 'error' => sprintf(
                'Wrong package type "%s". This is not an ITSWEBER CMS update package (expected type: "%s").',
                $manifest['type'] ?? '(none)',
                self::UPDATE_TYPE
            )];
        }

        // --- format version check (hard reject on mismatch) ---
        $formatVersion = (string) ($manifest['format_version'] ?? '');
        if ($formatVersion !== self::UPDATE_FORMAT_VERSION) {
            return ['valid' => false, 'error' => sprintf(
                'Unsupported update package format version "%s". Only version "%s" is accepted by this CMS installation.',
                $formatVersion !== '' ? $formatVersion : '(missing)',
                self::UPDATE_FORMAT_VERSION
            )];
        }

        // --- required manifest fields ---
        foreach (self::UPDATE_MANIFEST_REQUIRED as $field) {
            if (!isset($manifest[$field]) || $manifest[$field] === '') {
                return ['valid' => false, 'error' => 'Update manifest is incomplete: missing required field "' . $field . '".'];
            }
        }

        // --- version: check minimum version compatibility ---
        if (!empty($manifest['min_version'])) {
            if (version_compare($currentVersion, $manifest['min_version'], '<')) {
                return [
                    'valid' => false,
                    'error' => sprintf(
                        'This update requires CMS version %s or higher. Your installed version is %s. Please update to an intermediate version first.',
                        $manifest['min_version'],
                        $currentVersion
                    ),
                ];
            }
        }

        // --- version: prevent downgrade ---
        if (version_compare($manifest['target_version'], $currentVersion, '<')) {
            return [
                'valid' => false,
                'error' => sprintf(
                    'The update target version (%s) is older than your installed version (%s). Downgrading is not supported.',
                    $manifest['target_version'],
                    $currentVersion
                ),
            ];
        }

        return ['valid' => true, 'manifest' => $manifest];
    }

    /**
     * Run a preflight check on a validated update package.
     *
     * Must be called AFTER validateUpdate() has returned valid=true.
     *
     * Checks:
     *  - ZIP can be opened
     *  - No path traversal sequences ("..", NUL) in package entries [hard error]
     *  - Protected paths in package [warning – they will be skipped]
     *  - Target directories in CMS root are writable [hard error per dir]
     *  - PHP zip extension available [hard error]
     *
     * Returns:
     * [
     *   'errors'   => string[]   // hard blockers – do NOT proceed if non-empty
     *   'warnings' => string[]   // skippable notices – user should be aware
     * ]
     *
     * @param  string $zipPath
     * @param  string $basePath
     * @return array{errors: string[], warnings: string[]}
     */
    public static function preflightUpdate(string $zipPath, string $basePath): array
    {
        $errors   = [];
        $warnings = [];

        // PHP extension check
        if (!class_exists('ZipArchive')) {
            return ['errors' => ['PHP extension "zip" is not available on this server.'], 'warnings' => []];
        }

        // ZIP openable
        $zip = new \ZipArchive();
        if ($zip->open($zipPath) !== true) {
            return ['errors' => ['Cannot open update ZIP for preflight inspection.'], 'warnings' => []];
        }

        $forbiddenEntries  = [];
        $protectedEntries  = [];

        for ($i = 0; $i < $zip->numFiles; $i++) {
            $name = $zip->getNameIndex($i);

            if (strpos($name, 'files/') !== 0) {
                continue;
            }

            $relative = substr($name, strlen('files/'));

            if ($relative === '' || substr($name, -1) === '/') {
                continue;
            }

            // Normalize
            $relative = str_replace('\\', '/', $relative);

            // Path traversal sequences: hard error
            if (strpos($relative, '..') !== false || strpos($relative, "\0") !== false) {
                $forbiddenEntries[] = $name;
                continue;
            }

            // Protected path: warning (will be skipped, not an error)
            if (self::isProtectedPath($relative)) {
                $protectedEntries[] = $relative;
            }
        }

        $zip->close();

        // Hard error: path traversal entries
        if (!empty($forbiddenEntries)) {
            $errors[] = 'Update package contains forbidden path entries (path traversal detected): '
                . implode(', ', array_slice($forbiddenEntries, 0, 5))
                . (count($forbiddenEntries) > 5 ? ' … (' . count($forbiddenEntries) . ' total)' : '');
        }

        // Warning: protected paths present (will be skipped transparently)
        if (!empty($protectedEntries)) {
            $warnings[] = 'The following protected paths are included in the update package and will be '
                . 'skipped automatically (your data is safe): '
                . implode(', ', $protectedEntries);
        }

        // Check writability of key CMS directories
        $checkDirs = ['app', 'admin', 'themes', 'public'];
        foreach ($checkDirs as $dir) {
            $path = $basePath . '/' . $dir;
            if (is_dir($path) && !is_writable($path)) {
                $errors[] = 'Directory is not writable (update cannot proceed): ' . $dir . '/';
            }
        }

        return ['errors' => $errors, 'warnings' => $warnings];
    }

    /**
     * Apply a validated CMS update package.
     *
     * Copies files from files/ inside the ZIP into the CMS root (excluding
     * protected paths and any path-traversal entries), then executes SQL
     * migration files in migrations/ in deterministic alphabetical order.
     *
     * Maintenance mode is enabled for the duration of the update.
     *
     * Path traversal protection:
     *  - Entries with ".." or NUL are logged as errors and skipped (not silently ignored).
     *  - After directory creation, realpath() is verified against basePath.
     *  - Protected paths are skipped with a logged notice (not silently).
     *
     * Migration order:
     *  - All *.sql files in migrations/ are collected first.
     *  - They are sorted alphabetically by basename (e.g. 015_... before 016_...).
     *  - ZIP internal ordering is NOT relied upon.
     *
     * Throws \RuntimeException on failure (with accumulated error messages).
     *
     * @param  string   $zipPath    Path to the update ZIP
     * @param  string   $basePath   Absolute CMS root
     * @param  Database $db
     */
    public static function applyUpdate(string $zipPath, string $basePath, Database $db): void
    {
        if (!class_exists('ZipArchive')) {
            throw new \RuntimeException('PHP extension "zip" is required for update.');
        }

        // Enable maintenance mode
        self::enableMaintenanceMode($basePath);

        try {
            $zip = new \ZipArchive();
            if ($zip->open($zipPath) !== true) {
                throw new \RuntimeException('Cannot open update ZIP.');
            }

            // Read target_version from the manifest so we can persist it after success.
            // Done before the file-copy loop to keep it close to the open() call.
            $updateTargetVersion = null;
            $manifestJsonRaw = $zip->getFromName('update_manifest.json');
            if ($manifestJsonRaw !== false) {
                $mf = json_decode($manifestJsonRaw, true);
                if (is_array($mf) && isset($mf['target_version']) && is_string($mf['target_version'])) {
                    $updateTargetVersion = $mf['target_version'];
                }
            }

            $errors            = [];
            $migrations        = [];   // [basename => sql_content]
            $skippedProtected  = [];
            $skippedForbidden  = [];

            // Resolve basePath realpath once
            $realBase = realpath($basePath);
            if ($realBase === false) {
                $zip->close();
                throw new \RuntimeException('Cannot resolve CMS base path: ' . $basePath);
            }

            for ($i = 0; $i < $zip->numFiles; $i++) {
                $name = $zip->getNameIndex($i);

                // ── Collect SQL migrations (do NOT execute yet) ──────────────
                if (strpos($name, 'migrations/') === 0 && substr($name, -4) === '.sql') {
                    $sql = $zip->getFromIndex($i);
                    if ($sql !== false && trim($sql) !== '') {
                        $migrations[basename($name)] = $sql;
                    }
                    continue;
                }

                // ── Process files/ entries ───────────────────────────────────
                if (strpos($name, 'files/') !== 0) {
                    continue;
                }

                $relative = substr($name, strlen('files/'));

                if ($relative === '' || substr($name, -1) === '/') {
                    continue; // skip directory entries
                }

                // Normalize path separators
                $relative = str_replace('\\', '/', $relative);

                // Hard check: reject path traversal entries (error, not silent skip)
                if (strpos($relative, '..') !== false || strpos($relative, "\0") !== false) {
                    $skippedForbidden[] = $name;
                    $errors[] = 'Rejected forbidden path entry (path traversal): ' . $name;
                    continue;
                }

                // Protected path: skip with notice (not silently)
                if (self::isProtectedPath($relative)) {
                    $skippedProtected[] = $relative;
                    continue;
                }

                $targetPath = $realBase . '/' . $relative;

                // Create target directory
                $dir = dirname($targetPath);
                if (!is_dir($dir) && !mkdir($dir, 0755, true)) {
                    $errors[] = 'Could not create directory: ' . dirname($relative) . '/';
                    continue;
                }

                // Security: after mkdir, verify real path is inside basePath
                $realDir = realpath($dir);
                if ($realDir === false || strpos($realDir . '/', $realBase . '/') !== 0) {
                    $errors[] = 'Rejected path outside CMS root (realpath check): ' . $relative;
                    continue;
                }

                $content = $zip->getFromIndex($i);
                if ($content === false) {
                    $errors[] = 'Could not read ZIP entry: ' . $name;
                    continue;
                }

                if (file_put_contents($targetPath, $content) === false) {
                    $errors[] = 'Could not write file: ' . $relative;
                }
            }

            $zip->close();

            // Log skipped protected paths (informational, not an error)
            if (!empty($skippedProtected)) {
                error_log('ITSWEBER Update: skipped protected paths (expected): ' . implode(', ', $skippedProtected));
            }

            // ── Execute SQL migrations in deterministic alphabetical order ───
            // Sort by basename (e.g. 015_add_feature.sql < 016_extend_table.sql)
            ksort($migrations, SORT_NATURAL);

            foreach ($migrations as $migBasename => $sql) {
                try {
                    self::executeSqlStatements($db, $sql);
                } catch (\Exception $e) {
                    $errors[] = 'Migration error in ' . $migBasename . ': ' . $e->getMessage();
                }
            }

            if (!empty($errors)) {
                throw new \RuntimeException(
                    'Update completed with errors:' . "\n" . implode("\n", $errors)
                );
            }

            // ── Run PHP-based migrations (022+) ────────────────────────────
            // Updates from pre-PageBuilder versions (e.g. 3.1.3) include new PHP
            // migration classes in database/migrations/. The SQL migration loop above
            // only handles .sql files from the ZIP's top-level migrations/ folder.
            // The PHP Migrator picks up any pending .php migrations from disk.
            $phpMigrationsPath = $realBase . '/database/migrations';
            if (is_dir($phpMigrationsPath)) {
                try {
                    $migrator = new \App\Database\Migrator($db, $phpMigrationsPath);
                    $migrator->runAll();
                } catch (\Exception $e) {
                    error_log('ITSWEBER Update: PHP migration error: ' . $e->getMessage());
                }
            }

            // ── Persist the new installed version ───────────────────────────
            // Write app/Config/version.php so getCmsVersion() returns the correct
            // value after this update. This file is NOT in PROTECTED_PATHS and is
            // the canonical source for the installed version (ADR-067).
            if ($updateTargetVersion !== null) {
                $versionFile    = $realBase . '/app/Config/version.php';
                $versionContent = "<?php\n\n"
                    . "/**\n"
                    . " * ITSWEBER CMS – Installed Version\n"
                    . " * Updated automatically by the CMS updater. Do not edit manually.\n"
                    . " */\n"
                    . "return '" . addslashes($updateTargetVersion) . "';\n";

                if (@file_put_contents($versionFile, $versionContent) === false) {
                    // Non-fatal: log the failure but do not abort – the update succeeded,
                    // only the version stamp could not be written (e.g. read-only filesystem).
                    error_log(
                        'ITSWEBER Update: could not write app/Config/version.php '
                        . '(version stamp not updated to ' . $updateTargetVersion . '). '
                        . 'Check file-write permissions for app/Config/.'
                    );
                }
            }

            // ── Clear OPcache after file deployment ──────────────────────────
            // Without this, PHP continues to serve pre-update bytecode even
            // though new files have been written to disk. opcache_reset() flushes
            // all cached scripts so the next request compiles the fresh files.
            if (function_exists('opcache_reset')) {
                opcache_reset();
            }

        } finally {
            // Always disable maintenance mode, even on failure
            self::disableMaintenanceMode($basePath);
        }
    }

    // =========================================================================
    // SQL DUMP & IMPORT
    // =========================================================================

    /**
     * Generate a SQL dump of all iw_* tables using PDO.
     * The output is UTF-8, uses INSERT statements, and is re-importable.
     */
    public static function generateSqlDump(Database $db): string
    {
        $pdo  = $db->pdo();
        $sql  = '';
        $sql .= "-- ITSWEBER CMS Database Backup\n";
        $sql .= "-- Generated: " . date('c') . "\n";
        $sql .= "-- Format: UTF-8, MySQL 5.7+ / MariaDB 10.3+\n\n";
        $sql .= "SET NAMES utf8mb4;\n";
        $sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";

        // Get all iw_* tables
        $tables = $pdo->query("SHOW TABLES")->fetchAll(\PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            // Only export iw_* tables
            if (strpos($table, 'iw_') !== 0) {
                continue;
            }

            // Table structure
            $createStmt = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(\PDO::FETCH_ASSOC);
            $createSql  = $createStmt['Create Table'] ?? $createStmt[array_key_last($createStmt)] ?? '';

            $sql .= "-- Table: {$table}\n";
            $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
            $sql .= $createSql . ";\n\n";

            // Table data
            $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(\PDO::FETCH_ASSOC);

            if (!empty($rows)) {
                // Get columns
                $columns = array_map(
                    static fn($col) => '`' . $col . '`',
                    array_keys($rows[0])
                );
                $colList = implode(', ', $columns);

                // Batch inserts (100 rows per statement)
                $batch = [];
                foreach ($rows as $row) {
                    $values = array_map(
                        static function ($val) use ($pdo) {
                            if ($val === null) {
                                return 'NULL';
                            }
                            return $pdo->quote((string) $val);
                        },
                        array_values($row)
                    );
                    $batch[] = '(' . implode(', ', $values) . ')';

                    if (count($batch) >= 100) {
                        $sql .= "INSERT INTO `{$table}` ({$colList}) VALUES\n"
                              . implode(",\n", $batch) . ";\n";
                        $batch = [];
                    }
                }

                if (!empty($batch)) {
                    $sql .= "INSERT INTO `{$table}` ({$colList}) VALUES\n"
                          . implode(",\n", $batch) . ";\n";
                }

                $sql .= "\n";
            }
        }

        $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";

        return $sql;
    }

    /**
     * Import a SQL dump string, executing each statement via PDO.
     */
    public static function importSqlDump(Database $db, string $sql): void
    {
        $db->pdo()->exec("SET NAMES utf8mb4");
        $db->pdo()->exec("SET FOREIGN_KEY_CHECKS = 0");

        self::executeSqlStatements($db, $sql);

        $db->pdo()->exec("SET FOREIGN_KEY_CHECKS = 1");
    }

    // =========================================================================
    // HELPERS
    // =========================================================================

    /**
     * Check whether a relative path (from CMS root) matches a protected path.
     *
     * Input is normalized to forward-slashes, leading slash stripped.
     * Both exact matches and sub-path matches (path starts with protected + '/') are blocked.
     */
    public static function isProtectedPath(string $relative): bool
    {
        $relative = ltrim(str_replace('\\', '/', $relative), '/');

        foreach (self::PROTECTED_PATHS as $protected) {
            if ($relative === $protected || strpos($relative, $protected . '/') === 0) {
                return true;
            }
        }

        return false;
    }

    /**
     * Return the list of paths protected from update overwrites.
     * Useful for display in the admin UI.
     *
     * @return string[]
     */
    public static function getProtectedPaths(): array
    {
        return self::PROTECTED_PATHS;
    }

    /**
     * Return the effective maximum ZIP size in bytes for restore/update uploads.
     *
     * The effective limit is the minimum of:
     *  1. App-side limit (config 'backup_max_zip_mb' or MAX_ZIP_SIZE_DEFAULT_MB)
     *  2. PHP upload_max_filesize
     *  3. PHP post_max_size
     *
     * This ensures the UI never promises more than the server can actually handle.
     */
    public static function getMaxZipSize(): int
    {
        $appLimit = self::getAppMaxZipSize();

        // Determine PHP limits
        $phpUpload = self::parsePhpSize(ini_get('upload_max_filesize') ?: '2M');
        $phpPost   = self::parsePhpSize(ini_get('post_max_size') ?: '8M');

        // post_max_size = 0 means unlimited in PHP
        $phpEffective = ($phpPost > 0) ? min($phpUpload, $phpPost) : $phpUpload;

        return min($appLimit, $phpEffective);
    }

    /**
     * Return the app-configured max ZIP size in bytes (ignoring PHP limits).
     */
    public static function getAppMaxZipSize(): int
    {
        if (defined('BASE_PATH')) {
            $configFile = BASE_PATH . '/app/Config/app.php';
            if (is_file($configFile)) {
                $config = @include $configFile;
                if (is_array($config) && isset($config['backup_max_zip_mb'])) {
                    $mb = (int) $config['backup_max_zip_mb'];
                    if ($mb > 0) {
                        return $mb * 1024 * 1024;
                    }
                }
            }
        }
        return self::MAX_ZIP_SIZE_DEFAULT_MB * 1024 * 1024;
    }

    /**
     * Parse a PHP ini shorthand size value (e.g. '128M', '2G', '50000K') into bytes.
     */
    private static function parsePhpSize(string $value): int
    {
        $value = trim($value);
        $num   = (int) $value;
        $unit  = strtolower(substr($value, -1));

        switch ($unit) {
            case 'g': return $num * 1024 * 1024 * 1024;
            case 'm': return $num * 1024 * 1024;
            case 'k': return $num * 1024;
            default:  return $num;
        }
    }

    /**
     * Recursively add a directory to a ZipArchive.
     *
     * @param \ZipArchive $zip
     * @param string      $dir        Absolute path of directory to add
     * @param string      $baseDir    Base path to strip when building ZIP entry names
     * @param string      $zipPrefix  Prefix to prepend to entry names inside the ZIP
     */
    private static function addDirectoryToZip(
        \ZipArchive $zip,
        string $dir,
        string $baseDir,
        string $zipPrefix
    ): void {
        $baseDir = rtrim($baseDir, '/');

        $iterator = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($dir, \FilesystemIterator::SKIP_DOTS),
            \RecursiveIteratorIterator::SELF_FIRST
        );

        foreach ($iterator as $file) {
            $realPath = $file->getRealPath();
            if ($realPath === false) {
                continue;
            }

            // Strip base dir to get relative portion
            $relative = substr($realPath, strlen($baseDir) + 1);
            $relative = str_replace('\\', '/', $relative);

            $entryName = $zipPrefix . '/' . $relative;

            if ($file->isDir()) {
                $zip->addEmptyDir($entryName);
            } else {
                $zip->addFile($realPath, $entryName);
            }
        }
    }

    /**
     * Execute a multi-statement SQL string via PDO.
     * Splits on semicolons outside string literals (simplified).
     */
    private static function executeSqlStatements(Database $db, string $sql): void
    {
        // Remove comment lines
        $sql = preg_replace('/^--.*$/m', '', $sql);
        $sql = preg_replace('/^\/\*.*?\*\//ms', '', $sql);

        // Split on semicolons at the end of a line
        $statements = preg_split('/;\s*\n/u', $sql);

        foreach ($statements as $stmt) {
            $stmt = trim($stmt);
            if ($stmt === '' || strtoupper($stmt) === 'SET NAMES utf8mb4'
                             || strtoupper($stmt) === 'SET FOREIGN_KEY_CHECKS = 0'
                             || strtoupper($stmt) === 'SET FOREIGN_KEY_CHECKS = 1') {
                continue;
            }

            try {
                $db->pdo()->exec($stmt);
            } catch (\PDOException $e) {
                $msg = $e->getMessage();
                // Tolerate idempotent DDL errors:
                // 1051 = table doesn't exist (DROP TABLE IF EXISTS)
                // 1060 = duplicate column name (ADD COLUMN if already exists)
                // 1061 = duplicate key name (ADD INDEX if already exists)
                if (strpos($msg, '1051') !== false
                    || strpos($msg, '1060') !== false
                    || strpos($msg, '1061') !== false) {
                    continue;
                }
                throw $e;
            }
        }
    }

    /**
     * Return the installed CMS version.
     *
     * Reads from app/Config/version.php first (canonical, writable by the updater)
     * and falls back to app/Config/app.php for installations without a version.php
     * (i.e. fresh installs that have never had an update applied).
     *
     * app/Config/app.php is in PROTECTED_PATHS and cannot be overwritten by update
     * packages, so it may be stale (still showing the initial version) on updated
     * installations. app/Config/version.php is not protected and is written by
     * applyUpdate() after each successful update.
     */
    private static function getCmsVersion(string $basePath): string
    {
        // Canonical source: app/Config/version.php (not in PROTECTED_PATHS)
        $versionFile = $basePath . '/app/Config/version.php';
        if (is_file($versionFile)) {
            // phpcs:ignore
            $v = include $versionFile;
            if (is_string($v) && $v !== '') {
                return $v;
            }
        }

        // Fallback: app/Config/app.php (protected – may be stale)
        $configFile = $basePath . '/app/Config/app.php';
        if (is_file($configFile)) {
            $config = include $configFile;
            if (is_array($config) && isset($config['version'])) {
                return $config['version'];
            }
        }

        return '1.0.0';
    }

    /**
     * Get the site name from the database settings.
     */
    private static function getSiteName(Database $db): string
    {
        try {
            $row = $db->fetch(
                "SELECT setting_value FROM iw_settings WHERE setting_key = 'site_name' LIMIT 1"
            );
            return $row['setting_value'] ?? 'ITSWEBER CMS';
        } catch (\Exception $e) {
            return 'ITSWEBER CMS';
        }
    }
}
