<?php

namespace App\Console\Commands;

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

class DatabaseBackup extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'db:backup 
                            {--format=sql : Backup format (sql, json, sqlite)}
                            {--tables= : Comma-separated list of specific tables to backup}
                            {--compress : Compress the backup file}';

    /**
     * The console command description.
     */
    protected $description = 'Create a backup of the SQLite database';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $format = $this->option('format');
        $specificTables = $this->option('tables');
        $compress = $this->option('compress');
        
        // Create backups directory if it doesn't exist
        $backupDir = storage_path('app/backups');
        if (!File::exists($backupDir)) {
            File::makeDirectory($backupDir, 0755, true);
        }

        $timestamp = now()->format('Y-m-d_H-i-s');
        
        $this->info("Starting database backup...");

        try {
            switch ($format) {
                case 'sql':
                    $this->createSqlBackup($timestamp, $specificTables, $compress);
                    break;
                case 'json':
                    $this->createJsonBackup($timestamp, $specificTables, $compress);
                    break;
                case 'sqlite':
                    $this->createSqliteBackup($timestamp, $compress);
                    break;
                default:
                    $this->error("Invalid format: {$format}. Use sql, json, or sqlite.");
                    return 1;
            }
        } catch (\Exception $e) {
            $this->error("Backup failed: " . $e->getMessage());
            return 1;
        }

        return 0;
    }

    /**
     * Create SQL dump backup
     */
    private function createSqlBackup($timestamp, $specificTables, $compress)
    {
        $dbPath = database_path('database.sqlite');
        $backupPath = storage_path("app/backups/database_backup_{$timestamp}.sql");

        if (!File::exists($dbPath)) {
            throw new \Exception("Database file not found: {$dbPath}");
        }

        $this->info("Creating SQL backup...");

        if ($specificTables) {
            // Backup specific tables
            $tables = explode(',', $specificTables);
            $command = "sqlite3 \"{$dbPath}\" \".dump\"";
            $output = shell_exec($command);
            
            // Filter output to only include specified tables
            $filteredOutput = $this->filterSqlDumpForTables($output, $tables);
            File::put($backupPath, $filteredOutput);
        } else {
            // Full database dump
            $command = "sqlite3 \"{$dbPath}\" \".dump\" > \"{$backupPath}\"";
            shell_exec($command);
        }

        if ($compress) {
            $this->compressFile($backupPath);
            $backupPath .= '.gz';
        }

        $size = $this->formatBytes(File::size($backupPath));
        $this->info("✅ SQL backup created: {$backupPath} ({$size})");
    }

    /**
     * Create JSON backup
     */
    private function createJsonBackup($timestamp, $specificTables, $compress)
    {
        $this->info("Creating JSON backup...");

        $tables = $specificTables ? 
            explode(',', $specificTables) : 
            $this->getAllTableNames();

        $backup = [
            'created_at' => now()->toISOString(),
            'database_name' => config('database.connections.sqlite.database'),
            'tables' => []
        ];

        $totalRecords = 0;

        foreach ($tables as $table) {
            $table = trim($table);
            
            if (!$this->tableExists($table)) {
                $this->warn("Table '{$table}' does not exist, skipping...");
                continue;
            }

            $data = DB::table($table)->get()->toArray();
            $backup['tables'][$table] = [
                'count' => count($data),
                'data' => $data
            ];
            
            $totalRecords += count($data);
            $this->info("  ✓ Backed up table: {$table} ({count($data)} records)");
        }

        $backupPath = storage_path("app/backups/database_backup_{$timestamp}.json");
        File::put($backupPath, json_encode($backup, JSON_PRETTY_PRINT));

        if ($compress) {
            $this->compressFile($backupPath);
            $backupPath .= '.gz';
        }

        $size = $this->formatBytes(File::size($backupPath));
        $this->info("✅ JSON backup created: {$backupPath} ({$size})");
        $this->info("Total records backed up: {$totalRecords}");
    }

    /**
     * Create SQLite file backup
     */
    private function createSqliteBackup($timestamp, $compress)
    {
        $dbPath = database_path('database.sqlite');
        $backupPath = storage_path("app/backups/database_backup_{$timestamp}.sqlite");

        if (!File::exists($dbPath)) {
            throw new \Exception("Database file not found: {$dbPath}");
        }

        $this->info("Creating SQLite file backup...");

        File::copy($dbPath, $backupPath);

        if ($compress) {
            $this->compressFile($backupPath);
            $backupPath .= '.gz';
        }

        $size = $this->formatBytes(File::size($backupPath));
        $this->info("✅ SQLite backup created: {$backupPath} ({$size})");
    }

    /**
     * Get all table names from the database
     */
    private function getAllTableNames()
    {
        $tables = DB::select("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");
        return collect($tables)->pluck('name')->toArray();
    }

    /**
     * Check if table exists
     */
    private function tableExists($table)
    {
        try {
            return \Illuminate\Support\Facades\Schema::hasTable($table);
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * Filter SQL dump for specific tables
     */
    private function filterSqlDumpForTables($sqlDump, $tables)
    {
        $lines = explode("\n", $sqlDump);
        $filteredLines = [];
        $currentTable = null;
        $includeCurrentBlock = false;

        foreach ($lines as $line) {
            // Check if this is a CREATE TABLE statement
            if (preg_match('/CREATE TABLE (?:IF NOT EXISTS )?["`]?(\w+)["`]?/', $line, $matches)) {
                $currentTable = $matches[1];
                $includeCurrentBlock = in_array($currentTable, $tables);
            }
            
            // Check if this is an INSERT statement
            if (preg_match('/INSERT INTO ["`]?(\w+)["`]?/', $line, $matches)) {
                $currentTable = $matches[1];
                $includeCurrentBlock = in_array($currentTable, $tables);
            }

            // Include line if we're in a relevant block
            if ($includeCurrentBlock || 
                strpos($line, 'PRAGMA') === 0 || 
                strpos($line, 'BEGIN TRANSACTION') === 0 || 
                strpos($line, 'COMMIT') === 0 ||
                trim($line) === '') {
                $filteredLines[] = $line;
            }
        }

        return implode("\n", $filteredLines);
    }

    /**
     * Compress a file using gzip
     */
    private function compressFile($filePath)
    {
        $this->info("Compressing backup...");
        
        $command = "gzip \"{$filePath}\"";
        shell_exec($command);
    }

    /**
     * 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];
    }
}