<?php
/**
 * SisPen Snesaka - Backup & Restore Database
 */
require_once '../../includes/functions.php';
requireAdmin();

$pageTitle  = 'Backup & Restore';
$activeMenu = 'backup';

$dbHost = DB_HOST ?? 'localhost';
$dbUser = DB_USER ?? 'root';
$dbPass = DB_PASS ?? '';
$dbName = DB_NAME ?? 'sispenra';

// ── BACKUP ──────────────────────────────────────────────────
if (isset($_GET['action']) && $_GET['action'] === 'backup') {
    // Buat SQL dump via PHP (tanpa mysqldump, aman di shared hosting)
    try {
        $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql  = "-- SisPen Snesaka Database Backup\n";
        $sql .= "-- Tanggal : " . date('d/m/Y H:i:s') . "\n";
        $sql .= "-- Database: $dbName\n";
        $sql .= "-- =========================================\n\n";
        $sql .= "SET NAMES utf8mb4;\n";
        $sql .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

        // Ambil semua tabel
        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            // Drop + Create
            $create = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_NUM);
            $sql .= "-- Tabel: $table\n";
            $sql .= "DROP TABLE IF EXISTS `$table`;\n";
            $sql .= $create[1] . ";\n\n";

            // Data
            $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($rows)) {
                $sql .= "INSERT INTO `$table` VALUES\n";
                $vals = array();
                foreach ($rows as $row) {
                    $escaped = array();
                    foreach ($row as $v) {
                        $escaped[] = $v === null ? 'NULL' : "'" . $pdo->quote(strval($v), PDO::PARAM_STR) . "'";
                    }
                    // Hapus double quote dari pdo->quote
                    $vals[] = '(' . implode(',', array_map(function($v) use($pdo){
                        if ($v === 'NULL') return 'NULL';
                        $v = substr($v, 1, -1); // strip outer single quotes
                        return "'" . $v . "'";
                    }, $escaped)) . ')';
                }
                $sql .= implode(",\n", $vals) . ";\n\n";
            }
        }

        $sql .= "SET FOREIGN_KEY_CHECKS=1;\n";

        // Gunakan metode alternatif yang lebih bersih
        ob_clean();
        $filename = 'sispenra_backup_' . date('Ymd_His') . '.sql';
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));
        echo $sql;
        exit;

    } catch (Exception $e) {
        setFlash('danger', 'Backup gagal: ' . $e->getMessage());
        header('Location: index.php');
        exit;
    }
}

// ── BACKUP BERSIH (metode lebih aman) ─────────────────────
if (isset($_GET['action']) && $_GET['action'] === 'backup_clean') {
    try {
        $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $lines   = array();
        $lines[] = "-- SisPen Snesaka | Backup " . date('d/m/Y H:i:s');
        $lines[] = "-- Database: $dbName";
        $lines[] = "SET NAMES 'utf8mb4';";
        $lines[] = "SET FOREIGN_KEY_CHECKS=0;";
        $lines[] = "";

        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

        foreach ($tables as $table) {
            $create = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_NUM);
            $lines[] = "-- --------";
            $lines[] = "DROP TABLE IF EXISTS `$table`;";
            $lines[] = $create[1] . ";";
            $lines[] = "";

            $stmt = $pdo->query("SELECT * FROM `$table`");
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rows as $row) {
                $vals = array();
                foreach ($row as $v) {
                    if ($v === null) {
                        $vals[] = 'NULL';
                    } else {
                        $vals[] = $pdo->quote($v);
                    }
                }
                $lines[] = "INSERT INTO `$table` VALUES (" . implode(', ', $vals) . ");";
            }
            $lines[] = "";
        }

        $lines[] = "SET FOREIGN_KEY_CHECKS=1;";
        $sql      = implode("\n", $lines);
        $filename = 'sispenra_backup_' . date('Ymd_His') . '.sql';

        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));
        header('Pragma: no-cache');
        ob_end_clean();
        echo $sql;
        exit;

    } catch (Exception $e) {
        setFlash('danger', 'Backup gagal: ' . $e->getMessage());
        header('Location: index.php');
        exit;
    }
}

// ── RESTORE ─────────────────────────────────────────────────
$restoreResult = null;
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['sql_file'])) {
    $file = $_FILES['sql_file'];

    if ($file['error'] !== UPLOAD_ERR_OK) {
        setFlash('danger', 'Upload gagal.');
    } elseif (!str_ends_with(strtolower($file['name']), '.sql')) {
        setFlash('danger', 'File harus berformat .sql');
    } else {
        $sql = file_get_contents($file['tmp_name']);

        if (empty(trim($sql))) {
            setFlash('danger', 'File SQL kosong.');
        } else {
            try {
                $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                // Split SQL statements
                $pdo->exec("SET FOREIGN_KEY_CHECKS=0");

                // Jalankan per statement
                $statements = array_filter(
                    array_map('trim', explode(";\n", $sql)),
                    function($s) { return !empty($s) && !str_starts_with(ltrim($s), '--'); }
                );

                $count = 0;
                $errors = array();
                foreach ($statements as $stmt) {
                    if (empty(trim($stmt))) continue;
                    try {
                        $pdo->exec($stmt);
                        $count++;
                    } catch (PDOException $e) {
                        $errors[] = substr($e->getMessage(), 0, 100);
                    }
                }

                $pdo->exec("SET FOREIGN_KEY_CHECKS=1");
                $restoreResult = array('count'=>$count, 'errors'=>$errors);
                setFlash(empty($errors) ? 'success' : 'warning',
                    "Restore selesai: $count statement berhasil" . (count($errors) ? ', ' . count($errors) . ' error.' : '.'));

            } catch (Exception $e) {
                setFlash('danger', 'Restore gagal: ' . $e->getMessage());
            }
        }
    }
    header('Location: index.php');
    exit;
}

// Info database
try {
    $pdo    = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    $dbSize = $pdo->query("
        SELECT ROUND(SUM(data_length + index_length)/1024/1024, 2) as size
        FROM information_schema.tables
        WHERE table_schema = '$dbName'
    ")->fetchColumn();
    $dbInfo = array('tables' => count($tables), 'size' => $dbSize);
} catch (Exception $e) {
    $dbInfo = array('tables' => '?', 'size' => '?');
}

include '../../templates/header.php';
?>

<div style="display:grid;grid-template-columns:1fr 1fr;gap:20px;max-width:1000px;">

  <!-- BACKUP -->
  <div class="card">
    <div class="card-header">
      <span class="card-title">
        <i class="fas fa-download" style="color:var(--success);margin-right:8px;"></i>
        Backup Database
      </span>
    </div>
    <div class="card-body">

      <!-- Info DB -->
      <div style="background:linear-gradient(135deg,var(--primary-dark),var(--primary));color:#fff;border-radius:10px;padding:16px;margin-bottom:20px;display:flex;gap:20px;">
        <div style="text-align:center;flex:1;">
          <div style="font-size:28px;font-weight:800;"><?= $dbInfo['tables'] ?></div>
          <div style="font-size:11px;opacity:.75;">Tabel</div>
        </div>
        <div style="width:1px;background:rgba(255,255,255,.2);"></div>
        <div style="text-align:center;flex:1;">
          <div style="font-size:28px;font-weight:800;"><?= $dbInfo['size'] ?></div>
          <div style="font-size:11px;opacity:.75;">MB Ukuran</div>
        </div>
        <div style="width:1px;background:rgba(255,255,255,.2);"></div>
        <div style="text-align:center;flex:1;">
          <div style="font-size:18px;font-weight:700;"><?= date('d/m/Y') ?></div>
          <div style="font-size:11px;opacity:.75;">Hari ini</div>
        </div>
      </div>

      <p style="font-size:13px;color:var(--text-muted);margin-bottom:16px;">
        Unduh seluruh data database sebagai file <code>.sql</code> untuk backup.
        Simpan di tempat yang aman.
      </p>

      <a href="?action=backup_clean" class="btn btn-primary" style="width:100%;justify-content:center;margin-bottom:10px;">
        <i class="fas fa-database"></i>&nbsp; Backup Sekarang
      </a>

      <div style="background:#f0fdf4;border:1px solid #bbf7d0;border-radius:8px;padding:12px;font-size:12px;color:#15803d;">
        <i class="fas fa-shield-alt"></i>
        <strong>Tips keamanan:</strong> Lakukan backup rutin setiap minggu atau sebelum perubahan data besar.
      </div>
    </div>
  </div>

  <!-- RESTORE -->
  <div class="card">
    <div class="card-header">
      <span class="card-title">
        <i class="fas fa-upload" style="color:var(--warning);margin-right:8px;"></i>
        Restore Database
      </span>
    </div>
    <div class="card-body">

      <div style="background:#fff8e1;border:1px solid #ffe082;border-radius:8px;padding:12px 14px;margin-bottom:16px;font-size:12px;color:#7c5e00;">
        <i class="fas fa-exclamation-triangle"></i>
        <strong>Peringatan:</strong> Restore akan <strong>menimpa semua data saat ini</strong>.
        Pastikan Anda sudah backup terlebih dahulu!
      </div>

      <form method="POST" enctype="multipart/form-data">
        <div class="form-group">
          <label class="form-label">File SQL Backup <span class="required">*</span></label>
          <input type="file" name="sql_file" class="form-control" accept=".sql" required>
          <div style="font-size:11px;color:var(--text-muted);margin-top:4px;">Hanya file <code>.sql</code> hasil backup SisPen Snesaka</div>
        </div>

        <button type="submit"
                class="btn" style="width:100%;justify-content:center;background:#f59e0b;color:#fff;border:none;"
                onclick="return confirm('⚠️ PERINGATAN!\n\nSeluruh data database akan ditimpa dengan data dari file backup.\n\nYakin ingin melanjutkan restore?')">
          <i class="fas fa-undo-alt"></i>&nbsp; Restore Sekarang
        </button>
      </form>

      <div style="margin-top:16px;padding:12px;background:var(--surface-2);border-radius:8px;font-size:12px;color:var(--text-muted);">
        <div style="font-weight:600;margin-bottom:6px;"><i class="fas fa-history"></i> Tabel yang akan di-restore:</div>
        <?php foreach ($tables as $t): ?>
        <span style="display:inline-block;background:var(--border);border-radius:4px;padding:2px 8px;margin:2px;font-family:monospace;"><?= $t ?></span>
        <?php endforeach; ?>
      </div>
    </div>
  </div>

</div>

<?php include '../../templates/footer.php'; ?>
