<?php

namespace App\Services;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

/**
 * Self-contained database backup / restore. Produces a plain .sql dump using
 * pure PHP (no mysqldump / exec dependency) so it runs on any shared host,
 * and restores a dump created by this tool (or any standard MySQL dump).
 */
class DatabaseBackup
{
    public function dir(): string
    {
        $path = storage_path('app/backups');
        File::ensureDirectoryExists($path);

        return $path;
    }

    /** Create a full SQL dump and return the filename. */
    public function create(?string $label = null): string
    {
        $database = DB::getDatabaseName();
        $name = 'backup_'.Carbon::now()->format('Y-m-d_His').($label ? '_'.$label : '').'.sql';
        $file = $this->dir().DIRECTORY_SEPARATOR.$name;

        $handle = fopen($file, 'w');
        fwrite($handle, "-- SMS ERP backup\n-- Database: {$database}\n-- Created: ".Carbon::now()->toDateTimeString()."\n\n");
        fwrite($handle, "SET FOREIGN_KEY_CHECKS=0;\nSET NAMES utf8mb4;\n\n");

        foreach ($this->tables() as $table) {
            // Structure
            $create = DB::selectOne("SHOW CREATE TABLE `{$table}`");
            $createSql = $create->{'Create Table'} ?? array_values((array) $create)[1] ?? '';
            fwrite($handle, "DROP TABLE IF EXISTS `{$table}`;\n{$createSql};\n\n");

            // Data (chunked to keep memory low)
            $pdo = DB::getPdo();
            DB::table($table)->orderBy(DB::raw('1'))->chunk(500, function ($rows) use ($handle, $table, $pdo) {
                foreach ($rows as $row) {
                    $row = (array) $row;
                    $cols = '`'.implode('`,`', array_keys($row)).'`';
                    $vals = array_map(function ($v) use ($pdo) {
                        if (is_null($v)) {
                            return 'NULL';
                        }

                        return $pdo->quote((string) $v);
                    }, array_values($row));
                    fwrite($handle, "INSERT INTO `{$table}` ({$cols}) VALUES (".implode(',', $vals).");\n");
                }
            });
            fwrite($handle, "\n");
        }

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

        return $name;
    }

    /** Restore the database from a .sql dump file (executes statement-by-statement). */
    public function restore(string $path): void
    {
        $sql = file_get_contents($path);
        if ($sql === false || trim($sql) === '') {
            throw new \RuntimeException('The backup file is empty or unreadable.');
        }

        DB::unprepared('SET FOREIGN_KEY_CHECKS=0');
        foreach ($this->splitStatements($sql) as $statement) {
            $clean = preg_replace('/^\s*--.*$/m', '', $statement); // drop -- comment lines
            if (trim($clean) === '') {
                continue;
            }
            DB::unprepared($statement);
        }
        DB::unprepared('SET FOREIGN_KEY_CHECKS=1');
    }

    /** Split a SQL script into individual statements, respecting quoted strings. */
    private function splitStatements(string $sql): array
    {
        $statements = [];
        $buffer = '';
        $len = strlen($sql);
        $inString = false;
        $quote = '';

        for ($i = 0; $i < $len; $i++) {
            $ch = $sql[$i];
            $buffer .= $ch;

            if ($inString) {
                if ($ch === '\\' && $i + 1 < $len) {      // escaped char
                    $buffer .= $sql[$i + 1];
                    $i++;
                } elseif ($ch === $quote) {
                    if ($i + 1 < $len && $sql[$i + 1] === $quote) { // doubled quote
                        $buffer .= $sql[$i + 1];
                        $i++;
                    } else {
                        $inString = false;
                    }
                }

                continue;
            }

            if ($ch === "'" || $ch === '"' || $ch === '`') {
                $inString = true;
                $quote = $ch;
            } elseif ($ch === ';') {
                $stmt = trim(rtrim(trim($buffer), ';'));
                if ($stmt !== '') {
                    $statements[] = $stmt;
                }
                $buffer = '';
            }
        }

        if (trim($buffer) !== '') {
            $statements[] = trim($buffer);
        }

        return $statements;
    }

    /** List available backups, newest first. */
    public function list(): array
    {
        $files = collect(File::files($this->dir()))
            ->filter(fn ($f) => in_array(strtolower($f->getExtension()), ['sql', 'gz'], true))
            ->map(fn ($f) => [
                'name' => $f->getFilename(),
                'size' => $f->getSize(),
                'created_at' => Carbon::createFromTimestamp($f->getMTime()),
            ])
            ->sortByDesc('created_at')->values()->all();

        return $files;
    }

    /** Keep only the newest N backups, delete the rest. */
    public function prune(int $keep = 14): int
    {
        $deleted = 0;
        foreach (array_slice($this->list(), $keep) as $b) {
            File::delete($this->dir().DIRECTORY_SEPARATOR.$b['name']);
            $deleted++;
        }

        return $deleted;
    }

    private function tables(): array
    {
        $db = DB::getDatabaseName();
        $key = 'Tables_in_'.$db;

        return collect(DB::select('SHOW TABLES'))->map(fn ($t) => $t->$key)->all();
    }

    /* ---------------- schedule settings (JSON file) ---------------- */

    private function settingsFile(): string
    {
        return $this->dir().DIRECTORY_SEPARATOR.'schedule.json';
    }

    public function settings(): array
    {
        $default = ['enabled' => false, 'frequency' => 'daily', 'keep' => 14];
        if (! File::exists($this->settingsFile())) {
            return $default;
        }

        return array_merge($default, json_decode(File::get($this->settingsFile()), true) ?: []);
    }

    public function saveSettings(array $data): void
    {
        File::put($this->settingsFile(), json_encode(array_merge($this->settings(), $data)));
    }
}
