<?php

namespace App\Services;

use App\Models\Order;
use Illuminate\Support\Facades\DB;
use RuntimeException;
use ZipArchive;

class DatabaseBackupService
{
    public function directory(): string
    {
        $dir = storage_path('app/private/backups');
        if (!is_dir($dir) && !mkdir($dir, 0755, true) && !is_dir($dir)) {
            throw new RuntimeException('امکان ایجاد پوشه بک‌آپ وجود ندارد.');
        }

        return $dir;
    }

    /**
     * @return array{filename:string, path:string, size:int}
     */
    public function create(): array
    {
        if (!class_exists(ZipArchive::class)) {
            throw new RuntimeException('افزونه ZipArchive روی سرور فعال نیست.');
        }

        @set_time_limit(0);
        @ini_set('memory_limit', '512M');

        $connection = DB::connection();
        $pdo        = $connection->getPdo();
        $dbName     = (string) $connection->getDatabaseName();
        $driver     = $connection->getDriverName();

        if ($driver !== 'mysql' && $driver !== 'mariadb') {
            throw new RuntimeException('بک‌آپ فعلاً فقط برای MySQL/MariaDB پشتیبانی می‌شود.');
        }

        $stamp   = now()->format('Y-m-d_H-i-s');
        $sqlName = $dbName . '_' . $stamp . '.sql';
        $zipName = $dbName . '_' . $stamp . '.zip';
        $dir     = $this->directory();
        $sqlPath = $dir . DIRECTORY_SEPARATOR . $sqlName;
        $zipPath = $dir . DIRECTORY_SEPARATOR . $zipName;

        $fh = fopen($sqlPath, 'wb');
        if ($fh === false) {
            throw new RuntimeException('امکان نوشتن فایل SQL وجود ندارد.');
        }

        try {
            $this->writeHeader($fh, $dbName);
            $this->writeTables($fh, $pdo);
            fwrite($fh, "\nSET FOREIGN_KEY_CHECKS=1;\n");
            fwrite($fh, "COMMIT;\n");
        } catch (\Throwable $e) {
            fclose($fh);
            @unlink($sqlPath);
            throw new RuntimeException('خطا در ساخت بک‌آپ: ' . $e->getMessage(), 0, $e);
        }

        fclose($fh);

        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            @unlink($sqlPath);
            throw new RuntimeException('امکان ساخت فایل ZIP وجود ندارد.');
        }

        $zip->addFile($sqlPath, $sqlName);
        $zip->close();
        @unlink($sqlPath);

        return [
            'filename' => $zipName,
            'path'     => $zipPath,
            'size'     => (int) (@filesize($zipPath) ?: 0),
        ];
    }

    /**
     * @return list<array{filename:string, size:int, size_label:string, created_at:string, created_at_jalali:string, created_at_time:string}>
     */
    public function list(): array
    {
        $dir = $this->directory();
        $files = glob($dir . DIRECTORY_SEPARATOR . '*.zip') ?: [];

        usort($files, fn ($a, $b) => filemtime($b) <=> filemtime($a));

        $items = [];
        foreach ($files as $path) {
            $mtime = filemtime($path) ?: time();
            $dt    = \Carbon\Carbon::createFromTimestamp($mtime);
            $size  = (int) (@filesize($path) ?: 0);

            $items[] = [
                'filename'         => basename($path),
                'size'             => $size,
                'size_label'       => $this->formatBytes($size),
                'created_at'       => $dt->toDateTimeString(),
                'created_at_jalali'=> Order::toJalaliString($dt),
                'created_at_time'  => $dt->format('H:i:s'),
            ];
        }

        return $items;
    }

    public function pathFor(string $filename): string
    {
        $filename = basename($filename);
        if (!preg_match('/^[A-Za-z0-9._-]+\.zip$/', $filename)) {
            throw new RuntimeException('نام فایل نامعتبر است.');
        }

        $path = $this->directory() . DIRECTORY_SEPARATOR . $filename;
        if (!is_file($path)) {
            throw new RuntimeException('فایل بک‌آپ پیدا نشد.');
        }

        return $path;
    }

    public function delete(string $filename): void
    {
        $path = $this->pathFor($filename);
        if (!@unlink($path)) {
            throw new RuntimeException('حذف فایل بک‌آپ ناموفق بود.');
        }
    }

    /** @param resource $fh */
    private function writeHeader($fh, string $dbName): void
    {
        $now = now()->toDateTimeString();
        fwrite($fh, "-- BiatoShop database backup\n");
        fwrite($fh, "-- Database: {$dbName}\n");
        fwrite($fh, "-- Generated at: {$now}\n");
        fwrite($fh, "-- Method: PHP PDO (no shell)\n\n");
        fwrite($fh, "SET NAMES utf8mb4;\n");
        fwrite($fh, "SET FOREIGN_KEY_CHECKS=0;\n");
        fwrite($fh, "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n");
        fwrite($fh, "START TRANSACTION;\n\n");
    }

    /** @param resource $fh */
    private function writeTables($fh, \PDO $pdo): void
    {
        $tablesStmt = $pdo->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
        if ($tablesStmt === false) {
            throw new RuntimeException('دریافت لیست جداول ناموفق بود.');
        }

        $tables = $tablesStmt->fetchAll(\PDO::FETCH_NUM);
        foreach ($tables as $row) {
            $table = (string) $row[0];
            $this->writeTable($fh, $pdo, $table);
        }
    }

    /** @param resource $fh */
    private function writeTable($fh, \PDO $pdo, string $table): void
    {
        $safeTable = str_replace('`', '``', $table);

        fwrite($fh, "-- ----------------------------\n");
        fwrite($fh, "-- Table structure for `{$safeTable}`\n");
        fwrite($fh, "-- ----------------------------\n");
        fwrite($fh, "DROP TABLE IF EXISTS `{$safeTable}`;\n");

        $createStmt = $pdo->query("SHOW CREATE TABLE `{$safeTable}`");
        if ($createStmt === false) {
            throw new RuntimeException("دریافت ساختار جدول {$table} ناموفق بود.");
        }

        $createRow = $createStmt->fetch(\PDO::FETCH_ASSOC) ?: [];
        $createSql = $createRow['Create Table'] ?? null;
        if (!$createSql) {
            $values = array_values($createRow);
            $createSql = $values[1] ?? null;
        }
        if (!$createSql) {
            throw new RuntimeException("ساختار جدول {$table} خالی است.");
        }

        fwrite($fh, $createSql . ";\n\n");
        fwrite($fh, "-- Dumping data for table `{$safeTable}`\n");

        $dataStmt = $pdo->query("SELECT * FROM `{$safeTable}`");
        if ($dataStmt === false) {
            throw new RuntimeException("خواندن داده جدول {$table} ناموفق بود.");
        }
        $dataStmt->setFetchMode(\PDO::FETCH_ASSOC);

        $batch = [];
        $batchSize = 100;
        $columns = null;

        while ($row = $dataStmt->fetch(\PDO::FETCH_ASSOC)) {
            if ($columns === null) {
                $columns = array_map(
                    fn ($col) => '`' . str_replace('`', '``', $col) . '`',
                    array_keys($row)
                );
            }

            $values = [];
            foreach ($row as $value) {
                $values[] = $this->sqlValue($pdo, $value);
            }
            $batch[] = '(' . implode(',', $values) . ')';

            if (count($batch) >= $batchSize) {
                $this->flushInsert($fh, $safeTable, $columns, $batch);
                $batch = [];
            }
        }

        if ($batch !== [] && $columns !== null) {
            $this->flushInsert($fh, $safeTable, $columns, $batch);
        }

        fwrite($fh, "\n");
    }

    /**
     * @param resource $fh
     * @param list<string> $columns
     * @param list<string> $batch
     */
    private function flushInsert($fh, string $safeTable, array $columns, array $batch): void
    {
        fwrite(
            $fh,
            'INSERT INTO `' . $safeTable . '` (' . implode(',', $columns) . ") VALUES\n"
            . implode(",\n", $batch)
            . ";\n"
        );
    }

    private function sqlValue(\PDO $pdo, mixed $value): string
    {
        if ($value === null) {
            return 'NULL';
        }

        if (is_bool($value)) {
            return $value ? '1' : '0';
        }

        if (is_int($value) || is_float($value)) {
            return (string) $value;
        }

        if (is_string($value) && (str_contains($value, "\0") || !mb_check_encoding($value, 'UTF-8'))) {
            return '0x' . bin2hex($value);
        }

        return $pdo->quote((string) $value);
    }

    private function formatBytes(int $bytes): string
    {
        if ($bytes < 1024) {
            return $bytes . ' B';
        }
        if ($bytes < 1048576) {
            return number_format($bytes / 1024, 1) . ' KB';
        }
        if ($bytes < 1073741824) {
            return number_format($bytes / 1048576, 2) . ' MB';
        }

        return number_format($bytes / 1073741824, 2) . ' GB';
    }
}
