<?php
declare(strict_types=1);

final class DatabaseBackupService
{
    public static function directory(): string
    {
        return dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'backups';
    }

    public static function list(): array
    {
        $files = glob(self::directory() . DIRECTORY_SEPARATOR . 'fortunes-quotation-backup-*.sql') ?: [];
        usort($files, static fn(string $a, string $b): int => filemtime($b) <=> filemtime($a));
        return array_map(static fn(string $file): array => [
            'name' => basename($file),
            'path' => $file,
            'size' => filesize($file) ?: 0,
            'created_at' => date('Y-m-d H:i:s', filemtime($file) ?: time()),
        ], $files);
    }

    public static function create(PDO $pdo): string
    {
        $dir = self::directory();
        if (!is_dir($dir) && !mkdir($dir, 0755, true)) {
            throw new RuntimeException('Backup directory could not be created.');
        }
        $filename = 'fortunes-quotation-backup-' . date('Ymd-His') . '.sql';
        $path = $dir . DIRECTORY_SEPARATOR . $filename;
        $sql = self::exportWithPdo($pdo);
        if (file_put_contents($path, $sql, LOCK_EX) === false) {
            throw new RuntimeException('Backup file could not be written.');
        }
        return $filename;
    }

    public static function resolve(string $filename): string
    {
        if (!preg_match('/^fortunes-quotation-backup-\d{8}-\d{6}\.sql$/', $filename)) {
            throw new RuntimeException('Invalid backup file.');
        }
        $path = self::directory() . DIRECTORY_SEPARATOR . $filename;
        $dir = realpath(self::directory());
        $real = realpath($path);
        if (!$dir || !$real || !str_starts_with(str_replace('\\', '/', $real), str_replace('\\', '/', $dir)) || !is_file($real)) {
            throw new RuntimeException('Backup file not found.');
        }
        return $real;
    }

    private static function exportWithPdo(PDO $pdo): string
    {
        $lines = [
            '-- Fortunes Solar Quotation System database backup',
            '-- Created at: ' . date('Y-m-d H:i:s'),
            'SET FOREIGN_KEY_CHECKS=0;',
            '',
        ];
        $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
        foreach ($tables as $table) {
            $quotedTable = self::quoteIdentifier((string)$table);
            $create = $pdo->query('SHOW CREATE TABLE ' . $quotedTable)->fetch(PDO::FETCH_ASSOC);
            $createSql = (string)array_values($create)[1];
            $lines[] = 'DROP TABLE IF EXISTS ' . $quotedTable . ';';
            $lines[] = $createSql . ';';
            $lines[] = '';

            $stmt = $pdo->query('SELECT * FROM ' . $quotedTable);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $columns = array_map([self::class, 'quoteIdentifier'], array_keys($row));
                $values = array_map(static fn($value): string => $value === null ? 'NULL' : $pdo->quote((string)$value), array_values($row));
                $lines[] = 'INSERT INTO ' . $quotedTable . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ');';
            }
            $lines[] = '';
        }
        $lines[] = 'SET FOREIGN_KEY_CHECKS=1;';
        $lines[] = '';
        return implode("\n", $lines);
    }

    private static function quoteIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }
}
