<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;

class DatabaseController extends Controller
{
    /**
     * Daftar tabel yang akan dilindungi/tidak dihapus
     */
    protected $protectedTables = [
        // 'users',
        'role_has_permissions',
        'roles',
        // 'personal_access_tokens',
        'permissions',
        'password_resets',
        // 'model_has_roles',
        'model_has_permissions',
        // 'migrations',
        'licences',
        'failed_jobs',
        // 'menu_setting',
    ];

    // -------- DELETE DATABASE -------- //

    /**
     * Menghapus semua tabel kecuali yang dilindungi
     */
    public function dropAllTablesExcept()
    {
        try {
            // Dapatkan semua tabel dalam database
            $connection = config('database.default');
            $driver = config("database.connections.{$connection}.driver");
            
            // Mengambil semua tabel berdasarkan jenis database
            switch ($driver) {
                case 'mysql':
                    $tables = DB::select('SHOW TABLES');
                    $tables = array_map(function($table) {
                        return array_values((array)$table)[0];
                    }, $tables);
                    break;
                    
                case 'pgsql':
                    $tables = DB::select("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'");
                    $tables = array_map(function($table) {
                        return $table->table_name;
                    }, $tables);
                    break;
                    
                case 'sqlite':
                    $tables = DB::select("SELECT name FROM sqlite_master WHERE type='table'");
                    $tables = array_map(function($table) {
                        return $table->name;
                    }, $tables);
                    break;
                    
                default:
                    return response()->json([
                        'success' => false,
                        'message' => "Driver database tidak didukung: {$driver}"
                    ], 400);
            }
            
            // Filter tabel-tabel yang akan dihapus (yang tidak ada dalam daftar pengecualian)
            $tablesToDrop = array_filter($tables, function($table) {
                return !in_array($table, $this->protectedTables);
            });
            
            $results = [];
            $success = true;
            
            // Nonaktifkan foreign key checks jika menggunakan MySQL
            if ($driver === 'mysql') {
                DB::statement('SET FOREIGN_KEY_CHECKS=0');
            }
            
            // Hapus tabel-tabel yang dipilih
            foreach ($tablesToDrop as $tableName) {
                try {
                    Schema::dropIfExists($tableName);
                    $results[$tableName] = [
                        'status' => 'success',
                        'message' => "Tabel '{$tableName}' berhasil dihapus."
                    ];
                } catch (\Exception $e) {
                    $success = false;
                    $results[$tableName] = [
                        'status' => 'error',
                        'message' => "Gagal menghapus tabel: " . $e->getMessage()
                    ];
                }
            }
            
            // Aktifkan kembali foreign key checks jika menggunakan MySQL
            if ($driver === 'mysql') {
                DB::statement('SET FOREIGN_KEY_CHECKS=1');
            }
            
            return response()->json([
                'success' => $success,
                'protected_tables' => $this->protectedTables,
                'dropped_tables' => count($tablesToDrop),
                'results' => $results
            ], $success ? 200 : 207);
            
        } catch (\Exception $e) {
            // Pastikan foreign key checks diaktifkan kembali jika terjadi error
            if (config("database.connections.{$connection}.driver") === 'mysql') {
                DB::statement('SET FOREIGN_KEY_CHECKS=1');
            }
            
            return response()->json([
                'success' => false,
                'message' => "Terjadi kesalahan: " . $e->getMessage()
            ], 500);
        }
    }


    // -------- IMPORT DATABASE -------- //

    /**
     * Import database dari file SQL dengan melewati tabel yang dilindungi
     */
    public function importSql(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'sql_file' => 'required|file|mimes:sql,txt|max:51200',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'Validasi gagal',
                'errors' => $validator->errors()
            ], 422);
        }

        try {
            $file = $request->file('sql_file');
            $path = $file->getRealPath();
            $sql = file_get_contents($path);

            // Panggil method untuk memproses dan filter SQL
            $result = $this->processAndImportSql($sql);

            return response()->json([
                'success' => $result['success'],
                'message' => $result['message'],
                'file_name' => $file->getClientOriginalName(),
                'skipped_tables' => $result['skipped_tables'],
                'imported_tables' => $result['imported_tables'],
                'errors' => $result['errors'] ?? []
            ], $result['success'] ? 200 : 500);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => "Terjadi kesalahan: " . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Proses dan filter SQL sebelum import
     */
    protected function processAndImportSql($sql)
    {
        $statements = $this->extractSqlStatements($sql);
        list($filteredStatements, $skippedTables, $importedTables) = $this->filterSqlStatements($statements);

        $connection = config('database.default');
        $driver = config("database.connections.{$connection}.driver");

        if ($driver === 'mysql') {
            DB::statement('SET FOREIGN_KEY_CHECKS=0');
        }

        $success = true;
        $errors = [];
        $message = 'Database berhasil diimport';

        try {
            if (count($filteredStatements) > 0) {
                // Import setiap statement tanpa menggunakan transaksi
                foreach ($filteredStatements as $statement) {
                    try {
                        DB::unprepared($statement);
                    } catch (\Exception $statementException) {
                        // Log error tapi lanjutkan ke statement berikutnya
                        $errors[] = "Error pada statement: " . substr($statement, 0, 100) . "... : " . $statementException->getMessage();
                        Log::error("SQL Import Error: " . $statementException->getMessage(), [
                            'statement' => substr($statement, 0, 500)
                        ]);
                    }
                }
                
                // Jika ada error, tambahkan ke pesan
                if (count($errors) > 0) {
                    $message = 'Import selesai dengan ' . count($errors) . ' error';
                    // Jika semua statement gagal, anggap import gagal
                    if (count($errors) == count($filteredStatements)) {
                        $success = false;
                        $message = 'Semua perintah SQL gagal dieksekusi';
                    }
                }
            } else {
                $success = false;
                $message = 'Tidak ada perintah SQL yang bisa dijalankan.';
            }
        } catch (\Exception $e) {
            $success = false;
            $errors[] = $e->getMessage();
            $message = 'Gagal mengimport database: ' . $e->getMessage();
        }

        if ($driver === 'mysql') {
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        }

        return [
            'success' => $success,
            'message' => $message,
            'skipped_tables' => $skippedTables,
            'imported_tables' => $importedTables,
            'errors' => $errors
        ];
    }

    /**
     * Ekstrak pernyataan SQL dari string SQL lengkap
     */
    protected function extractSqlStatements($sql)
    {
        // Pisahkan pernyataan SQL berdasarkan titik koma
        $rawStatements = explode(';', $sql);
        
        // Bersihkan dan filter pernyataan kosong
        return array_filter(array_map('trim', $rawStatements), function($statement) {
            return !empty($statement);
        });
    }

    /**
     * Filter pernyataan SQL untuk melewati tabel yang dilindungi
     */
    protected function filterSqlStatements($statements)
    {
        $filteredStatements = [];
        $skippedTables = [];
        $importedTables = [];

        foreach ($statements as $statement) {
            $skipStatement = false;

            $patterns = [
                '/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/ALTER\s+TABLE\s+[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/INSERT\s+INTO\s+[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/UPDATE\s+[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/DELETE\s+FROM\s+[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
                '/TRUNCATE\s+TABLE\s+[`\'"]?([a-zA-Z0-9_]+)[`\'"]?/i',
            ];

            foreach ($patterns as $pattern) {
                if (preg_match($pattern, $statement, $matches)) {
                    $tableName = strtolower($matches[1]);

                    if (in_array($tableName, $this->protectedTables)) {
                        $skipStatement = true;
                        $skippedTables[] = $tableName;
                        break;
                    } else {
                        $importedTables[] = $tableName;
                    }
                }
            }

            if (!$skipStatement) {
                $filteredStatements[] = $statement;
            }
        }

        // Hapus duplikat
        $skippedTables = array_unique($skippedTables);
        $importedTables = array_unique($importedTables);

        return [$filteredStatements, $skippedTables, $importedTables];
    }

    public function executeDatabaseOperations(Request $request)
    {
        try {
            // Jalankan fungsi untuk menghapus semua tabel kecuali yang dilindungi
            $dropResult = $this->dropAllTablesExcept();
            
            // Periksa apakah drop table berhasil
            if (!$dropResult->getData()->success) {
                return response()->json([
                    'success' => false,
                    'message' => 'Gagal menghapus tabel: ' . $dropResult->getData()->message
                ], 500);
            }

            // Jalankan fungsi untuk mengimpor SQL
            $result = $this->importSql($request);
            $resultData = $result->getData(true); // true untuk mendapat array, bukan object
    
            return response()->json([
                'success' => $resultData['success'],
                'message' => $resultData['message'],
                'file_name' => $resultData['file_name'],
                'skipped_tables' => $resultData['skipped_tables'],
                'imported_tables' => $resultData['imported_tables'],
                'errors' => $resultData['errors']
            ], $resultData['success'] ? 200 : 500);
    
        } catch (\Exception $e) {
            Log::error('Database Operation Error: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => "Terjadi kesalahan: " . $e->getMessage()
            ], 500);
        }
    }
}
