<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use App\Services\BackupService;

class Database extends BaseController
{
    private function checkAccess()
    {
        if (!session()->get('logged_in')) {
            return redirect()->to('/login');
        }

        $role = session()->get('user_role');
        if (!in_array($role, ['superadmin', 'admin'])) {
            return redirect()->to('/dashboard')->with('error', 'Anda tidak memiliki hak akses.');
        }

        return null;
    }

    // ── HALAMAN UTAMA ──────────────────────────────────────────

    public function index()
    {
        if ($redirect = $this->checkAccess()) return $redirect;

        $db = db_connect();

        // Ambil informasi koneksi
        $config     = config('Database');
        $default    = $config->default;
        $dbName     = $default['database'];
        $hostname   = $default['hostname'];
        $port       = $default['port'] ?? 3306;
        $dbDriver   = $default['DBDriver'] ?? 'MySQLi';
        $charset    = $default['charset'] ?? 'utf8mb4';

        // Versi server
        $serverInfo = $db->query('SELECT VERSION() AS version')->getRowArray();
        $serverVer  = $serverInfo['version'] ?? '—';

        // Collation database
        $collationRow = $db->query("SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?", [$dbName])->getRowArray();
        $collation = $collationRow['DEFAULT_COLLATION_NAME'] ?? $charset;

        // Jumlah tabel
        $tableCount = (int) $db->query("SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?", [$dbName])->getRow()->cnt;

        // Ukuran database
        $sizeRow = $db->query("SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?", [$dbName])->getRow();
        $dbSize = $sizeRow->size_mb ?? 0;

        // Ambil daftar migration
        $migrations = $db->table('migrations')
            ->orderBy('batch', 'ASC')
            ->orderBy('version', 'ASC')
            ->get()
            ->getResultArray();

        $totalMigrations = count($migrations);
        $lastBatch = $totalMigrations > 0 ? max(array_column($migrations, 'batch')) : 0;

        $data = [
            'title'          => 'Database',
            'dbName'         => $dbName,
            'hostname'       => $hostname,
            'port'           => $port,
            'dbDriver'       => $dbDriver,
            'serverVer'      => $serverVer,
            'collation'      => $collation,
            'tableCount'     => $tableCount,
            'dbSize'         => $dbSize,
            'migrations'     => $migrations,
            'total'          => $totalMigrations,
            'lastBatch'      => $lastBatch,
            'backups'        => (new BackupService())->listBackups(),
        ];

        return view('settings/database', $data);
    }

    // ── JALANKAN MIGRASI ───────────────────────────────────────

    public function migrate()
    {
        if (!session()->get('logged_in') || !in_array(session()->get('user_role'), ['superadmin', 'admin'])) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Unauthorized']);
        }

        try {
            $migrations = \Config\Services::migrations();

            if ($migrations->latest()) {
                return $this->response->setJSON([
                    'status'  => 'success',
                    'message' => 'Migrasi database berhasil dijalankan!',
                ]);
            } else {
                return $this->response->setJSON([
                    'status'  => 'success',
                    'message' => 'Database sudah menggunakan versi terbaru, tidak ada migrasi baru.',
                ]);
            }
        } catch (\Throwable $e) {
            return $this->response->setJSON([
                'status'  => 'error',
                'message' => 'Gagal menjalankan migrasi database: ' . $e->getMessage(),
            ]);
        }
    }

    // ── BACKUP DATABASE ────────────────────────────────────────

    public function backup()
    {
        if (!session()->get('logged_in') || !in_array(session()->get('user_role'), ['superadmin', 'admin'])) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Unauthorized']);
        }

        $config  = config('Database');
        $default = $config->default;
        $dbName  = $default['database'];
        $user    = $default['username'];
        $pass    = $default['password'];
        $host    = $default['hostname'];
        $port    = $default['port'] ?? 3306;

        // Coba pakai mysqldump via PATH atau lokasi umum
        $mysqldump = 'mysqldump';
        $found = false;

        if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
            $candidates = [
                'C:\xampp\mysql\bin\mysqldump.exe',
                'C:\xampp82\mysql\bin\mysqldump.exe',
                'C:\wamp64\bin\mysql\mysql*\bin\mysqldump.exe',
            ];
            foreach ($candidates as $candidate) {
                $glob = glob($candidate);
                if (!empty($glob)) {
                    $mysqldump = escapeshellarg($glob[0]);
                    $found = true;
                    break;
                }
            }
        }

        if (!$found) {
            $testCmd = trim(shell_exec("which mysqldump 2>/dev/null"));
            if (!empty($testCmd)) {
                $mysqldump = escapeshellarg($testCmd);
            } else {
                // Fallback: gunakan query PHP langsung
                return $this->backupViaPhp($dbName, $user, $pass, $host, $port);
            }
        }

        $filename = $dbName . '_backup_' . date('Y-m-d_H-i-s') . '.sql';

        // Pipe via gzip kalau ada
        $gzipExists = !empty(trim(shell_exec("which gzip 2>/dev/null")));
        $ext = $gzipExists ? '.gz' : '';

        $tempDir = WRITEPATH . 'temp';
        if (!is_dir($tempDir)) {
            @mkdir($tempDir, 0755, true);
        }
        $outputFile = $tempDir . DIRECTORY_SEPARATOR . $filename . $ext;

        if ($gzipExists) {
            $cmd = "{$mysqldump} --user={$user} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null | gzip > " . escapeshellarg($outputFile);
        } else {
            $cmd = "{$mysqldump} --user={$user} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null > " . escapeshellarg($outputFile);
        }

        if ($pass !== '') {
            $cmd = "{$mysqldump} --user={$user} --password={$pass} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null > " . escapeshellarg($outputFile);
            if ($gzipExists) {
                $cmd = "{$mysqldump} --user={$user} --password={$pass} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null | gzip > " . escapeshellarg($outputFile);
            }
        }

        putenv('MYSQL_PWD=' . $pass);
        $cmdNoPass = "{$mysqldump} --user={$user} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null > " . escapeshellarg($outputFile);
        if ($gzipExists) {
            $cmdNoPass = "{$mysqldump} --user={$user} --host={$host} --port={$port} --routines --triggers --single-transaction {$dbName} 2>/dev/null | gzip > " . escapeshellarg($outputFile);
        }

        exec($cmdNoPass, $output, $exitCode);

        if ($exitCode !== 0 || !file_exists($outputFile) || filesize($outputFile) < 10) {
            // Hapus env var yang mungkin mengganggu
            putenv('MYSQL_PWD');
            // Fallback: backup via PHP
            @unlink($outputFile);
            return $this->backupViaPhp($dbName, $user, $pass, $host, $port);
        }

        putenv('MYSQL_PWD');

        $mimeType = $gzipExists ? 'application/gzip' : 'application/sql';
        $downloadName = $filename . $ext;

        return $this->response
            ->download($outputFile, null)
            ->setContentType($mimeType)
            ->setFileName($downloadName);
    }

    public function fullBackup()
    {
        if ($redirect = $this->checkAccess()) return $redirect;

        try {
            $path = (new BackupService())->createFullBackup();
            return $this->response->download($path, null)
                ->setContentType('application/zip')
                ->setFileName(basename($path));
        } catch (\Throwable $e) {
            log_message('error', 'Full backup error: ' . $e->getMessage());
            return redirect()->to('/settings/database')->with('error', 'Backup lengkap gagal: ' . $e->getMessage());
        }
    }

    public function downloadBackup(string $filename)
    {
        if ($redirect = $this->checkAccess()) return $redirect;

        $path = (new BackupService())->getBackupPath($filename);
        if ($path === null) {
            return redirect()->to('/settings/database')->with('error', 'File backup tidak ditemukan.');
        }

        return $this->response->download($path, null)
            ->setContentType('application/zip')
            ->setFileName(basename($path));
    }

    public function deleteBackup()
    {
        if ($redirect = $this->checkAccess()) return $redirect;

        $filename = (string) $this->request->getPost('filename');
        if ((new BackupService())->deleteBackup($filename)) {
            return $this->response->setJSON(['status' => 'success', 'message' => 'Backup berhasil dihapus.']);
        }

        return $this->response->setJSON(['status' => 'error', 'message' => 'File backup tidak ditemukan atau gagal dihapus.']);
    }

    // ── RESET DATABASE ─────────────────────────────────────────

    public function reset()
    {
        if (!session()->get('logged_in') || session()->get('user_role') !== 'superadmin') {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Unauthorized. Hanya superadmin yang boleh melakukan reset.']);
        }

        $password = $this->request->getPost('password');
        if (empty($password)) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Password admin wajib diisi.']);
        }

        $db = db_connect();

            // Cari superadmin aktif agar reset tidak bergantung pada email bawaan.
            $adminUser = $db->table('users')
                ->where('role', 'superadmin')
                ->where('is_active', 1)
                ->where('deleted_at', null)
                ->orderBy('id', 'ASC')
                ->get()
                ->getRowArray();

            if (!$adminUser) {
                return $this->response->setJSON(['status' => 'error', 'message' => 'Akun superadmin aktif tidak ditemukan. Reset dibatalkan.']);
            }

            if (!password_verify($password, $adminUser['password'] ?? '')) {
                return $this->response->setJSON(['status' => 'error', 'message' => 'Password admin tidak benar.']);
            }

        try {
            // Backup otomatis sebelum reset
            $this->autoBackupBeforeReset();

            // Daftar tabel yang harus dipertahankan
            $protectedTables = [
                'users',
                'migrations',
                'settings',
                'school_settings',
                'student_card_settings',
                'letter_settings',
                'schedule_teacher_settings',
            ];

            // Ambil semua tabel di database
            $tablesResult = $db->query('SHOW TABLES')->getResultArray();
            $key = array_keys($tablesResult[0])[0];

            // Nonaktifkan foreign key check sementara agar TRUNCATE tidak gagal karena constraint
            $db->query('SET FOREIGN_KEY_CHECKS = 0');

            foreach ($tablesResult as $row) {
                $tableName = $row[$key];

                // Lewati tabel yang dilindungi
                if (in_array($tableName, $protectedTables)) {
                    continue;
                }

                // Hapus data tabel
                $db->query("TRUNCATE TABLE `{$tableName}`");
            }

            // Aktifkan kembali foreign key check
            $db->query('SET FOREIGN_KEY_CHECKS = 1');

            // Hapus semua pengguna kecuali superadmin yang dipakai untuk konfirmasi.
            $db->table('users')
                ->where('id !=', (int) $adminUser['id'])
                ->delete();

            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Database berhasil di-reset. Semua data transaksi dan pengguna lain telah dihapus, kecuali akun admin utama. Silakan login kembali.',
            ]);
        } catch (\Throwable $e) {
            log_message('error', 'Database reset error: ' . $e->getMessage());
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Gagal mereset database: ' . $e->getMessage(),
            ]);
        }
    }

    /**
     * Backup otomatis sebelum reset database.
     */
    private function autoBackupBeforeReset(): void
    {
        $config  = config('Database');
        $default = $config->default;
        $dbName  = $default['database'];
        $user    = $default['username'];
        $pass    = $default['password'];
        $host    = $default['hostname'];
        $port    = $default['port'] ?? 3306;

        $sql = "-- SIMAK Database Backup Before Reset\n";
        $sql .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $sql .= "-- Database: {$dbName}\n\n";
        $sql .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
        $sql .= "SET time_zone = \"+00:00\";\n\n";
        $sql .= "START TRANSACTION;\n\n";

        $db = db_connect();
        $tables = $db->query('SHOW TABLES')->getResultArray();
        $key = array_keys($tables[0])[0];

        foreach ($tables as $tbl) {
            $tableName = $tbl[$key];

            $createRow = $db->query("SHOW CREATE TABLE `{$tableName}`")->getRowArray();
            $createSQL = $createRow['Create Table'];

            $sql .= "-- --------------------------------------------------------\n";
            $sql .= "-- Table: {$tableName}\n";
            $sql .= "-- --------------------------------------------------------\n\n";
            $sql .= "DROP TABLE IF EXISTS `{$tableName}`;\n";
            $sql .= $createSQL . ";\n\n";

            $count = (int) $db->table($tableName)->countAllResults();
            if ($count === 0) continue;

            $limit = 500;
            $offset = 0;
            while ($offset < $count) {
                $rows = $db->query("SELECT * FROM `{$tableName}` LIMIT {$limit} OFFSET {$offset}")->getResultArray();
                if (empty($rows)) break;

                $columns = array_keys($rows[0]);
                $colNames = '`' . implode('`, `', $columns) . '`';

                $sql .= "INSERT INTO `{$tableName}` ({$colNames}) VALUES\n";

                $valueLines = [];
                foreach ($rows as $row) {
                    $values = [];
                    foreach ($row as $val) {
                        if ($val === null) {
                            $values[] = 'NULL';
                        } elseif (is_numeric($val) && !str_starts_with((string)$val, '0')) {
                            $values[] = $val;
                        } else {
                            $values[] = "'" . str_replace("'", "\\'", $val) . "'";
                        }
                    }
                    $valueLines[] = '(' . implode(', ', $values) . ')';
                }
                $sql .= implode(",\n", $valueLines) . ";\n\n";

                $offset += $limit;
            }
        }

        $sql .= "COMMIT;\n";

        $backupDir = WRITEPATH . 'backup' . DIRECTORY_SEPARATOR . 'reset';
        if (!is_dir($backupDir)) {
            @mkdir($backupDir, 0755, true);
        }

        $filename = $dbName . '_backup_before_reset_' . date('Y-m-d_H-i-s') . '.sql';
        $filePath = $backupDir . DIRECTORY_SEPARATOR . $filename;

        file_put_contents($filePath, $sql);
    }

    /**
     * Fallback backup — menggunakan query PHP jika mysqldump tidak tersedia.
     */
    private function backupViaPhp(string $dbName, string $user, string $pass, string $host, int $port)
    {
        $db = db_connect();

        // Header SQL
        $sql = "-- SIMAK Database Backup\n";
        $sql .= "-- Generated: " . date('Y-m-d H:i:s') . "\n";
        $sql .= "-- Database: {$dbName}\n\n";
        $sql .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
        $sql .= "SET time_zone = \"+00:00\";\n\n";
        $sql .= "START TRANSACTION;\n\n";

        // Ambil semua tabel
        $tables = $db->query("SHOW TABLES")->getResultArray();
        $key = array_keys($tables[0])[0];

        foreach ($tables as $tbl) {
            $tableName = $tbl[$key];

            // CREATE TABLE
            $createRow = $db->query("SHOW CREATE TABLE `{$tableName}`")->getRowArray();
            $createSQL = $createRow['Create Table'];

            $sql .= "-- --------------------------------------------------------\n";
            $sql .= "-- Table: {$tableName}\n";
            $sql .= "-- --------------------------------------------------------\n\n";
            $sql .= "DROP TABLE IF EXISTS `{$tableName}`;\n";
            $sql .= $createSQL . ";\n\n";

            // INSERT DATA (batch 500 baris per query)
            $count = (int) $db->table($tableName)->countAllResults();
            if ($count === 0) continue;

            $limit = 500;
            $offset = 0;
            while ($offset < $count) {
                $rows = $db->query("SELECT * FROM `{$tableName}` LIMIT {$limit} OFFSET {$offset}")->getResultArray();
                if (empty($rows)) break;

                $columns = array_keys($rows[0]);
                $colNames = '`' . implode('`, `', $columns) . '`';

                $sql .= "INSERT INTO `{$tableName}` ({$colNames}) VALUES\n";

                $valueLines = [];
                foreach ($rows as $row) {
                    $values = [];
                    foreach ($row as $val) {
                        if ($val === null) {
                            $values[] = 'NULL';
                        } elseif (is_numeric($val) && !str_starts_with((string)$val, '0') && strpos((string)$val, '0') !== 0) {
                            $values[] = $val;
                        } else {
                            $values[] = "'" . str_replace("'", "\\'", $val) . "'";
                        }
                    }
                    $valueLines[] = '(' . implode(', ', $values) . ')';
                }
                $sql .= implode(",\n", $valueLines) . ";\n\n";

                $offset += $limit;
            }
        }

        $sql .= "COMMIT;\n";

        $filename = $dbName . '_backup_' . date('Y-m-d_H-i-s') . '.sql';

        return $this->response
            ->download($filename, $sql)
            ->setContentType('application/sql');
    }
}
