<?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',
        'licences_company_type',
        '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)
    {
        return $this->restoreDatabaseStream($request);
    }

    /**
     * Restore database dengan metode streaming untuk efisiensi memori dan stabilitas
     */
    public function restoreDatabaseStream(Request $request)
    {
        // Tingkatkan limit waktu dan memori untuk file besar
        set_time_limit(0);
        ini_set('memory_limit', '-1');

        $validator = Validator::make($request->all(), [
            'sql_file' => 'required|file|mimes:sql,txt',
        ]);

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

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

        // Disable transaction for DDL operations in MySQL as they cause implicit commit
        // DB::beginTransaction();

        try {
            // 1. Nonaktifkan Foreign Key Checks
            if ($driver === 'mysql') {
                DB::statement('SET FOREIGN_KEY_CHECKS=0');
            }

            // 2. Hapus Tabel (Kecuali yang dilindungi)
            $this->dropUnprotectedTablesInternal($driver);

            // 3. Proses File SQL secara Streaming
            $file = $request->file('sql_file');
            $path = $file->getRealPath();

            $handle = fopen($path, "r");
            if (!$handle) {
                throw new \Exception("Gagal membuka file backup");
            }

            $containsLicencesCompanyType = false;
            while (($scanLine = fgets($handle)) !== false) {
                if (stripos($scanLine, 'licences_company_type') !== false) {
                    $containsLicencesCompanyType = true;
                    break;
                }
            }
            rewind($handle);
            if ($containsLicencesCompanyType) {
                Log::info('Restore SQL file contains licences_company_type', [
                    'file_name' => $file->getClientOriginalName(),
                ]);

                try {
                    $fullSql = File::get($path);
                    $allStatements = $this->extractSqlStatementsAdvanced($fullSql);

                    $tableStatements = [];
                    foreach ($allStatements as $stmt) {
                        $s = trim($stmt);
                        if (
                            preg_match('/^\s*CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?[`\'"]?licences_company_type[`\'"]?\b/i', $s) ||
                            preg_match('/^\s*INSERT\s+INTO\s+[`\'"]?licences_company_type[`\'"]?\b/i', $s) ||
                            preg_match('/^\s*ALTER\s+TABLE\s+[`\'"]?licences_company_type[`\'"]?\b/i', $s) ||
                            preg_match('/^\s*DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?[`\'"]?licences_company_type[`\'"]?\b/i', $s) ||
                            preg_match('/^\s*TRUNCATE\s+TABLE\s+[`\'"]?licences_company_type[`\'"]?\b/i', $s)
                        ) {
                            $tableStatements[] = $stmt;
                        }
                    }

                    if (count($tableStatements) > 0) {
                        Schema::dropIfExists('licences_company_type');

                        foreach ($tableStatements as $ts) {
                            try {
                                DB::unprepared($ts);
                            } catch (\Exception $e) {
                                Log::warning('Import licences_company_type error: ' . $e->getMessage(), [
                                    'statement_preview' => substr($ts, 0, 150),
                                ]);
                            }
                        }

                        $typeVal = DB::table('licences_company_type')->orderBy('id', 'desc')->value('type');
                        if ($typeVal !== null && Schema::hasColumn('licences', 'company_type')) {
                            DB::table('licences')->update(['company_type' => $typeVal]);
                            Log::info('Licences company_type updated from licences_company_type', [
                                'type' => $typeVal,
                            ]);
                        }
                    } else {
                        Log::info('Restore SQL file has no executable statements for licences_company_type', [
                            'file_name' => $file->getClientOriginalName(),
                        ]);
                    }
                } catch (\Exception $e) {
                    Log::error('Failed processing licences_company_type from backup: ' . $e->getMessage());
                }
            }

            $successCount = 0;
            $skipCount = 0;
            $errorCount = 0;
            $currentQuery = '';

            // Loop membaca file baris per baris
            while (($line = fgets($handle)) !== false) {
                $trimmedLine = trim($line);

                // Skip baris kosong atau komentar
                if (empty($trimmedLine) || strpos($trimmedLine, '--') === 0 || strpos($trimmedLine, '/*') === 0) {
                    continue;
                }

                $currentQuery .= $line;

                // Cek apakah query sudah berakhir (diakhiri dengan ;)
                // Kita gunakan trim pada currentQuery untuk pengecekan akhir
                if (substr(trim($currentQuery), -1) === ';') {
                    // Cek apakah query melibatkan tabel yang dilindungi
                    if ($this->isQueryReferencingProtectedTable($currentQuery)) {
                        $skipCount++;
                    } else {
                        try {
                            DB::unprepared($currentQuery);
                            $successCount++;
                        } catch (\Exception $e) {
                            // Abaikan error "Table already exists" jika itu CREATE TABLE untuk tabel protected (safety net)
                            // Atau log error lainnya
                            $errorCount++;
                            Log::warning("Restore SQL Error: " . $e->getMessage() . " | Query: " . substr($currentQuery, 0, 100));
                        }
                    }

                    // Reset query buffer
                    $currentQuery = '';
                }
            }

            fclose($handle);

            // 4. Aktifkan kembali Foreign Key Checks
            if ($driver === 'mysql') {
                DB::statement('SET FOREIGN_KEY_CHECKS=1');
            }

            // DB::commit();

            return response()->json([
                'success' => true,
                'message' => "Restore selesai. Berhasil: $successCount, Dilewati: $skipCount, Gagal: $errorCount",
                'details' => [
                    'executed' => $successCount,
                    'skipped' => $skipCount,
                    'failed' => $errorCount,
                ]
            ]);
        } catch (\Exception $e) {
            DB::rollBack();

            // Pastikan FK check kembali aktif jika error
            if ($driver === 'mysql') {
                try {
                    DB::statement('SET FOREIGN_KEY_CHECKS=1');
                } catch (\Exception $ex) {
                }
            }

            if (isset($handle) && is_resource($handle)) {
                fclose($handle);
            }

            Log::error("Database Restore Critical Error: " . $e->getMessage());

            return response()->json([
                'success' => false,
                'message' => 'Terjadi kesalahan fatal saat restore: ' . $e->getMessage()
            ], 500);
        }
    }

    public function checkProtectedTablesStructure()
    {
        $expectedTables = [
            'akuns' => 16,
            'akun_role' => 5,
            'attachment' => 6,
            'cashtransactions' => 28,
            'contacts' => 18,
            'contacttypes' => 7,
            'contact_points' => 6,
            'corporate_income_taxes' => 9,
            'credits' => 10,
            'credit_cashtransactions' => 10,
            'currencies' => 8,
            'currency_settings' => 8,
            'error_logs' => 5,
            'failed_jobs' => 7,
            'invoice_setting' => 13,
            'languages' => 6,
            'licences' => 10,
            'licences_company_type' => 4,
            'media' => 4,
            'menu_setting' => 5,
            'migrations' => 3,
            'model_has_permissions' => 3,
            'model_has_roles' => 3,
            'multi_units' => 10,
            'password_resets' => 3,
            'permissions' => 5,
            'personal_access_tokens' => 9,
            'point_transactions' => 10,
            'priceproducts' => 8,
            'productions' => 11,
            'production_costs' => 8,
            'production_cost_settings' => 6,
            'production_recipes' => 9,
            'products' => 25,
            'products_wholesale' => 8,
            'producttypes' => 4,
            'product_racks' => 5,
            'roles' => 5,
            'role_has_permissions' => 2,
            'setting_apps' => 5,
            'setting_points' => 7,
            'signatures' => 6,
            'stocktransactions' => 36,
            'stocktransactions_so' => 7,
            'subcashtransactions' => 7,
            'substockopnames' => 9,
            'substocktransactions' => 22,
            'suppliers' => 7,
            'tax_fiscal_corrections' => 9,
            'tax_names' => 6,
            'tax_setting_main' => 13,
            'tax_types' => 13,
            'units' => 5,
            'users' => 12,
        ];

        $results = [];
        $allOk = true;

        foreach ($expectedTables as $table => $expectedCount) {
            if (!Schema::hasTable($table)) {
                $allOk = false;
                $results[$table] = [
                    'status' => 'missing',
                    'expected_columns' => $expectedCount,
                    'actual_columns' => 0,
                    'columns' => [],
                ];
                continue;
            }

            $columns = Schema::getColumnListing($table);
            $actualCount = count($columns);
            $status = $actualCount === $expectedCount ? 'ok' : 'column_mismatch';

            if ($status !== 'ok') {
                $allOk = false;
            }

            $results[$table] = [
                'status' => $status,
                'expected_columns' => $expectedCount,
                'actual_columns' => $actualCount,
                'columns' => $columns,
            ];
        }

        return response()->json([
            'success' => true,
            'status' => $allOk,
            // 'tables' => $results,
        ]);
    }

    /**
     * Helper untuk menghapus tabel yang tidak dilindungi
     */
    protected function dropUnprotectedTablesInternal($driver)
    {
        $tables = [];

        if ($driver === 'mysql') {
            $allTables = DB::select('SHOW TABLES');
            foreach ($allTables as $table) {
                $tables[] = array_values((array)$table)[0];
            }
        } elseif ($driver === 'pgsql') {
            $allTables = DB::select("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'");
            foreach ($allTables as $table) {
                $tables[] = $table->table_name;
            }
        } else {
            // Fallback untuk SQLite atau lainnya jika diperlukan
            $allTables = DB::select("SELECT name FROM sqlite_master WHERE type='table'");
            foreach ($allTables as $table) {
                $tables[] = $table->name;
            }
        }

        foreach ($tables as $tableName) {
            if (!in_array($tableName, $this->protectedTables)) {
                Schema::dropIfExists($tableName);
            }
        }
    }

    /**
     * Cek apakah query SQL mencoba memodifikasi tabel yang dilindungi
     */
    protected function isQueryReferencingProtectedTable($query)
    {
        foreach ($this->protectedTables as $table) {
            // Regex patterns untuk mendeteksi operasi pada tabel
            // Menangani quote (` atau ' atau ") dan spasi
            $patterns = [
                '/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/INSERT\s+INTO\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/UPDATE\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/DELETE\s+FROM\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/ALTER\s+TABLE\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/TRUNCATE\s+TABLE\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
                '/LOCK\s+TABLES\s+[`\'"]?' . preg_quote($table, '/') . '[`\'"]?/i',
            ];

            foreach ($patterns as $pattern) {
                if (preg_match($pattern, $query)) {
                    return true;
                }
            }
        }
        return false;
    }
}
