<?php

declare(strict_types=1);

namespace PpPhp\Core\Databases;

use PpPhp\Core\DatabaseConnection;

/**
 * VirtualDb — the built-in "Virtual Database Engine" primitives (SQLite-backed).
 *
 * Every virtual database is a single SQLite file. This helper centralises the
 * file lifecycle (create / stats / backup / restore / vacuum / integrity / drop),
 * safe naming, and — critically — keeping the .sqlite files OUT of the web root
 * by writing a deny-all .htaccess into any directory that holds one.
 *
 * Two homes for virtual databases:
 *   • per-project  → <project>/database/app.sqlite  (portable, ships with a build)
 *   • central pool → BASE_PATH/storage/vdb/<name>.sqlite  (owner-managed, shared)
 */
final class VirtualDb
{
    /** Magic header every SQLite 3 file starts with. */
    private const MAGIC = "SQLite format 3\0";

    public static function basePath(): string
    {
        return defined('BASE_PATH') ? rtrim((string) constant('BASE_PATH'), '/\\') : dirname(__DIR__, 2);
    }

    /** Root directory for the central (owner-managed) virtual database pool. */
    public static function poolDir(): string
    {
        $dir = self::basePath() . '/storage/vdb';
        self::protectDir($dir);
        return $dir;
    }

    /** Absolute path of a central-pool database file, by its (already-safe) name. */
    public static function poolPath(string $name): string
    {
        return self::poolDir() . '/' . self::safeName($name) . '.sqlite';
    }

    /**
     * Make a directory exist and block all web access to it (SQLite files must
     * never be downloadable). Idempotent + best-effort.
     */
    public static function protectDir(string $dir): void
    {
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        $ht = $dir . '/.htaccess';
        if (!is_file($ht)) {
            @file_put_contents($ht, "# PPPHP Virtual Database — deny all web access\n"
                . "<IfModule mod_authz_core.c>\n  Require all denied\n</IfModule>\n"
                . "<IfModule !mod_authz_core.c>\n  Order allow,deny\n  Deny from all\n</IfModule>\n");
        }
        // IIS hosts ignore .htaccess — deny there too.
        $wc = $dir . '/web.config';
        if (!is_file($wc)) {
            @file_put_contents($wc, "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
                . "<configuration>\n  <system.webServer>\n    <security>\n"
                . "      <authorization>\n        <deny users=\"*\" />\n      </authorization>\n"
                . "    </security>\n  </system.webServer>\n</configuration>\n");
        }
        $idx = $dir . '/index.html';
        if (!is_file($idx)) { @file_put_contents($idx, ''); }
    }

    /** Turn a human label into a safe, unique-ish file/db name. */
    public static function safeName(string $label): string
    {
        $slug = strtolower(trim($label));
        $slug = preg_replace('/[^a-z0-9]+/', '_', $slug) ?? '';
        $slug = trim($slug, '_');
        if ($slug === '') { $slug = 'db'; }
        return substr($slug, 0, 40);
    }

    /** Generate a collision-resistant central-pool database name. */
    public static function newName(string $label = ''): string
    {
        $tag = self::safeName($label !== '' ? $label : 'vdb');
        return 'vdb_' . $tag . '_' . substr(bin2hex(random_bytes(4)), 0, 8);
    }

    /**
     * A user's database-name PREFIX — the shared-hosting convention (cPanel /
     * DirectAdmin / Plesk): every database a user owns begins with their account
     * handle + an underscore, so ownership is visible at a glance and names never
     * collide across users. Lower-cased, letters+digits only, capped at 24 chars.
     */
    public static function userPrefix(string $username): string
    {
        $p = strtolower(trim($username));
        $p = preg_replace('/[^a-z0-9]+/', '', $p) ?? '';
        if ($p === '') { $p = 'user'; }
        return substr($p, 0, 24);
    }

    /**
     * Compose a full pool-DB name `<prefix>_<dbpart>`, mirroring cPanel. The db part
     * is slugified; a prefix the user re-typed is stripped so it is never doubled.
     * The whole thing is capped at 60 chars (stays a valid identifier if the DB is
     * later exported to MySQL/Postgres, whose limit is 64).
     */
    public static function composeName(string $prefix, string $dbPart): string
    {
        $prefix = self::userPrefix($prefix);
        $part = strtolower(trim($dbPart));
        $part = preg_replace('/[^a-z0-9]+/', '_', $part) ?? '';
        $part = trim($part, '_');
        // Don't double the prefix if the user typed "ppradhan_cms".
        if ($part !== '' && str_starts_with($part, $prefix . '_')) {
            $part = substr($part, strlen($prefix) + 1);
        }
        if ($part === '') { $part = 'db'; }
        $part = substr($part, 0, 60 - strlen($prefix) - 1);
        $part = trim($part, '_');
        if ($part === '') { $part = 'db'; }
        return $prefix . '_' . $part;
    }

    /** Create an (empty, initialised) SQLite file at an absolute path. */
    public static function createFile(string $absPath): void
    {
        self::protectDir(dirname($absPath));
        $pdo = new \PDO('sqlite:' . $absPath);
        DatabaseConnection::applySqlitePragmas($pdo);
        // A no-op that forces the file + WAL to materialise on disk.
        $pdo->exec('PRAGMA user_version');
        $pdo = null;
    }

    /** Is this a real SQLite database file? (validates the magic header) */
    public static function isSqliteFile(string $path): bool
    {
        if (!is_file($path) || filesize($path) === 0) { return false; }
        $fh = @fopen($path, 'rb');
        if (!$fh) { return false; }
        $head = fread($fh, 16);
        fclose($fh);
        return $head === self::MAGIC;
    }

    /** Total on-disk footprint (main db + -wal + -shm sidecars), in bytes. */
    public static function fileSize(string $absPath): int
    {
        $bytes = 0;
        foreach (['', '-wal', '-shm'] as $suffix) {
            $p = $absPath . $suffix;
            if (is_file($p)) { $bytes += (int) filesize($p); }
        }
        return $bytes;
    }

    /** Lightweight stats for the console list: size, table count, ok flag. */
    public static function stats(string $absPath): array
    {
        $out = ['exists' => is_file($absPath), 'bytes' => self::fileSize($absPath), 'tables' => 0, 'ok' => false, 'error' => ''];
        if (!$out['exists']) { return $out; }
        try {
            $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            $out['tables'] = (int) $pdo->query("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")->fetchColumn();
            $out['ok'] = ((string) $pdo->query('PRAGMA quick_check')->fetchColumn()) === 'ok';
            $pdo = null;
        } catch (\Throwable $e) {
            $out['error'] = $e->getMessage();
        }
        return $out;
    }

    /** Full integrity check (slower) — returns "ok" or the first problem(s). */
    public static function integrityCheck(string $absPath): string
    {
        try {
            $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            $rows = $pdo->query('PRAGMA integrity_check')->fetchAll(\PDO::FETCH_COLUMN);
            return implode('; ', $rows ?: ['ok']);
        } catch (\Throwable $e) {
            return 'error: ' . $e->getMessage();
        }
    }

    /** Reclaim space + defragment. */
    public static function vacuum(string $absPath): void
    {
        $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
        $pdo->exec('VACUUM');
        $pdo = null;
    }

    /**
     * Consistent snapshot into a fresh file (safe even while the DB is in use,
     * unlike copying the file directly). Returns the destination path.
     */
    public static function backupTo(string $absPath, string $destPath): string
    {
        self::protectDir(dirname($destPath));
        if (is_file($destPath)) { @unlink($destPath); }
        $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
        $pdo->exec("VACUUM INTO " . $pdo->quote($destPath));
        $pdo = null;
        return $destPath;
    }

    /**
     * Delete a virtual database and its WAL/SHM sidecars. When $secure is set the
     * file bytes are overwritten with random data before unlinking, so the data
     * cannot be recovered from the freed disk blocks (best-effort "shred").
     */
    public static function deleteFile(string $absPath, bool $secure = false): void
    {
        foreach (['', '-wal', '-shm', '-journal'] as $suffix) {
            $p = $absPath . $suffix;
            if (!is_file($p)) { continue; }
            if ($secure) { self::shred($p); }
            @unlink($p);
        }
    }

    /** Overwrite a file's bytes with random data (best-effort secure-erase). */
    private static function shred(string $path): void
    {
        $size = @filesize($path);
        if ($size === false || $size <= 0) { return; }
        $fh = @fopen($path, 'r+b');
        if (!$fh) { return; }
        try {
            $written = 0;
            $chunk = 1 << 16; // 64 KB
            while ($written < $size) {
                $len = (int) min($chunk, $size - $written);
                @fwrite($fh, random_bytes($len));
                $written += $len;
            }
            @fflush($fh);
        } catch (\Throwable $e) { /* best effort */ }
        finally { @fclose($fh); }
    }

    /**
     * Fold the WAL back into the main database (TRUNCATE mode) so the -wal file
     * cannot grow without bound. Safe to call routinely; a no-op on a fresh DB.
     */
    public static function checkpoint(string $absPath): void
    {
        try {
            $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            $pdo->exec('PRAGMA wal_checkpoint(TRUNCATE)');
            $pdo = null;
        } catch (\Throwable $e) { /* best effort */ }
    }

    /**
     * Refresh the query planner's statistics (PRAGMA optimize). Cheap; keeps
     * queries choosing good indexes as the data grows.
     */
    public static function optimize(string $absPath): void
    {
        try {
            $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            DatabaseConnection::applySqlitePragmas($pdo);
            $pdo->exec('PRAGMA optimize');
            $pdo->exec('PRAGMA wal_checkpoint(TRUNCATE)');
            $pdo = null;
        } catch (\Throwable $e) { /* best effort */ }
    }

    /**
     * One-shot health/maintenance pass: quick integrity probe, planner refresh,
     * and WAL checkpoint. Returns a compact report for logging/console display.
     */
    public static function maintain(string $absPath): array
    {
        $before = self::fileSize($absPath);
        $out = ['ok' => false, 'bytes_before' => $before, 'bytes_after' => $before, 'error' => ''];
        try {
            $pdo = new \PDO('sqlite:' . $absPath, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            DatabaseConnection::applySqlitePragmas($pdo);
            $out['ok'] = ((string) $pdo->query('PRAGMA quick_check')->fetchColumn()) === 'ok';
            $pdo->exec('PRAGMA optimize');
            $pdo->exec('PRAGMA wal_checkpoint(TRUNCATE)');
            $pdo = null;
        } catch (\Throwable $e) {
            $out['error'] = $e->getMessage();
        }
        $out['bytes_after'] = self::fileSize($absPath);
        return $out;
    }

    /**
     * Consistent, hot snapshot of a database into a rotating snapshot directory,
     * keeping only the newest $keep files. Returns the new snapshot's path.
     * The snapshot is a plain .sqlite (VACUUM INTO) unless $encryptKey is given,
     * in which case it is AES-256-GCM encrypted at rest (.sqlite.enc).
     */
    public static function snapshot(string $absPath, string $snapDir, int $keep = 5, ?string $encryptKey = null): string
    {
        self::protectDir($snapDir);
        // Sub-second precision in the NAME makes retention ordering deterministic
        // even for snapshots taken in the same second (names sort chronologically).
        $us    = sprintf('%06d', (int) ((microtime(true) - floor(microtime(true))) * 1_000_000));
        $stamp = date('Ymd-His') . '-' . $us;
        $base  = $snapDir . '/snap-' . $stamp . '-' . substr(bin2hex(random_bytes(3)), 0, 6) . '.sqlite';

        // VACUUM INTO always writes a fresh, defragmented, consistent copy.
        $tmp = $base;
        if ($encryptKey !== null) { $tmp = $base . '.plain'; }
        self::backupTo($absPath, $tmp);

        $final = $base;
        if ($encryptKey !== null) {
            $final = $base . '.enc';
            self::encryptFile($tmp, $final, $encryptKey);
            self::shred($tmp);
            @unlink($tmp);
        }

        self::pruneSnapshots($snapDir, $keep);
        return $final;
    }

    /** Newest-first list of snapshots in a snapshot directory. */
    public static function listSnapshots(string $snapDir): array
    {
        if (!is_dir($snapDir)) { return []; }
        $out = [];
        foreach (glob($snapDir . '/snap-*.sqlite*') ?: [] as $p) {
            if (str_ends_with($p, '.plain')) { continue; }
            $out[] = [
                'path'      => $p,
                'name'      => basename($p),
                'bytes'     => (int) @filesize($p),
                'mtime'     => (int) @filemtime($p),
                'encrypted' => str_ends_with($p, '.enc'),
            ];
        }
        // Order by the timestamped filename (chronological + unique), newest first —
        // deterministic even when several snapshots share the same wall-clock second.
        usort($out, static fn($a, $b) => strcmp($b['name'], $a['name']));
        return $out;
    }

    /** Keep only the newest $keep snapshots; delete the rest (sidecars too). */
    public static function pruneSnapshots(string $snapDir, int $keep): void
    {
        if ($keep < 1) { $keep = 1; }
        $snaps = self::listSnapshots($snapDir);
        foreach (array_slice($snaps, $keep) as $old) {
            @unlink($old['path']);
        }
    }

    /**
     * Fork a database into a brand-new file (structure + data), via VACUUM INTO —
     * the copy is consistent even if the source is being written to. Returns the
     * destination path.
     */
    public static function cloneTo(string $srcPath, string $destPath): string
    {
        if (is_file($destPath)) {
            throw new \RuntimeException('A database with that name already exists.');
        }
        return self::backupTo($srcPath, $destPath);
    }

    /** AES-256-GCM encrypt a file (key is any string; a 32-byte key is derived). */
    private static function encryptFile(string $src, string $dest, string $key): void
    {
        $plain = (string) file_get_contents($src);
        $k   = hash('sha256', $key, true);
        $iv  = random_bytes(12);
        $tag = '';
        $ct  = openssl_encrypt($plain, 'aes-256-gcm', $k, OPENSSL_RAW_DATA, $iv, $tag);
        if ($ct === false) { throw new \RuntimeException('Snapshot encryption failed.'); }
        // Layout: magic(8) | iv(12) | tag(16) | ciphertext
        file_put_contents($dest, "PPVDBENC" . $iv . $tag . $ct);
    }

    /** Decrypt a .enc snapshot produced by encryptFile() back to $dest. */
    public static function decryptFile(string $src, string $dest, string $key): void
    {
        $blob = (string) file_get_contents($src);
        if (substr($blob, 0, 8) !== 'PPVDBENC') { throw new \RuntimeException('Not an encrypted PPPHP snapshot.'); }
        $iv  = substr($blob, 8, 12);
        $tag = substr($blob, 20, 16);
        $ct  = substr($blob, 36);
        $k   = hash('sha256', $key, true);
        $plain = openssl_decrypt($ct, 'aes-256-gcm', $k, OPENSSL_RAW_DATA, $iv, $tag);
        if ($plain === false) { throw new \RuntimeException('Snapshot decryption failed (wrong key or corrupt file).'); }
        file_put_contents($dest, $plain);
    }

    /** Human-readable byte size. */
    public static function humanSize(int $bytes): string
    {
        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
        $i = 0;
        $n = (float) $bytes;
        while ($n >= 1024 && $i < count($units) - 1) { $n /= 1024; $i++; }
        return ($i === 0 ? (string) $bytes : number_format($n, 1)) . ' ' . $units[$i];
    }
}
