<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class DatabaseRestore extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'db:restore 
                            {backup : Path to backup file}
                            {--force : Force restore without confirmation}';

    /**
     * The console command description.
     */
    protected $description = 'Restore database from a backup file';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $backupPath = $this->argument('backup');
        $force = $this->option('force');

        // Make path relative to storage/app/backups if not absolute
        if (!File::exists($backupPath)) {
            $backupPath = storage_path("app/backups/{$backupPath}");
        }

        if (!File::exists($backupPath)) {
            $this->error("Backup file not found: {$backupPath}");
            return 1;
        }

        // Show backup info
        $this->info("Backup file: {$backupPath}");
        $this->info("File size: " . $this->formatBytes(File::size($backupPath)));
        $this->info("Created: " . date('Y-m-d H:i:s', File::lastModified($backupPath)));

        // Confirmation
        if (!$force && !$this->confirm('This will replace your current database. Are you sure?')) {
            $this->info('Restore cancelled.');
            return 0;
        }

        try {
            $extension = pathinfo($backupPath, PATHINFO_EXTENSION);
            
            // Handle compressed files
            if ($extension === 'gz') {
                $this->info("Decompressing backup...");
                $backupPath = $this->decompressFile($backupPath);
                $extension = pathinfo($backupPath, PATHINFO_EXTENSION);
            }

            switch ($extension) {
                case 'sql':
                    $this->restoreFromSql($backupPath);
                    break;
                case 'json':
                    $this->restoreFromJson($backupPath);
                    break;
                case 'sqlite':
                    $this->restoreFromSqlite($backupPath);
                    break;
                default:
                    $this->error("Unsupported backup format: {$extension}");
                    return 1;
            }

            $this->info("✅ Database restored successfully!");
            return 0;

        } catch (\Exception $e) {
            $this->error("Restore failed: " . $e->getMessage());
            return 1;
        }
    }

    /**
     * Restore from SQL dump
     */
    private function restoreFromSql($backupPath)
    {
        $this->info("Restoring from SQL dump...");
        
        $dbPath = database_path('database.sqlite');
        
        // Backup current database
        $currentBackup = database_path('database.sqlite.pre-restore-' . now()->format('Y-m-d_H-i-s'));
        if (File::exists($dbPath)) {
            File::copy($dbPath, $currentBackup);
            $this->info("Current database backed up to: {$currentBackup}");
        }

        // Remove current database
        if (File::exists($dbPath)) {
            File::delete($dbPath);
        }

        // Restore from SQL
        $command = "sqlite3 \"{$dbPath}\" < \"{$backupPath}\"";
        $result = shell_exec($command);

        if (!File::exists($dbPath)) {
            throw new \Exception("Failed to restore database from SQL dump");
        }
    }

    /**
     * Restore from JSON backup
     */
    private function restoreFromJson($backupPath)
    {
        $this->info("Restoring from JSON backup...");

        $backupData = json_decode(File::get($backupPath), true);
        
        if (!$backupData || !isset($backupData['tables'])) {
            throw new \Exception("Invalid JSON backup format");
        }

        $this->info("Backup created: " . ($backupData['created_at'] ?? 'Unknown'));

        // Clear existing data
        DB::statement('PRAGMA foreign_keys=OFF');
        
        foreach ($backupData['tables'] as $tableName => $tableData) {
            if (!\Illuminate\Support\Facades\Schema::hasTable($tableName)) {
                $this->warn("Table '{$tableName}' does not exist in current schema, skipping...");
                continue;
            }

            $this->info("Restoring table: {$tableName} ({$tableData['count']} records)");
            
            // Truncate table
            DB::table($tableName)->truncate();
            
            // Insert data in chunks
            if (!empty($tableData['data'])) {
                $chunks = array_chunk($tableData['data'], 1000);
                foreach ($chunks as $chunk) {
                    DB::table($tableName)->insert($chunk);
                }
            }
        }

        DB::statement('PRAGMA foreign_keys=ON');
    }

    /**
     * Restore from SQLite file
     */
    private function restoreFromSqlite($backupPath)
    {
        $this->info("Restoring from SQLite file...");
        
        $dbPath = database_path('database.sqlite');
        
        // Backup current database
        if (File::exists($dbPath)) {
            $currentBackup = database_path('database.sqlite.pre-restore-' . now()->format('Y-m-d_H-i-s'));
            File::copy($dbPath, $currentBackup);
            $this->info("Current database backed up to: {$currentBackup}");
        }

        // Copy backup file to database location
        File::copy($backupPath, $dbPath);
    }

    /**
     * Decompress a gzipped file
     */
    private function decompressFile($filePath)
    {
        $outputPath = str_replace('.gz', '', $filePath);
        
        $command = "gunzip -c \"{$filePath}\" > \"{$outputPath}\"";
        shell_exec($command);
        
        if (!File::exists($outputPath)) {
            throw new \Exception("Failed to decompress file: {$filePath}");
        }

        return $outputPath;
    }

    /**
     * Format bytes to human readable format
     */
    private function formatBytes($bytes, $precision = 2)
    {
        $units = array('B', 'KB', 'MB', 'GB', 'TB');

        for ($i = 0; $bytes > 1024 && $i < count($units) - 1; $i++) {
            $bytes /= 1024;
        }

        return round($bytes, $precision) . ' ' . $units[$i];
    }
}