<?php
namespace App\Services;

use App\Core\Database;
use PDO;
use RuntimeException;
use Throwable;

final class BackupService
{
    public function createDatabaseBackup(?int $requestedBy = null): array
    {
        $config = $GLOBALS['config']['database'] ?? [];
        $backupDir = $this->backupDir();
        if (!is_dir($backupDir) && !mkdir($backupDir, 0775, true) && !is_dir($backupDir)) {
            throw new RuntimeException('Could not create backup directory: ' . $backupDir);
        }

        $file = $backupDir . '/pyrk_db_' . date('Ymd_His') . '.sql';
        $method = 'pdo';

        try {
            if ($this->mysqldumpAvailable()) {
                $this->createWithMysqldump($file, $config);
                $method = 'mysqldump';
            } else {
                $this->createWithPdo($file);
            }

            $size = file_exists($file) ? filesize($file) : 0;
            $this->logBackup(basename($file), $file, $size, 'created', $method, null, $requestedBy);
            $this->cleanupOldBackups();

            (new NotificationService())->notifyBackupResult(true, 'Database backup created successfully.', $file);

            return [
                'file_name' => basename($file),
                'file_path' => $file,
                'size_bytes' => $size,
                'method' => $method,
            ];
        } catch (Throwable $e) {
            $this->logBackup(basename($file), $file, 0, 'failed', $method, $e->getMessage(), $requestedBy);
            (new NotificationService())->notifyBackupResult(false, 'Database backup failed: ' . $e->getMessage(), $file);
            throw $e;
        }
    }

    public function listBackups(): array
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->query('SELECT * FROM database_backups ORDER BY created_at DESC, id DESC LIMIT 100');
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Throwable) {
            $rows = [];
            foreach (glob($this->backupDir() . '/*.sql') ?: [] as $file) {
                $rows[] = [
                    'file_name' => basename($file),
                    'file_path' => $file,
                    'size_bytes' => filesize($file),
                    'status' => 'created',
                    'created_at' => date('Y-m-d H:i:s', filemtime($file)),
                ];
            }
            return $rows;
        }
    }

    private function backupDir(): string
    {
        $settings = new NotificationService();
        $configured = trim($settings->setting('db_backup_path', ''));
        if ($configured !== '') {
            return $configured;
        }
        return dirname(__DIR__, 2) . '/storage/backups';
    }

    private function retentionDays(): int
    {
        $days = (int)(new NotificationService())->setting('db_backup_retention_days', '30');
        return max(1, $days);
    }

    private function mysqldumpAvailable(): bool
    {
        $cmd = stripos(PHP_OS_FAMILY, 'Windows') === 0 ? 'where mysqldump' : 'command -v mysqldump';
        $output = [];
        $code = 1;
        @exec($cmd . ' 2>/dev/null', $output, $code);
        return $code === 0 && !empty($output);
    }

    private function createWithMysqldump(string $file, array $config): void
    {
        $host = $config['host'] ?? '127.0.0.1';
        $port = (string)($config['port'] ?? '3306');
        $db = $config['name'] ?? '';
        $user = $config['user'] ?? '';
        $pass = $config['password'] ?? '';

        if ($db === '' || $user === '') {
            throw new RuntimeException('Missing database name or user in config.');
        }

        $cmd = 'mysqldump'
            . ' --host=' . escapeshellarg($host)
            . ' --port=' . escapeshellarg($port)
            . ' --user=' . escapeshellarg($user)
            . ' --password=' . escapeshellarg($pass)
            . ' --single-transaction --quick --routines --triggers '
            . escapeshellarg($db)
            . ' > ' . escapeshellarg($file);

        $output = [];
        $code = 0;
        @exec($cmd . ' 2>&1', $output, $code);
        if ($code !== 0 || !file_exists($file) || filesize($file) === 0) {
            @unlink($file);
            throw new RuntimeException('mysqldump failed: ' . implode(' ', $output));
        }
    }

    private function createWithPdo(string $file): void
    {
        $pdo = Database::connection();
        $handle = fopen($file, 'wb');
        if (!$handle) {
            throw new RuntimeException('Could not write backup file.');
        }

        fwrite($handle, "-- Pyrk Walking Club SQL backup\n");
        fwrite($handle, "-- Created: " . date('c') . "\n\n");
        fwrite($handle, "SET FOREIGN_KEY_CHECKS=0;\n\n");

        $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
        foreach ($tables as $table) {
            $createRow = $pdo->query('SHOW CREATE TABLE `' . str_replace('`', '``', $table) . '`')->fetch(PDO::FETCH_ASSOC);
            $createSql = $createRow['Create Table'] ?? array_values($createRow)[1] ?? '';
            fwrite($handle, "DROP TABLE IF EXISTS `{$table}`;\n");
            fwrite($handle, $createSql . ";\n\n");

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

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

    private function logBackup(string $fileName, string $filePath, int $size, string $status, string $method, ?string $error, ?int $requestedBy): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('INSERT INTO database_backups (file_name, file_path, size_bytes, status, method, error_message, requested_by, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())');
            $stmt->execute([$fileName, $filePath, $size, $status, $method, $error, $requestedBy]);
        } catch (Throwable) {
            // Migration may not be installed yet.
        }
    }

    private function cleanupOldBackups(): void
    {
        $cutoff = time() - ($this->retentionDays() * 86400);
        foreach (glob($this->backupDir() . '/pyrk_db_*.sql') ?: [] as $file) {
            if (is_file($file) && filemtime($file) < $cutoff) {
                @unlink($file);
            }
        }
    }
}
