<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Carbon\Carbon;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use ZipArchive;

class DatabaseBackup extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'database:backup {--type=sql : Backup type: sql, zip, or gzip}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Take backup of database and save it on storage folder';

    /**
     * Supported backup types
     */
    protected $supportedTypes = ['sql', 'zip', 'gzip'];

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $backupType = strtolower($this->option('type'));

        if (!in_array($backupType, $this->supportedTypes)) {
            $this->error("Invalid backup type: {$backupType}. Supported types: " . implode(', ', $this->supportedTypes));
            return 1;
        }

        $randomId = Str::random(8);
        $todayDate = Carbon::now()->format('Y-m-d');
        $todayTime = Carbon::now()->format('H:i:s');
        $path = Storage::disk('backup')->path('');

        // Create backup directory if it doesn't exist
        if (!is_dir($path)) {
            mkdir($path, 0755, true);
        }

        $databasePassword = config('database.connections.mysql.password', '');
        $databaseUsername = config('database.connections.mysql.username', 'root');
        $databaseHost = config('database.connections.mysql.host', '127.0.0.1');
        $databaseName = config('database.connections.mysql.database');

        $baseFilename = $randomId . "--backup--" . $todayDate . "--" . $todayTime;

        // Get mysqldump path - try env first, then auto-detect
        $dumpPath = $this->findMysqldumpPath();

        if (empty($dumpPath) || $dumpPath === 'mysqldump') {
            // Verify mysqldump is actually available
            exec('mysqldump --version 2>&1', $versionOutput, $versionReturnCode);
            if ($versionReturnCode !== 0) {
                $this->error('mysqldump not found. Please set DUMP_PATH in .env file.');
                $this->info('Common paths:');
                $this->info('  - cPanel/Linux: /usr/bin/mysqldump');
                $this->info('  - XAMPP: /opt/lampp/bin/mysqldump');
                $this->info('  - MAMP: /Applications/MAMP/Library/bin/mysqldump');
                return 1;
            }
        }

        // Create backup based on type
        switch ($backupType) {
            case 'gzip':
                return $this->createGzipBackup($dumpPath, $databaseUsername, $databasePassword, $databaseHost, $databaseName, $path, $baseFilename);

            case 'zip':
                return $this->createZipBackup($dumpPath, $databaseUsername, $databasePassword, $databaseHost, $databaseName, $path, $baseFilename);

            case 'sql':
            default:
                return $this->createSqlBackup($dumpPath, $databaseUsername, $databasePassword, $databaseHost, $databaseName, $path, $baseFilename);
        }
    }

    /**
     * Create plain SQL backup
     */
    protected function createSqlBackup($dumpPath, $username, $password, $host, $database, $path, $baseFilename)
    {
        $filename = $baseFilename . '.sql';
        $fullPath = $path . $filename;

        $command = $this->buildMysqldumpCommand($dumpPath, $username, $password, $host, $database);
        $command .= ' > ' . escapeshellarg($fullPath);
        $command .= ' 2>&1';

        exec($command, $output, $returnCode);

        if ($returnCode !== 0 || !file_exists($fullPath) || filesize($fullPath) === 0) {
            if (file_exists($fullPath)) {
                unlink($fullPath);
            }
            $this->error('Backup failed: ' . implode("\n", $output));
            return 1;
        }

        $this->info('Backup created successfully: ' . $filename);
        return 0;
    }

    /**
     * Create GZIP compressed backup
     */
    protected function createGzipBackup($dumpPath, $username, $password, $host, $database, $path, $baseFilename)
    {
        $filename = $baseFilename . '.sql.gz';
        $fullPath = $path . $filename;

        $command = $this->buildMysqldumpCommand($dumpPath, $username, $password, $host, $database);

        // Check if gzip is available
        if (PHP_OS_FAMILY !== 'Windows') {
            // On Unix, pipe directly to gzip
            $command .= ' | gzip > ' . escapeshellarg($fullPath);
            $command .= ' 2>&1';

            exec($command, $output, $returnCode);

            if ($returnCode !== 0 || !file_exists($fullPath) || filesize($fullPath) === 0) {
                if (file_exists($fullPath)) {
                    unlink($fullPath);
                }
                $this->error('Backup failed: ' . implode("\n", $output));
                return 1;
            }
        } else {
            // On Windows, create SQL first then compress with PHP
            $sqlPath = $path . $baseFilename . '.sql';
            $command .= ' > ' . escapeshellarg($sqlPath);
            $command .= ' 2>&1';

            exec($command, $output, $returnCode);

            if ($returnCode !== 0 || !file_exists($sqlPath) || filesize($sqlPath) === 0) {
                if (file_exists($sqlPath)) {
                    unlink($sqlPath);
                }
                $this->error('Backup failed: ' . implode("\n", $output));
                return 1;
            }

            // Compress using PHP's gzencode
            $sqlContent = file_get_contents($sqlPath);
            $gzContent = gzencode($sqlContent, 9);

            if (file_put_contents($fullPath, $gzContent) === false) {
                unlink($sqlPath);
                $this->error('Failed to create gzip file');
                return 1;
            }

            // Remove the temporary SQL file
            unlink($sqlPath);
        }

        $this->info('Backup created successfully: ' . $filename);
        return 0;
    }

    /**
     * Create ZIP compressed backup
     */
    protected function createZipBackup($dumpPath, $username, $password, $host, $database, $path, $baseFilename)
    {
        if (!class_exists('ZipArchive')) {
            $this->error('ZipArchive extension is not installed. Please install php-zip extension.');
            return 1;
        }

        $sqlFilename = $baseFilename . '.sql';
        $zipFilename = $baseFilename . '.zip';
        $sqlPath = $path . $sqlFilename;
        $zipPath = $path . $zipFilename;

        // First create the SQL backup
        $command = $this->buildMysqldumpCommand($dumpPath, $username, $password, $host, $database);
        $command .= ' > ' . escapeshellarg($sqlPath);
        $command .= ' 2>&1';

        exec($command, $output, $returnCode);

        if ($returnCode !== 0 || !file_exists($sqlPath) || filesize($sqlPath) === 0) {
            if (file_exists($sqlPath)) {
                unlink($sqlPath);
            }
            $this->error('Backup failed: ' . implode("\n", $output));
            return 1;
        }

        // Create ZIP archive
        $zip = new ZipArchive();
        if ($zip->open($zipPath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            unlink($sqlPath);
            $this->error('Failed to create zip file');
            return 1;
        }

        $zip->addFile($sqlPath, $sqlFilename);
        $zip->close();

        // Remove the temporary SQL file
        unlink($sqlPath);

        if (!file_exists($zipPath) || filesize($zipPath) === 0) {
            $this->error('Failed to create zip file');
            return 1;
        }

        $this->info('Backup created successfully: ' . $zipFilename);
        return 0;
    }

    /**
     * Build the mysqldump command
     */
    protected function buildMysqldumpCommand($dumpPath, $username, $password, $host, $database)
    {
        $command = escapeshellcmd($dumpPath);
        $command .= ' --user=' . escapeshellarg($username);

        if (!empty($password)) {
            $command .= ' --password=' . escapeshellarg($password);
        }

        $command .= ' --host=' . escapeshellarg($host);
        $command .= ' ' . escapeshellarg($database);

        return $command;
    }

    /**
     * Find mysqldump path automatically
     *
     * @return string
     */
    protected function findMysqldumpPath()
    {
        // First check if DUMP_PATH is set in .env
        $dumpPath = env('DUMP_PATH');
        if (!empty($dumpPath) && $this->isExecutable($dumpPath)) {
            return $dumpPath;
        }

        // Common paths for different environments
        $possiblePaths = [
            // cPanel / WHM / Linux servers (most common)
            '/usr/bin/mysqldump',
            '/usr/local/bin/mysqldump',
            '/usr/local/mysql/bin/mysqldump',

            // cPanel with CloudLinux
            '/opt/alt/php*/usr/bin/mysqldump',

            // Plesk
            '/usr/bin/mysqldump',

            // XAMPP on Linux
            '/opt/lampp/bin/mysqldump',

            // MAMP on macOS
            '/Applications/MAMP/Library/bin/mysqldump',

            // Homebrew on macOS
            '/opt/homebrew/bin/mysqldump',
            '/usr/local/opt/mysql/bin/mysqldump',
            '/usr/local/opt/mysql-client/bin/mysqldump',

            // DBngin on macOS
            '/Users/Shared/DBngin/mysql/8.0.33/bin/mysqldump',
            '/Users/Shared/DBngin/mysql/5.7.23/bin/mysqldump',

            // Windows XAMPP
            'C:/xampp/mysql/bin/mysqldump.exe',

            // Windows WAMP
            'C:/wamp64/bin/mysql/mysql8.0.31/bin/mysqldump.exe',
            'C:/wamp/bin/mysql/mysql5.7.36/bin/mysqldump.exe',

            // Windows Laragon
            'C:/laragon/bin/mysql/mysql-8.0.30-winx64/bin/mysqldump.exe',
        ];

        // Check each path
        foreach ($possiblePaths as $path) {
            // Handle glob patterns (for CloudLinux paths)
            if (strpos($path, '*') !== false) {
                $matches = glob($path);
                if (!empty($matches)) {
                    foreach ($matches as $match) {
                        if ($this->isExecutable($match)) {
                            return $match;
                        }
                    }
                }
                continue;
            }

            if ($this->isExecutable($path)) {
                return $path;
            }
        }

        // Try to find using 'which' command on Unix systems
        if (PHP_OS_FAMILY !== 'Windows') {
            exec('which mysqldump 2>/dev/null', $output, $returnCode);
            if ($returnCode === 0 && !empty($output[0])) {
                return trim($output[0]);
            }
        }

        // Try to find using 'where' command on Windows
        if (PHP_OS_FAMILY === 'Windows') {
            exec('where mysqldump 2>nul', $output, $returnCode);
            if ($returnCode === 0 && !empty($output[0])) {
                return trim($output[0]);
            }
        }

        // If still not found, return 'mysqldump' and hope it's in PATH
        return 'mysqldump';
    }

    /**
     * Check if a path is executable
     *
     * @param string $path
     * @return bool
     */
    protected function isExecutable($path)
    {
        if (empty($path)) {
            return false;
        }

        // Check if file exists and is executable
        if (file_exists($path) && is_executable($path)) {
            return true;
        }

        // On Windows, also check for .exe extension
        if (PHP_OS_FAMILY === 'Windows') {
            $exePath = $path . '.exe';
            if (file_exists($exePath) && is_executable($exePath)) {
                return true;
            }
        }

        return false;
    }
}
