<?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)
    {
        // Gunakan metode yang lebih robust untuk ekstrak statement
        $statements = $this->extractSqlStatementsAdvanced($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) {
                $executedStatements = 0;
                $failedStatements = 0;

                // Import setiap statement
                foreach ($filteredStatements as $index => $statement) {
                    try {
                        // Skip statement yang terlalu pendek (mungkin hasil pemisahan yang salah)
                        if (strlen(trim($statement)) < 10) {
                            continue;
                        }

                        // Validasi statement SQL dasar
                        if (!$this->isValidSqlStatement($statement)) {
                            $errors[] = "Statement #{$index} tidak valid: " . substr($statement, 0, 100);
                            $failedStatements++;
                            continue;
                        }

                        DB::unprepared($statement);
                        $executedStatements++;
                    } catch (\Exception $statementException) {
                        $failedStatements++;
                        $errorMsg = "Error pada statement #{$index}: " . $statementException->getMessage();
                        $errors[] = $errorMsg;

                        Log::error("SQL Import Error: " . $statementException->getMessage(), [
                            'statement_index' => $index,
                            'statement_preview' => substr($statement, 0, 200)
                        ]);
                    }
                }

                // Evaluasi hasil
                if ($failedStatements > 0) {
                    $message = 'Import selesai dengan ' . $failedStatements . ' error dari ' . count($filteredStatements) . ' statement';

                    // Jika semua statement gagal, anggap import gagal
                    if ($failedStatements == count($filteredStatements)) {
                        $success = false;
                        $message = 'Semua perintah SQL gagal dieksekusi';
                    } elseif ($executedStatements > 0) {
                        $message .= ', ' . $executedStatements . ' statement berhasil dieksekusi';
                    }
                } else {
                    $message = 'Semua ' . $executedStatements . ' statement berhasil diimport';
                }
            } 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 dengan metode yang lebih advanced
     * untuk menangani string yang mengandung titik koma
     */
    protected function extractSqlStatementsAdvanced($sql)
    {
        $statements = [];
        $currentStatement = '';
        $inString = false;
        $stringChar = '';
        $escaped = false;

        $sql = trim($sql);
        $length = strlen($sql);

        for ($i = 0; $i < $length; $i++) {
            $char = $sql[$i];
            $prevChar = $i > 0 ? $sql[$i - 1] : '';
            $nextChar = $i < ($length - 1) ? $sql[$i + 1] : '';

            // Handle escaping
            if ($escaped) {
                $currentStatement .= $char;
                $escaped = false;
                continue;
            }

            // Handle string literals
            if ($inString) {
                $currentStatement .= $char;

                // Check for escape character
                if ($char === '\\') {
                    $escaped = true;
                    continue;
                }

                // Check for string termination
                if ($char === $stringChar) {
                    $inString = false;
                    $stringChar = '';
                }
                continue;
            }

            // Check for string start
            if ($char === "'" || $char === '"' || $char === '`') {
                $inString = true;
                $stringChar = $char;
                $currentStatement .= $char;
                continue;
            }

            // Check for statement termination
            if ($char === ';') {
                $stmt = trim($currentStatement);
                if (!empty($stmt)) {
                    $statements[] = $stmt . ';';
                }
                $currentStatement = '';
                continue;
            }

            $currentStatement .= $char;
        }

        // Add the last statement if any
        $lastStmt = trim($currentStatement);
        if (!empty($lastStmt)) {
            $statements[] = $lastStmt;
        }

        return array_filter($statements, function ($stmt) {
            return !empty(trim($stmt));
        });
    }

    /**
     * Metode legacy untuk kompatibilitas (bisa dihapus setelah testing)
     */
    protected function extractSqlStatements($sql)
    {
        return $this->extractSqlStatementsAdvanced($sql);
    }

    /**
     * Validasi dasar statement SQL
     */
    protected function isValidSqlStatement($statement)
    {
        $statement = trim($statement);

        // Skip jika terlalu pendek
        if (strlen($statement) < 10) {
            return false;
        }

        // Cek jika statement dimulai dengan keyword SQL yang umum
        $sqlKeywords = [
            'INSERT',
            'UPDATE',
            'DELETE',
            'CREATE',
            'ALTER',
            'DROP',
            'SELECT',
            'SET',
            'BEGIN',
            'COMMIT',
            'ROLLBACK'
        ];

        $upperStatement = strtoupper($statement);
        foreach ($sqlKeywords as $keyword) {
            if (strpos($upperStatement, $keyword) === 0) {
                return true;
            }
        }

        return false;
    }

    /**
     * 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;
                        if (!in_array($tableName, $skippedTables)) {
                            $skippedTables[] = $tableName;
                        }
                        break;
                    } else {
                        if (!in_array($tableName, $importedTables)) {
                            $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);
        }
    }
}
