<?php

declare(strict_types=1);

namespace PpPhp\Core\Databases\Drivers;

/**
 * Direct-SQL driver — the original behaviour, unchanged.
 *
 * Connects with a privileged MySQL login and runs CREATE DATABASE / CREATE USER
 * / GRANT itself. Correct for a VPS, a local server, or any host where we hold a
 * genuinely privileged account and can reach port 3306.
 *
 * It is deliberately still the DEFAULT: every server row that predates the panel
 * drivers resolves to this, so adding cPanel and DirectAdmin cannot change how
 * an existing server behaves.
 */
final class SqlDriver implements ProvisionDriver
{
    public function __construct(
        private string $host,
        private int $port,
        private string $user,
        private string $pass,
        private string $grantHost = '%',
    ) {}

    public function id(): string { return 'sql'; }

    public function connectionTarget(): array
    {
        return ['host' => $this->host, 'port' => $this->port ?: 3306];
    }

    public function prefix(): string { return ''; }      // raw SQL imposes none

    public function test(): array
    {
        try {
            $pdo = $this->pdo();
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendly($e),
                    'canCreateDb' => false, 'canCreateUser' => false,
                    'prefix' => '', 'maxNameLength' => 64];
        }

        $canUser = false;
        try {
            foreach ($pdo->query('SHOW GRANTS')->fetchAll(\PDO::FETCH_COLUMN) as $g) {
                if (stripos((string) $g, 'CREATE USER') !== false || stripos((string) $g, 'ALL PRIVILEGES ON *.*') !== false) {
                    $canUser = true; break;
                }
            }
        } catch (\Throwable $e) { /* SHOW GRANTS unavailable — assume limited */ }

        // A live probe is the only trustworthy answer on CREATE DATABASE.
        $canDb = false;
        $probe = 'pp_probe_' . bin2hex(random_bytes(4));
        try {
            $pdo->exec("CREATE DATABASE IF NOT EXISTS `{$probe}`");
            $canDb = true;
            try { $pdo->exec("DROP DATABASE `{$probe}`"); } catch (\Throwable $e) {}
        } catch (\Throwable $e) { $canDb = false; }

        return [
            'success' => true,
            'message' => $canDb
                ? ($canUser ? 'Connected — can create databases and dedicated logins.'
                            : 'Connected — can create databases, but not dedicated logins; databases will share this login.')
                : 'Connected, but this account cannot create databases. On shared hosting use the cPanel or DirectAdmin driver instead.',
            'canCreateDb' => $canDb, 'canCreateUser' => $canUser,
            'prefix' => '', 'maxNameLength' => 64,
        ];
    }

    public function createDatabase(string $desiredDb, string $desiredUser, string $password): array
    {
        try {
            $pdo = $this->pdo();
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendly($e)];
        }

        $db = preg_replace('/[^A-Za-z0-9_]/', '', $desiredDb) ?? $desiredDb;
        try {
            $pdo->exec("CREATE DATABASE IF NOT EXISTS `{$db}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendly($e)];
        }

        // A dedicated login is preferred but not essential — falling back to the
        // admin login keeps the database usable rather than failing outright.
        $user = preg_replace('/[^A-Za-z0-9_]/', '', $desiredUser) ?? $desiredUser;
        $dedicated = true;
        try {
            $u = $pdo->quote($user) . '@' . $pdo->quote($this->grantHost !== '' ? $this->grantHost : '%');
            $pdo->exec("CREATE USER {$u} IDENTIFIED BY " . $pdo->quote($password));
            $pdo->exec("GRANT ALL PRIVILEGES ON `{$db}`.* TO {$u}");
            $pdo->exec('FLUSH PRIVILEGES');
        } catch (\Throwable $e) {
            $dedicated = false;
            $user = $this->user;
        }

        return ['success' => true, 'message' => 'Database created.',
                'dbName' => $db, 'dbUser' => $user, 'dedicated' => $dedicated];
    }

    public function dropDatabase(string $dbName, string $dbUser = ''): array
    {
        try {
            $pdo = $this->pdo();
            $db = preg_replace('/[^A-Za-z0-9_]/', '', $dbName) ?? $dbName;
            $pdo->exec("DROP DATABASE IF EXISTS `{$db}`");
            if ($dbUser !== '' && $dbUser !== $this->user) {
                try {
                    $pdo->exec('DROP USER ' . $pdo->quote($dbUser) . '@' . $pdo->quote($this->grantHost ?: '%'));
                } catch (\Throwable $e) { /* already gone */ }
            }
            return ['success' => true, 'message' => 'Database dropped.'];
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendly($e)];
        }
    }

    private function pdo(): \PDO
    {
        return new \PDO(
            "mysql:host={$this->host};port={$this->port};charset=utf8mb4",
            $this->user, $this->pass,
            [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 8]
        );
    }

    private function friendly(\Throwable $e): string
    {
        $raw = $e->getMessage();
        $code = ($e instanceof \PDOException && isset($e->errorInfo[1])) ? (int) $e->errorInfo[1] : 0;
        if ($code === 1044 || $code === 1045 || stripos($raw, 'access denied') !== false) {
            return 'MySQL refused the credentials or the operation. On shared hosting the account usually cannot create databases — use the cPanel or DirectAdmin driver instead.';
        }
        if ($code === 2002 || stripos($raw, 'timed out') !== false || stripos($raw, 'refused') !== false) {
            return 'Could not reach MySQL at ' . $this->host . ':' . $this->port
                 . '. Most shared hosts block remote port 3306 — use the cPanel or DirectAdmin driver instead.';
        }
        return $raw;
    }
}
