<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Symfony\Component\Process\Process;
use Illuminate\Support\Facades\Storage;

class DatabaseController extends Controller
{
    // Backup database
    public function backup(Request $request)
    {
        $this->authorize('perform-db-ops');
        
        $filename = 'backup_'.date('Y-m-d_H-i-s').'.sql';
        $path = Storage::path('backups/'.$filename);
        
        $process = Process::fromShellCommandline(sprintf(
            'mysqldump -u%s -p%s %s > %s',
            config('database.connections.mysql.username'),
            config('database.connections.mysql.password'),
            config('database.connections.mysql.database'),
            $path
        ));
        
        $process->run();
        
        if (!$process->isSuccessful()) {
            return response()->json(['error' => 'Backup failed'], 500);
        }
        
        return response()->json([
            'success' => true,
            'path' => $path
        ]);
    }

    // Run safe queries
    public function runQuery(Request $request)
    {
        $this->authorize('perform-db-ops');
        
        $validated = $request->validate([
            'query' => 'required|string|max:1000|not_regex:/drop|alter|truncate|delete|insert|update/i'
        ]);
        
        try {
            $results = DB::select($validated['query']);
            return response()->json($results);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    // List all tables
    public function listTables()
    {
        $this->authorize('perform-db-ops');
        
        $tables = DB::select('SHOW TABLES');
        return response()->json($tables);
    }
}