<?php
// ============================================================
//  MJPOS – BackupController  (pure PHP backup, no mysqldump)
// ============================================================

class BackupController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('backup.manage');

        @mkdir(BACKUP_PATH, 0755, true);
        $files   = glob(BACKUP_PATH . DIRECTORY_SEPARATOR . '*.sql') ?: [];
        rsort($files);
        $backups = array_map(fn($f) => [
            'name' => basename($f),
            'size' => filesize($f),
            'date' => filemtime($f),
        ], $files);

        $totalSize   = array_sum(array_column($backups, 'size'));
        $totalCount  = count($backups);
        $latestDate  = $totalCount > 0 ? $backups[0]['date'] : null;
        $backupPath  = BACKUP_PATH;
        $writable    = is_writable(BACKUP_PATH);

        // DB stats for info panel
        $tableCount  = (int) Database::fetchColumn("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ?", [DB_NAME]);
        $dbSizeKB    = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(data_length + index_length)/1024, 0)
             FROM information_schema.tables WHERE table_schema = ?",
            [DB_NAME]
        );

        $pageTitle  = 'Database Backup';
        $breadcrumb = [['label' => 'Backup']];
        ob_start();
        require APP_PATH . '/views/backup/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function delete(string $file): void
    {
        Auth::requirePermission('backup.manage');
        Auth::requireCsrf();

        $safe = preg_replace('/[^a-zA-Z0-9_\-\.]/', '', $file);
        if (!str_ends_with($safe, '.sql')) {
            redirectWith(APP_URL . '/backup', 'error', 'Invalid file.');
            return;
        }
        $path = BACKUP_PATH . DIRECTORY_SEPARATOR . $safe;
        if (!file_exists($path)) {
            redirectWith(APP_URL . '/backup', 'error', 'File not found.');
            return;
        }
        @unlink($path);
        Auth::logAudit(Auth::id(), 'backup_delete', 'backup', 'Deleted backup: ' . $safe);
        redirectWith(APP_URL . '/backup', 'success', 'Backup deleted: ' . $safe);
    }

    public function create(): void
    {
        Auth::requirePermission('backup.manage');
        Auth::requireCsrf();

        @mkdir(BACKUP_PATH, 0755, true);

        if (!is_writable(BACKUP_PATH)) {
            redirectWith(APP_URL . '/backup', 'error',
                'Backup folder is not writable: ' . BACKUP_PATH);
            return;
        }

        $filename = DB_NAME . '_' . date('Y-m-d_His') . '.sql';
        $filepath = BACKUP_PATH . DIRECTORY_SEPARATOR . $filename;

        try {
            $sql = $this->generateSqlDump();
            file_put_contents($filepath, $sql);

            Auth::logAudit(Auth::id(), 'backup_create', 'backup',
                'Backup created: ' . $filename);
            redirectWith(APP_URL . '/backup', 'success',
                'Backup created: ' . $filename
                . ' (' . number_format(strlen($sql) / 1024, 1) . ' KB)');
        } catch (Exception $e) {
            if (file_exists($filepath)) @unlink($filepath);
            redirectWith(APP_URL . '/backup', 'error',
                'Backup failed: ' . $e->getMessage());
        }
    }

    public function download(string $file): void
    {
        Auth::requirePermission('backup.manage');

        // Strip all non-safe characters — no slashes, no dots except extension dot
        $safe = preg_replace('/[^a-zA-Z0-9_\-\.]/', '', basename($file));
        if (!preg_match('/^[a-zA-Z0-9_\-]+\.sql$/', $safe)) {
            http_response_code(400); die('Invalid filename.');
        }
        $path = BACKUP_PATH . DIRECTORY_SEPARATOR . $safe;

        // Path traversal guard
        if (!Security::safePath($path, BACKUP_PATH)) {
            http_response_code(403); die('Access denied.');
        }
        if (!file_exists($path)) {
            http_response_code(404); die('File not found.');
        }

        // Force download — never execute
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $safe . '"');
        header('Content-Length: ' . filesize($path));
        header('Cache-Control: no-store, no-cache, must-revalidate');
        header('Pragma: no-cache');
        header('X-Content-Type-Options: nosniff');
        readfile($path);
        exit;
    }

    // ── Restore from server backup ───────────────────────────
    public function restore(string $file): void
    {
        Auth::requirePermission('backup.manage');
        Auth::requireCsrf();

        $safe = preg_replace('/[^a-zA-Z0-9_\-\.]/', '', basename($file));
        if (!preg_match('/^[a-zA-Z0-9_\-]+\.sql$/', $safe)) {
            redirectWith(APP_URL . '/backup', 'error', 'Invalid filename.');
            return;
        }
        $path = BACKUP_PATH . DIRECTORY_SEPARATOR . $safe;
        if (!Security::safePath($path, BACKUP_PATH) || !file_exists($path)) {
            redirectWith(APP_URL . '/backup', 'error', 'Backup file not found.');
            return;
        }

        $this->executeSqlFile($path, $safe);
    }

    // ── Restore from uploaded .sql file ─────────────────────
    public function upload(): void
    {
        Auth::requirePermission('backup.manage');
        Auth::requireCsrf();

        if (empty($_FILES['sql_file']['tmp_name'])) {
            redirectWith(APP_URL . '/backup', 'error', 'No file uploaded.');
            return;
        }

        $orig = $_FILES['sql_file']['name'];
        $tmp  = $_FILES['sql_file']['tmp_name'];
        $size = $_FILES['sql_file']['size'];

        // Validate: must be .sql, max 50 MB
        if (!str_ends_with(strtolower($orig), '.sql')) {
            redirectWith(APP_URL . '/backup', 'error', 'Only .sql files are accepted.');
            return;
        }
        if ($size > 50 * 1024 * 1024) {
            redirectWith(APP_URL . '/backup', 'error', 'File too large (max 50 MB).');
            return;
        }
        if (!is_uploaded_file($tmp)) {
            redirectWith(APP_URL . '/backup', 'error', 'Invalid upload.');
            return;
        }

        // Save to backup folder
        @mkdir(BACKUP_PATH, 0755, true);
        $dest = BACKUP_PATH . DIRECTORY_SEPARATOR . 'upload_' . date('Y-m-d_His') . '.sql';
        if (!move_uploaded_file($tmp, $dest)) {
            redirectWith(APP_URL . '/backup', 'error', 'Could not save uploaded file.');
            return;
        }

        $this->executeSqlFile($dest, basename($dest));
    }

    // ── Shared: execute SQL file ─────────────────────────────
    private function executeSqlFile(string $path, string $label): void
    {
        try {
            $sql = file_get_contents($path);
            if ($sql === false || trim($sql) === '') {
                redirectWith(APP_URL . '/backup', 'error', 'Backup file is empty or unreadable.');
                return;
            }

            $pdo = Database::getInstance();
            $pdo->exec('SET FOREIGN_KEY_CHECKS = 0;');

            // Split on semicolons but respect string literals
            $statements = $this->splitSql($sql);
            $executed   = 0;
            $errors     = 0;

            foreach ($statements as $stmt) {
                $stmt = trim($stmt);
                if ($stmt === '' || str_starts_with($stmt, '--') || str_starts_with($stmt, '/*')) {
                    continue;
                }
                try {
                    $pdo->exec($stmt);
                    $executed++;
                } catch (PDOException $e) {
                    $errors++;
                    // Skip minor errors (duplicate key etc) but stop on critical
                    if (str_contains($e->getMessage(), 'syntax error') || $errors > 10) {
                        $pdo->exec('SET FOREIGN_KEY_CHECKS = 1;');
                        redirectWith(APP_URL . '/backup', 'error',
                            "Restore failed at statement #{$executed}: " . $e->getMessage());
                        return;
                    }
                }
            }

            $pdo->exec('SET FOREIGN_KEY_CHECKS = 1;');

            // Refresh settings cache
            try {
                $rows = Database::fetchAll('SELECT setting_key,setting_value FROM settings');
                $_SESSION['settings'] = array_column($rows,'setting_value','setting_key');
            } catch(Exception $e) {}

            Auth::logAudit(Auth::id(), 'backup_restore', 'backup',
                "Restore from: {$label} — {$executed} statements, {$errors} skipped");

            redirectWith(APP_URL . '/backup', 'success',
                "✅ Restore complete from <strong>{$label}</strong> — {$executed} statements executed." .
                ($errors > 0 ? " ({$errors} minor errors skipped)" : ''));

        } catch (Exception $e) {
            try { Database::getInstance()->exec('SET FOREIGN_KEY_CHECKS = 1;'); } catch(Exception $ex){}
            redirectWith(APP_URL . '/backup', 'error',
                'Restore error: ' . $e->getMessage());
        }
    }

    // ── Split SQL into statements ────────────────────────────
    private function splitSql(string $sql): array
    {
        $statements = [];
        $current    = '';
        $inString   = false;
        $strChar    = '';
        $len        = strlen($sql);

        for ($i = 0; $i < $len; $i++) {
            $ch = $sql[$i];

            if ($inString) {
                $current .= $ch;
                if ($ch === $strChar && ($i === 0 || $sql[$i-1] !== '\\')) {
                    $inString = false;
                }
            } elseif ($ch === "'" || $ch === '"' || $ch === '`') {
                $inString = true;
                $strChar  = $ch;
                $current .= $ch;
            } elseif ($ch === '-' && isset($sql[$i+1]) && $sql[$i+1] === '-') {
                // Line comment — skip to end of line
                while ($i < $len && $sql[$i] !== "\n") $i++;
            } elseif ($ch === '/' && isset($sql[$i+1]) && $sql[$i+1] === '*') {
                // Block comment — skip to */
                $i += 2;
                while ($i < $len - 1 && !($sql[$i] === '*' && $sql[$i+1] === '/')) $i++;
                $i++;
            } elseif ($ch === ';') {
                $statements[] = trim($current);
                $current = '';
            } else {
                $current .= $ch;
            }
        }
        if (trim($current) !== '') {
            $statements[] = trim($current);
        }
        return $statements;
    }

    // ── Pure-PHP SQL dump ────────────────────────────────────

    private function generateSqlDump(): string
    {
        $pdo    = Database::getInstance();
        $out    = [];
        $out[]  = '-- ================================================';
        $out[]  = '-- MJPOS Database Backup';
        $out[]  = '-- Database : ' . DB_NAME;
        $out[]  = '-- Generated: ' . date('Y-m-d H:i:s');
        $out[]  = '-- Host     : ' . DB_HOST;
        $out[]  = '-- ================================================';
        $out[]  = '';
        $out[]  = 'SET FOREIGN_KEY_CHECKS = 0;';
        $out[]  = 'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";';
        $out[]  = 'SET time_zone = "+00:00";';
        $out[]  = '';

        // Get all tables
        $tables = $pdo->query('SHOW FULL TABLES WHERE Table_type = "BASE TABLE"')
                      ->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            $out[] = '-- ------------------------------------------------';
            $out[] = '-- Table: `' . $table . '`';
            $out[] = '-- ------------------------------------------------';
            $out[] = '';

            // DROP + CREATE
            $out[] = 'DROP TABLE IF EXISTS `' . $table . '`;';
            $create = $pdo->query('SHOW CREATE TABLE `' . $table . '`')
                          ->fetch(PDO::FETCH_ASSOC);
            $out[] = $create['Create Table'] . ';';
            $out[] = '';

            // Rows
            $rows = $pdo->query('SELECT * FROM `' . $table . '`')
                        ->fetchAll(PDO::FETCH_ASSOC);

            if (empty($rows)) {
                $out[] = '';
                continue;
            }

            // Chunk inserts (500 rows each to keep lines manageable)
            foreach (array_chunk($rows, 500) as $chunk) {
                $cols   = '`' . implode('`, `', array_keys($chunk[0])) . '`';
                $values = [];

                foreach ($chunk as $row) {
                    $escaped = array_map(function ($val) use ($pdo) {
                        if ($val === null) return 'NULL';
                        return $pdo->quote((string) $val);
                    }, array_values($row));
                    $values[] = '(' . implode(', ', $escaped) . ')';
                }

                $out[] = 'INSERT INTO `' . $table . '` (' . $cols . ') VALUES';
                $out[] = implode(",\n", $values) . ';';
                $out[] = '';
            }
        }

        // Views
        $views = $pdo->query('SHOW FULL TABLES WHERE Table_type = "VIEW"')
                     ->fetchAll(PDO::FETCH_COLUMN);

        if (!empty($views)) {
            $out[] = '-- ------------------------------------------------';
            $out[] = '-- Views';
            $out[] = '-- ------------------------------------------------';
            $out[] = '';
            foreach ($views as $view) {
                $out[] = 'DROP VIEW IF EXISTS `' . $view . '`;';
                $create = $pdo->query('SHOW CREATE VIEW `' . $view . '`')
                              ->fetch(PDO::FETCH_ASSOC);
                $out[] = $create['Create View'] . ';';
                $out[] = '';
            }
        }

        $out[] = 'SET FOREIGN_KEY_CHECKS = 1;';
        $out[] = '';
        $out[] = '-- End of backup';

        return implode("\n", $out);
    }
}