<?php
/**
 * Savit Platform — Data Migration Tool
 * Export your full database + config as a portable backup.
 * Import it on any new XAMPP machine.
 *
 * Usage:
 *   Export: http://localhost/Savit/migrate.php
 *   Import: http://localhost/Savit/migrate.php?action=import  (POST form)
 */
declare(strict_types=1);

define('SAVIT_ROOT', __DIR__);
require_once __DIR__ . '/Common/bootstrap.php';

header('Content-Type: text/html; charset=UTF-8');

// ── DB connection (no DB selected yet for export) ────────────────────────────
function getPdo(bool $withDb = true): PDO {
    $cfg = savit_db_config();
    $dsn = $withDb
        ? "mysql:host={$cfg['host']};port={$cfg['port']};dbname={$cfg['dbname']};charset={$cfg['charset']}"
        : "mysql:host={$cfg['host']};port={$cfg['port']};charset={$cfg['charset']}";
    return new PDO($dsn, $cfg['username'], $cfg['password'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
}

// ── EXPORT ACTION ─────────────────────────────────────────────────────────────
if (($_GET['action'] ?? '') === 'export') {
    $cfg    = savit_db_config();
    $dbName = $cfg['dbname'];
    $pdo    = getPdo(true);

    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    $sql    = "-- Savit Platform Database Export\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 FOREIGN_KEY_CHECKS = 0;\n\n";
    $sql   .= "CREATE DATABASE IF NOT EXISTS `{$dbName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n";
    $sql   .= "USE `{$dbName}`;\n\n";

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

        // Table data
        $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
        if (!empty($rows)) {
            $cols  = '`' . implode('`, `', array_keys($rows[0])) . '`';
            $sql  .= "INSERT INTO `{$table}` ({$cols}) VALUES\n";
            $chunks = [];
            foreach ($rows as $row) {
                $vals = array_map(fn($v) => $v === null ? 'NULL' : $pdo->quote((string)$v), $row);
                $chunks[] = '(' . implode(', ', $vals) . ')';
            }
            $sql .= implode(",\n", $chunks) . ";\n\n";
        }
    }
    $sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";

    $filename = 'savit_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;
}

// ── IMPORT ACTION (process uploaded .sql file) ────────────────────────────────
if (($_SERVER['REQUEST_METHOD'] ?? '') === 'POST' && isset($_FILES['sql_file'])) {
    $errors = [];
    $ok     = [];

    if ($_FILES['sql_file']['error'] !== UPLOAD_ERR_OK) {
        $errors[] = 'Upload failed (error code: ' . $_FILES['sql_file']['error'] . ')';
    } else {
        $sql  = file_get_contents($_FILES['sql_file']['tmp_name']);
        $pdo  = getPdo(false); // connect without selecting DB first

        $stmts = preg_split('/;\s*\n/m', str_replace(["\r\n", "\r"], "\n", $sql));
        $count = 0;
        foreach ($stmts as $stmt) {
            $stmt = trim($stmt);
            if ($stmt === '' || str_starts_with($stmt, '--')) continue;
            try {
                $pdo->exec($stmt);
                $count++;
            } catch (PDOException $e) {
                $msg = $e->getMessage();
                // Skip harmless duplicate warnings
                if (!str_contains($msg, 'Duplicate entry') && !str_contains($msg, 'already exists')) {
                    $errors[] = htmlspecialchars(substr($stmt, 0, 80)) . '…  → ' . htmlspecialchars($msg);
                }
            }
        }
        $ok[] = "Executed {$count} SQL statement(s) successfully.";
    }

    // Copy uploaded .env if provided
    if (!empty($_FILES['env_file']['tmp_name'])) {
        $target = SAVIT_ROOT . '/.env';
        if (move_uploaded_file($_FILES['env_file']['tmp_name'], $target)) {
            $ok[] = '.env file restored successfully.';
        } else {
            $errors[] = 'Could not write .env file — check folder permissions.';
        }
    }

    $importResult = ['ok' => $ok, 'errors' => $errors];
}

$cfg    = savit_db_config();
$dbName = $cfg['dbname'];
$tables = [];
$tableStats = [];
try {
    $pdo = getPdo(true);
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $t) {
        $cnt = $pdo->query("SELECT COUNT(*) FROM `{$t}`")->fetchColumn();
        $tableStats[$t] = (int)$cnt;
    }
    $dbOk = true;
} catch (PDOException $e) {
    $dbOk = false;
    $dbError = $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Savit — Data Migration Tool</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css">
  <style>
    body { background:#0f172a; color:#e2e8f0; font-family:'Inter',system-ui,sans-serif; }

    /* ── Bootstrap dark-theme overrides ─────────────────────────────────── */
    .text-muted  { color:#94a3b8 !important; }
    .text-light  { color:#e2e8f0 !important; }
    label, .form-label, .form-label-sm, small { color:#94a3b8; }

    /* Form controls */
    .form-control {
      background:#0f172a !important;
      color:#e2e8f0 !important;
      border-color:#334155 !important;
    }
    .form-control::placeholder { color:#475569 !important; }
    .form-control:focus {
      background:#0f172a !important;
      color:#e2e8f0 !important;
      border-color:#6366f1 !important;
      box-shadow:0 0 0 3px rgba(99,102,241,.2) !important;
    }

    /* Alerts — keep Bootstrap background colors but ensure text is dark (they have light bg) */
    .alert { border-radius:10px; }
    .alert-success { color:#166534; }
    .alert-danger  { color:#991b1b; }
    .alert-warning { color:#92400e; }

    /* Buttons */
    .btn-outline-secondary {
      color:#94a3b8 !important;
      border-color:#334155 !important;
    }
    .btn-outline-secondary:hover {
      background:#1e293b !important;
      color:#e2e8f0 !important;
    }

    /* ── Custom components ───────────────────────────────────────────────── */
    .card-dark { background:#1e293b; border:1px solid #334155; border-radius:14px; }
    .card-dark .card-header {
      background:#162032; border-bottom:1px solid #334155;
      border-radius:13px 13px 0 0; padding:14px 20px; font-weight:700;
      color:#e2e8f0;
    }
    .savit-logo {
      width:42px; height:42px;
      background:linear-gradient(135deg,#6366f1,#a78bfa);
      border-radius:12px; display:flex; align-items:center;
      justify-content:center; font-size:1.3rem; font-weight:900; color:#fff;
    }
    .badge-count {
      background:#334155; color:#94a3b8; font-weight:600;
      padding:2px 8px; border-radius:8px; font-size:.75rem;
    }
    .step-num {
      width:28px; height:28px; border-radius:50%; background:#6366f1;
      color:#fff; display:flex; align-items:center; justify-content:center;
      font-weight:700; font-size:.8rem; flex-shrink:0;
    }
    .upload-zone {
      border:2px dashed #334155; border-radius:10px;
      padding:24px; text-align:center; transition:border .2s;
    }
    .upload-zone:hover { border-color:#6366f1; }
    .upload-zone .text-muted { color:#475569 !important; }
    code {
      background:#0f172a; padding:2px 6px;
      border-radius:4px; color:#a5b4fc; font-size:.85em;
    }
    .stat-row {
      display:flex; align-items:center; justify-content:space-between;
      padding:6px 0; border-bottom:1px solid #1e293b;
    }
    .stat-row:last-child { border-bottom:none; }
    .stat-row .small { color:#cbd5e1; }

    /* Step labels in the export guide */
    .step-label { color:#cbd5e1; }

    /* Info section at bottom */
    .info-label  { color:#e2e8f0; font-weight:600; }
    .info-sub    { color:#64748b; }

    /* File inputs on dark background */
    input[type="file"] { color:#94a3b8; }
  </style>
</head>
<body>
<div class="container py-5" style="max-width:860px;">

  <!-- Header -->
  <div class="d-flex align-items-center gap-3 mb-4">
    <div class="savit-logo">S</div>
    <div>
      <h1 class="mb-0 fs-4 fw-bold">Savit Data Migration Tool</h1>
      <div class="text-muted small">Export your data &middot; Import on any new machine</div>
    </div>
    <a href="<?= htmlspecialchars(rtrim(str_replace('\\','/',dirname($_SERVER['SCRIPT_NAME'])),'/')) ?>/" class="btn btn-sm btn-outline-secondary ms-auto">
      <i class="bi bi-arrow-left me-1"></i>Back to Dashboard
    </a>
  </div>

  <!-- Import Result -->
  <?php if (isset($importResult)): ?>
  <div class="mb-4">
    <?php foreach ($importResult['ok'] as $m): ?>
      <div class="alert alert-success d-flex gap-2 py-2"><i class="bi bi-check-circle-fill"></i><?= $m ?></div>
    <?php endforeach; ?>
    <?php foreach ($importResult['errors'] as $m): ?>
      <div class="alert alert-danger d-flex gap-2 py-2" style="font-size:.85rem;"><i class="bi bi-x-circle-fill flex-shrink-0"></i><?= $m ?></div>
    <?php endforeach; ?>
    <?php if (empty($importResult['errors'])): ?>
      <div class="alert alert-success"><i class="bi bi-stars me-2"></i><strong>Import complete!</strong> All your data is restored. <a href="<?= htmlspecialchars(rtrim(str_replace('\\','/',dirname($_SERVER['SCRIPT_NAME'])),'/')) ?>/" class="alert-link">Go to Dashboard &rarr;</a></div>
    <?php endif; ?>
  </div>
  <?php endif; ?>

  <div class="row g-4">

    <!-- LEFT: Current DB Status -->
    <div class="col-md-5">
      <div class="card-dark h-100">
        <div class="card-header d-flex align-items-center gap-2">
          <i class="bi bi-database-fill text-info"></i> Current Database
        </div>
        <div class="p-3">
          <?php if ($dbOk): ?>
          <div class="mb-3">
            <div class="text-muted small mb-1">Database</div>
            <code><?= htmlspecialchars($dbName) ?></code>
            <span class="badge-count ms-2"><?= count($tables) ?> tables</span>
          </div>
          <div class="text-muted small mb-2">Table row counts:</div>
          <?php foreach ($tableStats as $tbl => $cnt): ?>
          <div class="stat-row">
            <span class="small text-light"><?= htmlspecialchars($tbl) ?></span>
            <span class="badge-count"><?= number_format($cnt) ?> rows</span>
          </div>
          <?php endforeach; ?>
          <?php else: ?>
          <div class="alert alert-danger small py-2"><?= htmlspecialchars($dbError ?? 'Cannot connect') ?></div>
          <?php endif; ?>
        </div>
      </div>
    </div>

    <!-- RIGHT: Export + Import -->
    <div class="col-md-7 d-flex flex-column gap-4">

      <!-- EXPORT -->
      <div class="card-dark">
        <div class="card-header d-flex align-items-center gap-2">
          <i class="bi bi-box-arrow-up text-success"></i> Export &mdash; Download Backup
        </div>
        <div class="p-4">
          <p class="text-muted small mb-3">Downloads a complete <code>.sql</code> backup of all your projects, crawl data, analytics, OAuth tokens, and settings. Use this to move to a new machine.</p>

          <div class="d-flex flex-column gap-2 mb-4">
            <?php
            $steps = [
                '1' => ['Download the SQL backup below', 'success'],
                '2' => ['Copy your <code>Savit/.env</code> file', 'info'],
                '3' => ['On the new machine: drop files in htdocs/Savit', 'primary'],
                '4' => ['Open setup.php &rarr; run installer', 'primary'],
                '5' => ['Open migrate.php &rarr; Import the .sql backup', 'warning'],
                '6' => ['Paste your .env &mdash; all data restored &#10003;', 'success'],
            ];
            foreach ($steps as $n => [$text, $color]): ?>
            <div class="d-flex align-items-center gap-2">
              <div class="step-num" style="background:var(--bs-<?= $color ?>)"><?= $n ?></div>
              <span class="small"><?= $text ?></span>
            </div>
            <?php endforeach; ?>
          </div>

          <?php if ($dbOk): ?>
          <a href="?action=export" class="btn btn-success w-100 fw-semibold">
            <i class="bi bi-download me-2"></i>Download Full Database Backup (.sql)
          </a>
          <div class="text-muted small text-center mt-2">
            ~<?= count($tables) ?> tables &middot; <?= number_format(array_sum($tableStats)) ?> total rows
          </div>
          <?php else: ?>
          <div class="alert alert-danger small py-2">Cannot export — database connection failed</div>
          <?php endif; ?>
        </div>
      </div>

      <!-- IMPORT -->
      <div class="card-dark">
        <div class="card-header d-flex align-items-center gap-2">
          <i class="bi bi-box-arrow-in-down text-warning"></i> Import &mdash; Restore Backup
        </div>
        <div class="p-4">
          <p class="text-muted small mb-3">Upload a previously exported <code>.sql</code> backup to restore all data on this machine. Optionally also restore your <code>.env</code> config.</p>

          <form method="POST" enctype="multipart/form-data">
            <div class="mb-3">
              <label class="form-label text-muted small fw-semibold">SQL Backup File <span class="text-danger">*</span></label>
              <div class="upload-zone">
                <i class="bi bi-file-earmark-code fs-3 text-muted d-block mb-2"></i>
                <input type="file" name="sql_file" accept=".sql" required class="form-control bg-transparent border-0 text-light" style="max-width:300px;margin:auto;">
                <div class="text-muted small mt-1">savit_backup_YYYYMMDD_HHMMSS.sql</div>
              </div>
            </div>
            <div class="mb-4">
              <label class="form-label text-muted small fw-semibold">.env Config File <span class="text-muted fw-normal">(optional)</span></label>
              <div class="upload-zone">
                <i class="bi bi-file-lock fs-3 text-muted d-block mb-2"></i>
                <input type="file" name="env_file" accept=".env,text/plain" class="form-control bg-transparent border-0 text-light" style="max-width:300px;margin:auto;">
                <div class="text-muted small mt-1">Your Savit .env file</div>
              </div>
            </div>

            <div class="alert alert-warning d-flex gap-2 py-2 small mb-3">
              <i class="bi bi-exclamation-triangle-fill flex-shrink-0"></i>
              <span>Import will <strong>overwrite existing data</strong>. Run <code>setup.php</code> first on a fresh install before importing.</span>
            </div>

            <button type="submit" class="btn btn-warning w-100 fw-semibold text-dark">
              <i class="bi bi-upload me-2"></i>Import & Restore Data
            </button>
          </form>
        </div>
      </div>

    </div>
  </div>

  <!-- What gets exported note -->
  <div class="card-dark mt-4 p-4">
    <h6 class="fw-semibold mb-3"><i class="bi bi-info-circle me-2 text-info"></i>What's Included in a Backup</h6>
    <div class="row row-cols-2 row-cols-md-4 g-3">
      <?php
      $groups = [
        ['icon' => 'folder2-open', 'label' => 'Projects', 'color' => '#6366f1', 'tables' => ['projects']],
        ['icon' => 'search', 'label' => 'Audit Data', 'color' => '#0891b2', 'tables' => ['crawl_runs','crawl_pages','crawl_links']],
        ['icon' => 'bar-chart-line-fill', 'label' => 'Analytics', 'color' => '#7c3aed', 'tables' => ['analytics_snapshots','page_analytics']],
        ['icon' => 'shield-lock', 'label' => 'OAuth Tokens', 'color' => '#059669', 'tables' => ['oauth_tokens']],
        ['icon' => 'lightning-fill', 'label' => 'Content', 'color' => '#d97706', 'tables' => ['content_articles']],
        ['icon' => 'key-fill', 'label' => 'Categories', 'color' => '#db2777', 'tables' => ['page_categories']],
        ['icon' => 'gear-fill', 'label' => 'Settings', 'color' => '#475569', 'tables' => ['*all tables*']],
        ['icon' => 'file-lock', 'label' => '.env Config', 'color' => '#b45309', 'tables' => ['manual copy']],
      ];
      foreach ($groups as $g): ?>
      <div class="col">
        <div class="d-flex align-items-center gap-2">
          <i class="bi bi-<?= $g['icon'] ?>" style="color:<?= $g['color'] ?>;font-size:1.1rem;"></i>
          <div>
            <div class="small fw-semibold info-label"><?= $g['label'] ?></div>
            <div class="info-sub" style="font-size:.7rem;"><?= implode(', ', $g['tables']) ?></div>
          </div>
        </div>
      </div>
      <?php endforeach; ?>
    </div>
  </div>

  <p class="text-muted small text-center mt-4">
    <i class="bi bi-shield-lock me-1"></i>This tool is for local/trusted use only. Remove or password-protect <code>migrate.php</code> on production servers.
  </p>

</div>
</body>
</html>
