<?php
require __DIR__ . '/_common.php';
require_perm('manage_db_tools');

$action = $_GET['action'] ?? 'list';

$backupDir = __DIR__ . '/../../storage/backups';
if (!is_dir($backupDir)) {
  @mkdir($backupDir, 0777, true);
}

if (!function_exists('db_tables')) {
function db_tables(): array {
  $rows = db_all("SHOW FULL TABLES WHERE Table_type='BASE TABLE'");
  $out = [];
  foreach ($rows as $r) {
    $vals = array_values($r);
    if (isset($vals[0]) && $vals[0] !== '') $out[] = (string)$vals[0];
  }
  sort($out);
  return $out;
}
}

if (!function_exists('dump_database_to_file')) {
function dump_database_to_file(string $filePath): array {
  $pdo = db();
  $dbName = app_config('DB_NAME', '');

  $fh = fopen($filePath, 'wb');
  if (!$fh) throw new RuntimeException('تعذّر إنشاء ملف النسخة الاحتياطية.');

  $meta = [
    'tables' => 0,
    'rows' => 0,
    'bytes' => 0,
  ];

  $write = function(string $s) use ($fh, &$meta) {
    $n = fwrite($fh, $s);
    if ($n !== false) $meta['bytes'] += $n;
  };

  $write("-- Oasis Management DB Backup\n");
  $write("-- Database: {$dbName}\n");
  $write("-- Generated at: " . date('Y-m-d H:i:s') . "\n\n");
  $write("SET NAMES utf8mb4;\n");
  $write("SET FOREIGN_KEY_CHECKS=0;\n\n");

  $tables = db_tables();
  foreach ($tables as $t) {
    $meta['tables']++;
    $tEsc = str_replace('`', '``', $t);

    $create = db_one("SHOW CREATE TABLE `{$tEsc}`");
    $createSql = $create['Create Table'] ?? null;
    if (!$createSql) continue;

    $write("-- ----------------------------\n");
    $write("-- Table: `{$t}`\n");
    $write("-- ----------------------------\n");
    $write("DROP TABLE IF EXISTS `{$tEsc}`;\n");
    $write($createSql . ";\n\n");

    // Data
    $st = $pdo->query("SELECT * FROM `{$tEsc}`");
    $cols = [];
    for ($i = 0; $i < $st->columnCount(); $i++) {
      $m = $st->getColumnMeta($i);
      if (!empty($m['name'])) $cols[] = (string)$m['name'];
    }
    $colList = '`' . implode('`,`', array_map(fn($c)=>str_replace('`','``',$c), $cols)) . '`';

    while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
      $vals = [];
      foreach ($cols as $c) {
        $v = $row[$c] ?? null;
        if ($v === null) {
          $vals[] = 'NULL';
        } else {
          $vals[] = $pdo->quote((string)$v);
        }
      }
      $write("INSERT INTO `{$tEsc}` ({$colList}) VALUES (" . implode(',', $vals) . ");\n");
      $meta['rows']++;
    }
    $write("\n");
  }

  $write("SET FOREIGN_KEY_CHECKS=1;\n");
  fclose($fh);
  return $meta;
}
}

if (!function_exists('restore_database_from_sql')) {
function restore_database_from_sql(string $filePath): array {
  $pdo = db();
  $meta = [
    'statements' => 0,
    'errors' => 0,
    'last_error' => null,
  ];

  $pdo->exec("SET FOREIGN_KEY_CHECKS=0");
  $pdo->exec("SET NAMES utf8mb4");

  $fh = fopen($filePath, 'rb');
  if (!$fh) throw new RuntimeException('تعذّر قراءة ملف SQL.');

  $sql = '';
  while (!feof($fh)) {
    $line = fgets($fh);
    if ($line === false) break;
    $trim = ltrim($line);
    // skip comments
    if ($trim === '' || str_starts_with($trim, '--') || str_starts_with($trim, '#')) continue;
    if (str_starts_with($trim, '/*') && str_contains($trim, '*/')) continue;

    $sql .= $line;
    if (preg_match('/;\s*$/', trim($line))) {
      $stmt = trim($sql);
      $sql = '';
      if ($stmt === '') continue;
      try {
        $pdo->exec($stmt);
        $meta['statements']++;
      } catch (Throwable $e) {
        $meta['errors']++;
        $meta['last_error'] = $e->getMessage();
      }
    }
  }
  fclose($fh);

  $pdo->exec("SET FOREIGN_KEY_CHECKS=1");
  return $meta;
}
}

// Download handler
if ($action === 'download') {
  $file = basename((string)($_GET['file'] ?? ''));
  if ($file === '' || !preg_match('/\.sql$/i', $file)) {
    http_response_code(400);
    die('Bad request');
  }
  $path = $backupDir . '/' . $file;
  if (!file_exists($path)) {
    http_response_code(404);
    die('Not found');
  }
  header('Content-Type: application/sql');
  header('Content-Disposition: attachment; filename="' . $file . '"');
  header('Content-Length: ' . filesize($path));
  readfile($path);
  exit;
}

// Delete backup
if ($action === 'delete_backup' && ($_SERVER['REQUEST_METHOD'] === 'POST')) {
  csrf_check();
  $file = basename((string)($_POST['file'] ?? ''));
  $path = $backupDir . '/' . $file;
  if ($file !== '' && preg_match('/\.sql$/i', $file) && file_exists($path)) {
    @unlink($path);
    audit_log('delete', 'db_backup', null, ['file'=>$file], null);
    flash_set('ok', 'تم حذف النسخة الاحتياطية.');
  }
  redirect(url('/index.php?page=db_tools'));
}

// Backup/Restore POST
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  csrf_check();

  if (($action === 'backup')) {
    $name = 'backup_' . date('Ymd_His') . '.sql';
    $path = $backupDir . '/' . $name;
    try {
      $meta = dump_database_to_file($path);
      audit_log('backup', 'db', null, null, ['file'=>$name] + $meta);
      flash_set('ok', 'تم إنشاء نسخة احتياطية بنجاح.');
    } catch (Throwable $e) {
      flash_set('error', 'فشل إنشاء النسخة الاحتياطية: ' . $e->getMessage());
    }
    redirect(url('/index.php?page=db_tools'));
  }

  if (($action === 'restore')) {
    if (empty($_FILES['sql_file']['tmp_name'])) {
      flash_set('error', 'اختر ملف SQL أولاً.');
      redirect(url('/index.php?page=db_tools'));
    }
    $tmp = (string)$_FILES['sql_file']['tmp_name'];
    $orig = (string)($_FILES['sql_file']['name'] ?? 'restore.sql');
    $orig = preg_replace('/[^A-Za-z0-9_\-.]/', '_', $orig);
    if (!preg_match('/\.sql$/i', $orig)) {
      flash_set('error', 'الملف يجب أن يكون بصيغة .sql');
      redirect(url('/index.php?page=db_tools'));
    }
    $dest = $backupDir . '/restore_' . date('Ymd_His') . '_' . $orig;
    @move_uploaded_file($tmp, $dest);
    try {
      $meta = restore_database_from_sql($dest);
      audit_log('restore', 'db', null, null, ['file'=>basename($dest)] + $meta);
      $msg = "تمت الاستعادة. عدد الأوامر: {$meta['statements']}.";
      if ($meta['errors'] > 0) $msg .= " (أخطاء: {$meta['errors']})";
      flash_set('ok', $msg);
    } catch (Throwable $e) {
      flash_set('error', 'فشل الاستعادة: ' . $e->getMessage());
    }
    redirect(url('/index.php?page=db_tools'));
  }
}

// List backups
$files = [];
foreach (glob($backupDir . '/*.sql') ?: [] as $p) {
  $files[] = [
    'file' => basename($p),
    'size' => filesize($p),
    'mtime' => filemtime($p),
  ];
}
usort($files, fn($a,$b)=>($b['mtime']<=>$a['mtime']));

?>

<div class="card">
  <div class="row" style="justify-content:space-between;align-items:center">
    <h3 style="margin:0">نسخ/استعادة قاعدة البيانات</h3>
    <div class="small muted">يفضّل أخذ نسخة احتياطية قبل أي ترقية.</div>
  </div>

  <div class="row" style="gap:12px;flex-wrap:wrap;margin-top:12px">
    <form method="post" action="<?= e(url('/index.php?page=db_tools&action=backup')) ?>">
      <?= csrf_field() ?>
      <button class="btn primary" type="submit">إنشاء نسخة احتياطية الآن</button>
    </form>

    <form method="post" enctype="multipart/form-data" action="<?= e(url('/index.php?page=db_tools&action=restore')) ?>" style="display:flex;gap:8px;align-items:center;flex-wrap:wrap">
      <?= csrf_field() ?>
      <input class="input" type="file" name="sql_file" accept=".sql" required>
      <button class="btn" type="submit" onclick="return confirm('سيتم استبدال البيانات الحالية بالملف المحدد. هل أنت متأكد؟')">استعادة من ملف SQL</button>
      <span class="small muted">(قد يستغرق وقتًا بحسب حجم الملف)</span>
    </form>
  </div>
</div>

<div class="card" style="margin-top:16px">
  <h3 style="margin-top:0">النسخ المتوفرة</h3>
  <?php if (empty($files)): ?>
    <div class="muted">لا توجد نسخ احتياطية بعد.</div>
  <?php else: ?>
    <div class="table-wrap">
      <table class="table">
        <thead>
          <tr>
            <th>الملف</th>
            <th>الحجم</th>
            <th>التاريخ</th>
            <th></th>
          </tr>
        </thead>
        <tbody>
          <?php foreach ($files as $it): ?>
            <tr>
              <td><?= e($it['file']) ?></td>
              <td><?= e(number_format($it['size']/1024, 1)) ?> KB</td>
              <td><?= e(date('Y-m-d H:i', (int)$it['mtime'])) ?></td>
              <td style="text-align:left;white-space:nowrap">
                <a class="btn" href="<?= e(url('/index.php?page=db_tools&action=download&file=' . urlencode($it['file']))) ?>">تنزيل</a>
                <form method="post" action="<?= e(url('/index.php?page=db_tools&action=delete_backup')) ?>" style="display:inline">
                  <?= csrf_field() ?>
                  <input type="hidden" name="file" value="<?= e($it['file']) ?>">
                  <button class="btn danger" type="submit" onclick="return confirm('حذف النسخة؟')">حذف</button>
                </form>
              </td>
            </tr>
          <?php endforeach; ?>
        </tbody>
      </table>
    </div>
  <?php endif; ?>
</div>
