<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use ZipArchive;

class BackupController extends Controller
{
    public function run(Request $request): JsonResponse
    {
        $backupDir = storage_path('app/backups');
        if (!File::exists($backupDir)) {
            File::makeDirectory($backupDir, 0755, true);
        }

        // Delete existing backup
        foreach (File::files($backupDir) as $file) {
            File::delete($file);
        }

        $timestamp = now()->format('Y-m-d_H-i-s');
        $zipPath = $backupDir . '/backup-' . $timestamp . '.zip';
        $sqlPath = $backupDir . '/database.sql';

        try {
            // Create database dump
            $this->exportDatabase($sqlPath);

            $zip = new ZipArchive();
            if ($zip->open($zipPath, ZipArchive::CREATE) !== true) {
                return response()->json(['message' => 'Could not create zip file.'], 500);
            }

            // Add SQL dump
            if (File::exists($sqlPath)) {
                $zip->addFile($sqlPath, 'database.sql');
            }

            // Add receipt files
            $receiptsPath = storage_path('app/public/receipts');
            if (File::exists($receiptsPath)) {
                $files = File::files($receiptsPath);
                foreach ($files as $file) {
                    $zip->addFile($file->getPathname(), 'receipts/' . $file->getFilename());
                }
            }

            $zip->close();

            // Clean up SQL file
            if (File::exists($sqlPath)) {
                File::delete($sqlPath);
            }

            // Keep only latest - rename to latest-backup.zip
            $latestPath = $backupDir . '/latest-backup.zip';
            if (File::exists($latestPath)) {
                File::delete($latestPath);
            }
            File::move($zipPath, $latestPath);

            return response()->json([
                'message' => 'Backup created successfully.',
                'size' => File::size($latestPath),
                'date' => now()->toDateTimeString(),
            ]);
        } catch (\Exception $e) {
            if (File::exists($sqlPath)) File::delete($sqlPath);
            if (isset($zipPath) && File::exists($zipPath)) File::delete($zipPath);
            return response()->json(['message' => 'Backup failed: ' . $e->getMessage()], 500);
        }
    }

    public function status(Request $request): JsonResponse
    {
        $backupDir = storage_path('app/backups');
        $latestPath = $backupDir . '/latest-backup.zip';

        if (File::exists($latestPath)) {
            return response()->json([
                'exists' => true,
                'size' => File::size($latestPath),
                'date' => date('Y-m-d H:i:s', File::lastModified($latestPath)),
            ]);
        }

        return response()->json(['exists' => false]);
    }

    public function download(Request $request)
    {
        if (!$request->user()->is_admin) {
            return response()->json(['message' => 'Only admins can download backups.'], 403);
        }

        $latestPath = storage_path('app/backups/latest-backup.zip');

        if (!File::exists($latestPath)) {
            return response()->json(['message' => 'No backup found.'], 404);
        }

        return response()->download($latestPath, 'backup-' . now()->format('Y-m-d') . '.zip');
    }

    private function exportDatabase(string $outputPath): void
    {
        $host = config('database.connections.mysql.host');
        $port = config('database.connections.mysql.port');
        $database = config('database.connections.mysql.database');
        $username = config('database.connections.mysql.username');
        $password = config('database.connections.mysql.password');

        // Try mysqldump first, fallback to PHP-based export
        $mysqldumpPath = $this->findMysqldump();
        if ($mysqldumpPath) {
            $command = sprintf(
                '"%s" --host=%s --port=%s --user=%s --password=%s --routines --skip-lock-tables %s > "%s" 2>&1',
                $mysqldumpPath,
                escapeshellarg($host),
                escapeshellarg($port),
                escapeshellarg($username),
                escapeshellarg($password),
                escapeshellarg($database),
                $outputPath
            );
            exec($command, $output, $returnCode);
            if ($returnCode === 0 && File::exists($outputPath) && File::size($outputPath) > 0) {
                return;
            }
        }

        // Fallback: PHP-based export
        $this->phpExportDatabase($outputPath);
    }

    private function findMysqldump(): ?string
    {
        $paths = ['mysqldump', 'C:\\xampp\\mysql\\bin\\mysqldump.exe', '/usr/bin/mysqldump', '/usr/local/bin/mysqldump'];
        foreach ($paths as $path) {
            if (is_executable($path) || (PHP_OS_FAMILY === 'Windows' && file_exists($path))) {
                return $path;
            }
        }

        // Try to find via `where` or `which`
        if (PHP_OS_FAMILY === 'Windows') {
            $output = [];
            exec('where mysqldump 2>nul', $output, $code);
            if ($code === 0 && !empty($output[0])) {
                return $output[0];
            }
        } else {
            $output = [];
            exec('which mysqldump 2>/dev/null', $output, $code);
            if ($code === 0 && !empty($output[0])) {
                return $output[0];
            }
        }

        return null;
    }

    private function phpExportDatabase(string $outputPath): void
    {
        $host = config('database.connections.mysql.host');
        $port = config('database.connections.mysql.port');
        $database = config('database.connections.mysql.database');
        $username = config('database.connections.mysql.username');
        $password = config('database.connections.mysql.password');

        try {
            $pdo = new \PDO("mysql:host={$host};port={$port};dbname={$database};charset=utf8mb4", $username, $password);
            $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

            $sql = "-- House Expense Tracker Database Backup\n";
            $sql .= "-- Generated: " . now()->toDateTimeString() . "\n\n";
            $sql .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

            // Get all tables
            $tables = $pdo->query("SHOW TABLES")->fetchAll(\PDO::FETCH_COLUMN);

            foreach ($tables as $table) {
                // Create table structure
                $createStmt = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch();
                if ($createStmt) {
                    $sql .= "\nDROP TABLE IF EXISTS `{$table}`;\n";
                    $sql .= $createStmt[1] . ";\n\n";
                }

                // Get data
                $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(\PDO::FETCH_ASSOC);
                if (!empty($rows)) {
                    $columns = array_keys($rows[0]);
                    $columnList = '`' . implode('`, `', $columns) . '`';

                    foreach ($rows as $row) {
                        $values = array_map(function ($value) use ($pdo) {
                            if ($value === null) return 'NULL';
                            return $pdo->quote($value);
                        }, array_values($row));
                        $sql .= "INSERT INTO `{$table}` ({$columnList}) VALUES (" . implode(', ', $values) . ");\n";
                    }
                    $sql .= "\n";
                }
            }

            $sql .= "SET FOREIGN_KEY_CHECKS=1;\n";
            File::put($outputPath, $sql);
        } catch (\Exception $e) {
            throw new \Exception('Database export failed: ' . $e->getMessage());
        }
    }
}
