<?php

declare(strict_types=1);

namespace BotGuard\Services;

use BotGuard\Core\Database;
use RuntimeException;

/**
 * Pure-PHP logical database backup.
 *
 * Dumps every table's structure (SHOW CREATE TABLE) and data (parameter-quoted
 * INSERT statements) to a timestamped .sql file under storage/backups — no
 * dependency on the `mysqldump` binary being available on the host. Filenames
 * are strictly validated to prevent path traversal on download/delete.
 */
final class BackupService
{
    private const NAME_PATTERN = '/^backup-\d{8}-\d{6}\.sql$/';

    private string $dir;

    public function __construct(private Database $db)
    {
        $this->dir = storage_path('backups');
        if (! is_dir($this->dir)) {
            @mkdir($this->dir, 0775, true);
        }
    }

    /**
     * Create a backup file and return its name.
     */
    public function create(): string
    {
        $name = 'backup-' . date('Ymd-His') . '.sql';
        $path = $this->dir . DIRECTORY_SEPARATOR . $name;

        $handle = fopen($path, 'w');
        if ($handle === false) {
            throw new RuntimeException('Unable to open the backup file for writing.');
        }

        $database = (string) config('database.database');
        fwrite($handle, "-- BotGuard backup\n-- Database: {$database}\n-- Generated: " . now() . " UTC\n\n");
        fwrite($handle, "SET FOREIGN_KEY_CHECKS=0;\nSET NAMES utf8mb4;\n\n");

        foreach ($this->tables() as $table) {
            $this->dumpTable($handle, $table);
        }

        fwrite($handle, "SET FOREIGN_KEY_CHECKS=1;\n");
        fclose($handle);

        return $name;
    }

    /**
     * List existing backups, newest first.
     *
     * @return array<int, array{name:string, size:int, modified:string}>
     */
    public function list(): array
    {
        $files = glob($this->dir . DIRECTORY_SEPARATOR . 'backup-*.sql') ?: [];
        $out = [];

        foreach ($files as $file) {
            $out[] = [
                'name'     => basename($file),
                'size'     => (int) filesize($file),
                'modified' => date('Y-m-d H:i:s', (int) filemtime($file)),
            ];
        }

        usort($out, static fn (array $a, array $b): int => strcmp($b['name'], $a['name']));

        return $out;
    }

    /**
     * Resolve a validated backup filename to its absolute path, or null.
     */
    public function path(string $name): ?string
    {
        if (! preg_match(self::NAME_PATTERN, $name)) {
            return null;
        }

        $path = $this->dir . DIRECTORY_SEPARATOR . $name;

        return is_file($path) ? $path : null;
    }

    public function delete(string $name): bool
    {
        $path = $this->path($name);

        return $path !== null && @unlink($path);
    }

    /**
     * @return string[]
     */
    private function tables(): array
    {
        $rows = $this->db->select('SHOW TABLES');
        $tables = [];
        foreach ($rows as $row) {
            $tables[] = (string) reset($row);
        }

        return $tables;
    }

    /**
     * @param resource $handle
     */
    private function dumpTable($handle, string $table): void
    {
        fwrite($handle, "\n-- ----------------------------\n-- Table: {$table}\n-- ----------------------------\n");
        fwrite($handle, "DROP TABLE IF EXISTS `{$table}`;\n");

        $create = $this->db->selectOne("SHOW CREATE TABLE `{$table}`");
        $createSql = $create['Create Table'] ?? ($create['Create View'] ?? '');
        fwrite($handle, $createSql . ";\n\n");

        $pdo = $this->db->pdo();
        $stmt = $pdo->query("SELECT * FROM `{$table}`");

        while (($row = $stmt->fetch()) !== false) {
            $columns = '`' . implode('`, `', array_keys($row)) . '`';
            $values = array_map(
                static fn ($v): string => $v === null ? 'NULL' : $pdo->quote((string) $v),
                array_values($row)
            );
            fwrite($handle, "INSERT INTO `{$table}` ({$columns}) VALUES (" . implode(', ', $values) . ");\n");
        }
    }
}
