<?php

declare(strict_types=1);

namespace App\Shared\Support;

use App\Core\Auth;
use App\Shared\Schema\SchemaSupport;
use PDO;
use Throwable;

final class BackupSupport
{
    public static function backupsDir(): string
    {
        $dir = Path::public('backups');
        if (!is_dir($dir)) {
            @mkdir($dir, 0775, true);
        }

        return $dir;
    }

    public static function listTables(PDO $pdo): array
    {
        $tables = [];
        foreach ($pdo->query('SHOW TABLES') as $row) {
            $tables[] = array_values($row)[0];
        }

        return $tables;
    }

    public static function escapeIdentifier(string $name): string
    {
        return '`' . str_replace('`', '``', $name) . '`';
    }

    public static function createSqlBackup(PDO $pdo, string $note = ''): string
    {
        $tables = self::listTables($pdo);
        $stamp = date('Ymd_His');
        $file = self::backupsDir() . DIRECTORY_SEPARATOR . "backup_{$stamp}.sql";

        $fh = fopen($file, 'wb');
        fwrite($fh, "-- StoreApp SQL Backup\n");
        fwrite($fh, '-- Created at: ' . date('c') . "\n\n");
        fwrite($fh, "SET FOREIGN_KEY_CHECKS=0;\n\n");

        foreach ($tables as $table) {
            $st = $pdo->query('SHOW CREATE TABLE ' . self::escapeIdentifier($table));
            $row = $st->fetch();
            $createSql = $row['Create Table'] ?? ($row[1] ?? null);
            if ($createSql) {
                fwrite($fh, "-- Table: {$table}\n");
                fwrite($fh, 'DROP TABLE IF EXISTS ' . self::escapeIdentifier($table) . ";\n");
                fwrite($fh, $createSql . ";\n\n");
            }

            $rows = $pdo->query('SELECT * FROM ' . self::escapeIdentifier($table));
            $cols = null;
            $count = 0;
            while ($r = $rows->fetch(PDO::FETCH_ASSOC)) {
                if ($cols === null) {
                    $cols = array_keys($r);
                }
                $vals = [];
                foreach ($cols as $c) {
                    $v = $r[$c];
                    $vals[] = $v === null ? 'NULL' : $pdo->quote((string) $v);
                }
                $colList = implode(',', array_map([self::class, 'escapeIdentifier'], $cols));
                fwrite($fh, 'INSERT INTO ' . self::escapeIdentifier($table) . " ({$colList}) VALUES (" . implode(',', $vals) . ");\n");
                $count++;
                if ($count % 500 === 0) {
                    fwrite($fh, "\n");
                }
            }
            fwrite($fh, "\n\n");
        }

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

        if (SchemaSupport::tableExists($pdo, 'db_backups')) {
            try {
                $st = $pdo->prepare('INSERT INTO db_backups (file_name, size_bytes, note, created_by) VALUES (?,?,?,?)');
                $st->execute([
                    basename($file),
                    filesize($file) ?: 0,
                    $note !== '' ? $note : null,
                    Auth::id(),
                ]);
            } catch (Throwable $e) {
            }
        }

        AuditSupport::log('BACKUP_CREATE', 'db_backup', null, ['file' => basename($file), 'note' => $note]);

        return $file;
    }

    public static function restoreSqlBackup(PDO $pdo, string $sqlFilePath): array
    {
        if (!file_exists($sqlFilePath)) {
            return [false, 'ملف النسخة غير موجود'];
        }
        $sql = file_get_contents($sqlFilePath);
        if ($sql === false) {
            return [false, 'تعذر قراءة ملف النسخة'];
        }

        try {
            $pdo->exec('SET FOREIGN_KEY_CHECKS=0');
            $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN) ?: [];
            foreach ($tables as $table) {
                $pdo->exec('DROP TABLE IF EXISTS ' . self::escapeIdentifier((string) $table));
            }
            $sql = preg_replace('/\/\*![0-9]{5} DEFINER=`[^`]+`@`[^`]+` SQL SECURITY DEFINER \*\//', '', $sql);
            $sql = preg_replace('/DEFINER=`[^`]+`@`[^`]+`/', '', $sql);
            $statements = preg_split('/;\s*(?:\r?\n|$)/', $sql);
            foreach ($statements as $stmt) {
                $stmt = trim((string) $stmt);
                if ($stmt === '' || str_starts_with($stmt, '--') || str_starts_with($stmt, '/*') || str_starts_with($stmt, 'LOCK TABLES') || str_starts_with($stmt, 'UNLOCK TABLES')) {
                    continue;
                }
                $pdo->exec($stmt);
            }
            $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
            AuditSupport::log('BACKUP_RESTORE', 'db_backup', null, ['file' => basename($sqlFilePath)]);

            return [true, 'تم الاسترجاع بنجاح'];
        } catch (Throwable $e) {
            try {
                $pdo->exec('SET FOREIGN_KEY_CHECKS=1');
            } catch (Throwable $ignored) {
            }

            return [false, 'فشل الاسترجاع: ' . $e->getMessage()];
        }
    }
}
