src/app/(dashboard)/dashboard/settings/components/SystemStorageTab.tsx

"use client";

import { useState, useEffect, useRef } from "react";
import { Card, Button, Badge } from "@/shared/components";
import { useLocale, useTranslations } from "next-intl";
import DatabaseBackupRetentionCard from "./DatabaseBackupRetentionCard";

export default function SystemStorageTab() {
  const [backups, setBackups] = useState([]);
  const [backupsLoading, setBackupsLoading] = useState(false);
  const [backupsExpanded, setBackupsExpanded] = useState(false);
  const [restoreStatus, setRestoreStatus] = useState({ type: "", message: "" });
  const [restoringId, setRestoringId] = useState(null);
  const [confirmRestoreId, setConfirmRestoreId] = useState(null);
  const [manualBackupLoading, setManualBackupLoading] = useState(false);
  const [manualBackupStatus, setManualBackupStatus] = useState({ type: "", message: "" });
  const [exportLoading, setExportLoading] = useState(false);
  const [importLoading, setImportLoading] = useState(false);
  const [importStatus, setImportStatus] = useState({ type: "", message: "" });
  const [confirmImport, setConfirmImport] = useState(false);
  const [pendingImportFile, setPendingImportFile] = useState<File | null>(null);
  const [clearCacheLoading, setClearCacheLoading] = useState(false);
  const [clearCacheStatus, setClearCacheStatus] = useState({ type: "", message: "" });
  const [purgeLogsLoading, setPurgeLogsLoading] = useState(false);
  const [purgeLogsStatus, setPurgeLogsStatus] = useState({ type: "", message: "" });
  const [manualVacuumLoading, setManualVacuumLoading] = useState(false);
  const [manualVacuumStatus, setManualVacuumStatus] = useState({ type: "", message: "" });
  const [cleanupBackupsLoading, setCleanupBackupsLoading] = useState(false);
  const [cleanupBackupsStatus, setCleanupBackupsStatus] = useState({ type: "", message: "" });
  const [saveBackupRetentionLoading, setSaveBackupRetentionLoading] = useState(false);
  const [backupRetentionStatus, setBackupRetentionStatus] = useState({ type: "", message: "" });
  const [purgeQuotaSnapshotsLoading, setPurgeQuotaSnapshotsLoading] = useState(false);
  const [purgeQuotaSnapshotsStatus, setPurgeQuotaSnapshotsStatus] = useState({
    type: "",
    message: "",
  });
  const [purgeCallLogsLoading, setPurgeCallLogsLoading] = useState(false);
  const [purgeCallLogsStatus, setPurgeCallLogsStatus] = useState({ type: "", message: "" });
  const [purgeDetailedLogsLoading, setPurgeDetailedLogsLoading] = useState(false);
  const [purgeDetailedLogsStatus, setPurgeDetailedLogsStatus] = useState({ type: "", message: "" });
  const fileInputRef = useRef<HTMLInputElement>(null);
  const jsonInputRef = useRef<HTMLInputElement>(null);
  const locale = useLocale();
  const t = useTranslations("settings");
  const tc = useTranslations("common");
  const [storageHealth, setStorageHealth] = useState({
    driver: "sqlite",
    dbPath: "~/.omniroute/storage.sqlite",
    sizeBytes: 0,
    retentionDays: {
      app: 7,
      call: 7,
    },
    tableMaxRows: {
      callLogs: 100000,
      proxyLogs: 100000,
    },
    backupCount: 0,
    backupRetention: {
      maxFiles: 20,
      days: 0,
    },
    lastBackupAt: null,
  });
  const [backupCleanupOptions, setBackupCleanupOptions] = useState({
    keepLatest: 20,
    retentionDays: 0,
  });

  // Database settings state (tasks 23-26)
  const [dbSettings, setDbSettings] = useState<any>(null);
  const [dbSettingsLoading, setDbSettingsLoading] = useState(true);
  const [dbSettingsSaving, setDbSettingsSaving] = useState(false);
  const [dbStatsRefreshing, setDbStatsRefreshing] = useState(false);

  const loadBackups = async () => {
    setBackupsLoading(true);
    try {
      const res = await fetch("/api/db-backups");
      const data = await res.json();
      setBackups(data.backups || []);
    } catch (err) {
      console.error("Failed to fetch backups:", err);
    } finally {
      setBackupsLoading(false);
    }
  };

  const loadStorageHealth = async () => {
    try {
      const res = await fetch("/api/storage/health");
      if (!res.ok) return;
      const data = await res.json();
      setStorageHealth((prev) => ({ ...prev, ...data }));
      setBackupCleanupOptions({
        keepLatest: data.backupRetention?.maxFiles || 20,
        retentionDays: data.backupRetention?.days || 0,
      });
    } catch (err) {
      console.error("Failed to fetch storage health:", err);
    }
  };

  const loadDatabaseSettings = async () => {
    setDbSettingsLoading(true);
    try {
      const res = await fetch("/api/settings/database");
      if (res.ok) {
        const data = await res.json();
        setDbSettings(data);
      }
    } catch (err) {
      console.error("Failed to load database settings:", err);
    } finally {
      setDbSettingsLoading(false);
    }
  };

  const saveDatabaseSettings = async () => {
    if (!dbSettings) return;
    setDbSettingsSaving(true);
    try {
      const { logs, backup, cache, retention, aggregation, optimization } = dbSettings;
      const res = await fetch("/api/settings/database", {
        method: "PATCH",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ logs, backup, cache, retention, aggregation, optimization }),
      });
      if (res.ok) {
        await loadDatabaseSettings();
      }
    } catch (err) {
      console.error("Failed to save database settings:", err);
    } finally {
      setDbSettingsSaving(false);
    }
  };

  const refreshDatabaseStats = async () => {
    setDbStatsRefreshing(true);
    try {
      await fetch("/api/settings/database/refresh-stats", { method: "POST" });
      await loadDatabaseSettings();
    } catch (err) {
      console.error("Failed to refresh database stats:", err);
    } finally {
      setDbStatsRefreshing(false);
    }
  };

  const handleSaveBackupRetention = async () => {
    setSaveBackupRetentionLoading(true);
    setBackupRetentionStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/db-backups", {
        method: "PATCH",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(backupCleanupOptions),
      });
      const data = await res.json();
      if (res.ok) {
        setBackupRetentionStatus({
          type: "success",
          message: "Backup retention saved.",
        });
        await loadStorageHealth();
      } else {
        setBackupRetentionStatus({
          type: "error",
          message: data.error || "Failed to save backup retention",
        });
      }
    } catch {
      setBackupRetentionStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setSaveBackupRetentionLoading(false);
    }
  };

  const handleCleanupBackups = async () => {
    setCleanupBackupsLoading(true);
    setCleanupBackupsStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/db-backups", {
        method: "DELETE",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify(backupCleanupOptions),
      });
      const data = await res.json();
      if (res.ok) {
        setCleanupBackupsStatus({
          type: "success",
          message: `Deleted ${data.deletedBackupFamilies} backup set(s) and ${data.deletedFiles} file(s).`,
        });
        await loadStorageHealth();
        if (backupsExpanded) await loadBackups();
      } else {
        setCleanupBackupsStatus({
          type: "error",
          message: data.error || "Failed to clean database backups",
        });
      }
    } catch {
      setCleanupBackupsStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setCleanupBackupsLoading(false);
    }
  };

  const handleClearCache = async () => {
    setClearCacheLoading(true);
    setClearCacheStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/cache", { method: "DELETE" });
      const data = await res.json().catch(() => null);
      if (res.ok) {
        setClearCacheStatus({
          type: "success",
          message: t("cacheCleared") || "Cache cleared successfully",
        });
      } else {
        setClearCacheStatus({
          type: "error",
          message: data?.error || t("clearCacheFailed") || "Failed to clear cache",
        });
      }
    } catch {
      setClearCacheStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setClearCacheLoading(false);
    }
  };

  const handlePurgeExpiredLogs = async () => {
    setPurgeLogsLoading(true);
    setPurgeLogsStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/settings/purge-logs", { method: "POST" });
      const data = await res.json().catch(() => null);
      if (res.ok) {
        const deleted = data?.deleted ?? 0;
        setPurgeLogsStatus({
          type: "success",
          message: t("logsDeleted", { count: deleted }) || `Purged ${deleted} expired log(s)`,
        });
      } else {
        setPurgeLogsStatus({
          type: "error",
          message: data?.error || t("purgeLogsFailed") || "Failed to purge logs",
        });
      }
    } catch {
      setPurgeLogsStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setPurgeLogsLoading(false);
    }
  };

  const handlePurgeQuotaSnapshots = async () => {
    setPurgeQuotaSnapshotsLoading(true);
    setPurgeQuotaSnapshotsStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/settings/purge-quota-snapshots", { method: "POST" });
      const data = await res.json();
      if (res.ok) {
        setPurgeQuotaSnapshotsStatus({
          type: "success",
          message: `Purged ${data.deleted} quota snapshots`,
        });
      } else {
        setPurgeQuotaSnapshotsStatus({
          type: "error",
          message: data.error || "Failed to purge quota snapshots",
        });
      }
    } catch {
      setPurgeQuotaSnapshotsStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setPurgeQuotaSnapshotsLoading(false);
    }
  };

  const handlePurgeCallLogs = async () => {
    setPurgeCallLogsLoading(true);
    setPurgeCallLogsStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/settings/purge-call-logs", { method: "POST" });
      const data = await res.json();
      if (res.ok) {
        setPurgeCallLogsStatus({
          type: "success",
          message: `Purged ${data.deleted} call logs`,
        });
      } else {
        setPurgeCallLogsStatus({
          type: "error",
          message: data.error || "Failed to purge call logs",
        });
      }
    } catch {
      setPurgeCallLogsStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setPurgeCallLogsLoading(false);
    }
  };

  const handlePurgeDetailedLogs = async () => {
    setPurgeDetailedLogsLoading(true);
    setPurgeDetailedLogsStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/settings/purge-detailed-logs", { method: "POST" });
      const data = await res.json();
      if (res.ok) {
        setPurgeDetailedLogsStatus({
          type: "success",
          message: `Purged ${data.deleted} detailed logs`,
        });
      } else {
        setPurgeDetailedLogsStatus({
          type: "error",
          message: data.error || "Failed to purge detailed logs",
        });
      }
    } catch {
      setPurgeDetailedLogsStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setPurgeDetailedLogsLoading(false);
    }
  };

  const handleManualVacuum = async () => {
    setManualVacuumLoading(true);
    setManualVacuumStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/settings/database/vacuum", { method: "POST" });
      const data = await res.json().catch(() => null);
      if (res.ok && data?.success !== false) {
        setManualVacuumStatus({
          type: "success",
          message: data?.message || "VACUUM completed",
        });
        await loadDatabaseSettings();
        await loadStorageHealth();
      } else {
        setManualVacuumStatus({
          type: "error",
          message: data?.error || "VACUUM failed",
        });
      }
    } catch {
      setManualVacuumStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setManualVacuumLoading(false);
    }
  };

  const handleManualBackup = async () => {
    setManualBackupLoading(true);
    setManualBackupStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/db-backups", { method: "PUT" });
      const data = await res.json();
      if (res.ok) {
        if (data.filename) {
          setManualBackupStatus({
            type: "success",
            message: t("backupCreated", { file: data.filename }),
          });
        } else {
          setManualBackupStatus({
            type: "info",
            message: data.message || t("noChangesSinceBackup"),
          });
        }
        await loadStorageHealth();
        if (backupsExpanded) await loadBackups();
      } else {
        setManualBackupStatus({ type: "error", message: data.error || t("backupFailed") });
      }
    } catch {
      setManualBackupStatus({ type: "error", message: t("errorOccurred") });
    } finally {
      setManualBackupLoading(false);
    }
  };

  const handleRestore = async (backupId) => {
    setRestoringId(backupId);
    setRestoreStatus({ type: "", message: "" });
    try {
      const res = await fetch("/api/db-backups", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ backupId }),
      });
      const data = await res.json();
      if (res.ok) {
        setRestoreStatus({
          type: "success",
          message: t("restoreSuccess", {
            connections: data.connectionCount,
            nodes: data.nodeCount,
            combos: data.comboCount,
            apiKeys: data.apiKeyCount,
          }),
        });
        await loadBackups();
        await loadStorageHealth();
      } else {
        setRestoreStatus({ type: "error", message: data.error || t("restoreFailed") });
      }
    } catch {
      setRestoreStatus({ type: "error", message: t("errorDuringRestore") });
    } finally {
      setRestoringId(null);
      setConfirmRestoreId(null);
    }
  };

  useEffect(() => {
    loadStorageHealth();
    loadDatabaseSettings();
  }, []);

  /** Triggers a browser file download from an existing Blob. */
  const triggerDownload = (blob: Blob, filename: string) => {
    const url = URL.createObjectURL(blob);
    const a = document.createElement("a");
    a.href = url;
    a.download = filename;
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    URL.revokeObjectURL(url);
  };

  /** Fetches a URL, reads the response as a Blob and triggers a download. */
  const fetchAndDownload = async (
    apiUrl: string,
    fallbackFilename: string,
    errorMessage: string
  ) => {
    const res = await fetch(apiUrl);
    if (!res.ok) {
      const data = await res.json().catch(() => ({}));
      throw new Error((data as { error?: string }).error || errorMessage);
    }
    const blob = await res.blob();
    const disposition = res.headers.get("Content-Disposition") || "";
    const filenameMatch = disposition.match(/filename="?([^"]+)"?/);
    triggerDownload(blob, filenameMatch?.[1] || fallbackFilename);
  };

  const handleExportJson = async () => {
    setExportLoading(true);
    try {
      await fetchAndDownload(
        "/api/settings/export-json",
        `omniroute-legacy-backup-${new Date().toISOString().replace(/[:.]/g, "-")}.json`,
        "JSON Export failed"
      );
    } catch (err) {
      console.error("Export JSON failed:", err);
      setImportStatus({
        type: "error",
        message: t("exportFailedWithError", { error: (err as Error).message }),
      });
    } finally {
      setExportLoading(false);
    }
  };

  const handleImportJsonClick = () => {
    jsonInputRef.current?.click();
  };

  const handleJsonSelected = (e: React.ChangeEvent<HTMLInputElement>) => {
    const file = e.target.files?.[0];
    if (!file) return;
    if (!file.name.endsWith(".json")) {
      setImportStatus({
        type: "error",
        message: "Invalid file type. Only .json allowed.",
      });
      return;
    }

    // Auto import JSON
    const reader = new FileReader();
    reader.onload = async (e) => {
      try {
        setImportLoading(true);
        const res = await fetch("/api/settings/import-json", {
          method: "POST",
          headers: { "Content-Type": "application/json" },
          body: e.target?.result as string,
        });
        const data = await res.json();
        if (res.ok) {
          setImportStatus({
            type: "success",
            message: data.message || "Legacy JSON imported successfully!",
          });
          await loadStorageHealth();
          if (backupsExpanded) await loadBackups();
        } else {
          setImportStatus({ type: "error", message: data.error || "Failed to import JSON" });
        }
      } catch (err) {
        setImportStatus({ type: "error", message: "Error during JSON import" });
      } finally {
        setImportLoading(false);
        if (jsonInputRef.current) jsonInputRef.current.value = "";
      }
    };
    reader.readAsText(file);
  };

  const handleExport = async () => {
    setExportLoading(true);
    try {
      await fetchAndDownload(
        "/api/db-backups/export",
        `omniroute-backup-${new Date().toISOString().replace(/[:.]/g, "-")}.sqlite`,
        t("exportFailed")
      );
    } catch (err) {
      console.error("Export failed:", err);
      setImportStatus({
        type: "error",
        message: t("exportFailedWithError", { error: (err as Error).message }),
      });
    } finally {
      setExportLoading(false);
    }
  };

  const handleImportClick = () => {
    fileInputRef.current?.click();
  };

  const handleFileSelected = (e: React.ChangeEvent<HTMLInputElement>) => {
    const file = e.target.files?.[0];
    if (!file) return;
    if (!file.name.endsWith(".sqlite")) {
      setImportStatus({
        type: "error",
        message: t("invalidFileType"),
      });
      return;
    }
    setPendingImportFile(file);
    setConfirmImport(true);
    if (fileInputRef.current) fileInputRef.current.value = "";
  };

  const handleImportConfirm = async () => {
    if (!pendingImportFile) return;
    setImportLoading(true);
    setImportStatus({ type: "", message: "" });
    setConfirmImport(false);
    try {
      const arrayBuffer = await pendingImportFile.arrayBuffer();
      const res = await fetch(
        `/api/db-backups/import?filename=${encodeURIComponent(pendingImportFile.name)}`,
        {
          method: "POST",
          headers: { "Content-Type": "application/octet-stream" },
          body: arrayBuffer,
        }
      );
      const data = await res.json();
      if (res.ok) {
        setImportStatus({
          type: "success",
          message: t("importSuccess", {
            connections: data.connectionCount,
            nodes: data.nodeCount,
            combos: data.comboCount,
            apiKeys: data.apiKeyCount,
          }),
        });
        await loadStorageHealth();
        if (backupsExpanded) await loadBackups();
      } else {
        setImportStatus({ type: "error", message: data.error || t("importFailed") });
      }
    } catch {
      setImportStatus({ type: "error", message: t("errorDuringImport") });
    } finally {
      setImportLoading(false);
      setPendingImportFile(null);
    }
  };

  const handleImportCancel = () => {
    setConfirmImport(false);
    setPendingImportFile(null);
  };

  const formatBytes = (bytes) => {
    if (!bytes || bytes === 0) return "0 B";
    if (bytes < 1024) return `${bytes} B`;
    if (bytes < 1024 * 1024) return `${(bytes / 1024).toFixed(1)} KB`;
    return `${(bytes / (1024 * 1024)).toFixed(1)} MB`;
  };

  const formatRelativeTime = (isoString) => {
    if (!isoString) return null;
    const now = new Date();
    const then = new Date(isoString);
    const diffMs = (now as any) - (then as any);
    const diffMin = Math.floor(diffMs / 60000);
    if (diffMin < 1) return t("justNow");
    if (diffMin < 60) return t("minutesAgo", { count: diffMin });
    const diffHr = Math.floor(diffMin / 60);
    if (diffHr < 24) return t("hoursAgo", { count: diffHr });
    const diffDays = Math.floor(diffHr / 24);
    return t("daysAgo", { count: diffDays });
  };

  const formatBackupReason = (reason) => {
    if (reason === "manual") return t("backupReasonManual");
    if (reason === "pre-restore") return t("backupReasonPreRestore");
    return reason;
  };

  const renderStatusAlert = (status, index) => {
    if (!status.message) return null;
    const isInfo = status.type === "info";
    const isSuccess = status.type === "success";
    const className =
      "p-3 rounded-lg text-sm " +
      (isSuccess
        ? "bg-green-500/10 text-green-500 border border-green-500/20"
        : isInfo
          ? "bg-blue-500/10 text-blue-500 border border-blue-500/20"
          : "bg-red-500/10 text-red-500 border border-red-500/20");

    return (
      <div key={index} className={className} role="alert">
        <div className="flex items-center gap-2">
          <span className="material-symbols-outlined text-[16px]" aria-hidden="true">
            {isSuccess ? "check_circle" : isInfo ? "info" : "error"}
          </span>
          {status.message}
        </div>
      </div>
    );
  };

  const renderDatabaseStatistics = () => {
    if (dbSettingsLoading || !dbSettings?.stats) return null;

    return (
      <div className="mb-4 p-4 rounded-lg border border-border bg-bg">
        <div className="flex items-center justify-between gap-3 mb-3">
          <h4 className="text-sm font-semibold flex items-center gap-2">
            <span className="material-symbols-outlined text-[18px]" aria-hidden="true">
              analytics
            </span>
            Database Statistics
          </h4>
          <Button
            variant="outline"
            size="sm"
            onClick={refreshDatabaseStats}
            loading={dbStatsRefreshing}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              refresh
            </span>
            Refresh
          </Button>
        </div>
        <div className="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 gap-4">
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storageDatabaseSize")}</p>
            <p className="text-sm font-semibold">
              {formatBytes(dbSettings.stats.databaseSizeBytes)}
            </p>
          </div>
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storagePageCount")}</p>
            <p className="text-sm font-semibold">{dbSettings.stats.pageCount.toLocaleString()}</p>
          </div>
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storageFreelistCount")}</p>
            <p className="text-sm font-semibold">
              {dbSettings.stats.freelistCount.toLocaleString()}
            </p>
          </div>
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storageLastVacuum")}</p>
            <p className="text-sm font-semibold">
              {dbSettings.stats.lastVacuumAt
                ? new Date(dbSettings.stats.lastVacuumAt).toLocaleString(locale)
                : "Never"}
            </p>
          </div>
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storageLastOptimization")}</p>
            <p className="text-sm font-semibold">
              {dbSettings.stats.lastOptimizationAt
                ? new Date(dbSettings.stats.lastOptimizationAt).toLocaleString(locale)
                : "Never"}
            </p>
          </div>
          <div className="p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02]">
            <p className="text-xs text-text-muted mb-1">{t("storageIntegrityCheck")}</p>
            <p className="text-sm font-semibold">
              {dbSettings.stats.integrityCheck === "ok" ? (
                <span className="text-green-500">{t("storageIntegrityOk")}</span>
              ) : dbSettings.stats.integrityCheck === "error" ? (
                <span className="text-red-500">{t("storageIntegrityError")}</span>
              ) : (
                "Not checked"
              )}
            </p>
          </div>
        </div>
      </div>
    );
  };

  const renderBackupList = () => {
    if (!backupsExpanded) return null;

    return (
      <div className="flex flex-col gap-2 mt-3">
        {backupsLoading ? (
          <div className="flex items-center justify-center py-6 text-text-muted">
            <span
              className="material-symbols-outlined animate-spin text-[20px] mr-2"
              aria-hidden="true"
            >
              progress_activity
            </span>
            {t("loadingBackups")}
          </div>
        ) : backups.length === 0 ? (
          <div className="text-center py-6 text-text-muted text-sm">
            <span
              className="material-symbols-outlined text-[32px] mb-2 block opacity-40"
              aria-hidden="true"
            >
              folder_off
            </span>
            {t("noBackupsYet")}
          </div>
        ) : (
          <>
            <div className="flex items-center justify-between mb-1">
              <span className="text-xs text-text-muted">
                {t("backupsAvailable", { count: backups.length })}
              </span>
              <button
                onClick={loadBackups}
                className="text-xs text-primary hover:underline flex items-center gap-1"
              >
                <span className="material-symbols-outlined text-[14px]" aria-hidden="true">
                  refresh
                </span>
                {t("refresh")}
              </button>
            </div>
            {backups.map((backup) => (
              <div
                key={backup.id}
                className="flex items-center justify-between p-3 rounded-lg bg-black/[0.02] dark:bg-white/[0.02] border border-border/50 hover:border-border transition-colors"
              >
                <div className="flex-1 min-w-0">
                  <div className="flex items-center gap-2 mb-1">
                    <span
                      className="material-symbols-outlined text-[16px] text-amber-500"
                      aria-hidden="true"
                    >
                      description
                    </span>
                    <span className="text-sm font-medium truncate">
                      {new Date(backup.createdAt).toLocaleString(locale)}
                    </span>
                    <Badge
                      variant={
                        backup.reason === "pre-restore"
                          ? "warning"
                          : backup.reason === "manual"
                            ? "success"
                            : "default"
                      }
                      size="sm"
                    >
                      {formatBackupReason(backup.reason)}
                    </Badge>
                  </div>
                  <div className="flex items-center gap-3 text-xs text-text-muted ml-6">
                    <span>{t("connectionsCount", { count: backup.connectionCount })}</span>
                    <span>•</span>
                    <span>{formatBytes(backup.size)}</span>
                  </div>
                </div>
                <div className="flex items-center gap-2 ml-3">
                  {confirmRestoreId === backup.id ? (
                    <>
                      <span className="text-xs text-amber-500 font-medium">{t("confirm")}</span>
                      <Button
                        variant="primary"
                        size="sm"
                        onClick={() => handleRestore(backup.id)}
                        loading={restoringId === backup.id}
                        className="!bg-amber-500 hover:!bg-amber-600"
                      >
                        {t("yes")}
                      </Button>
                      <Button variant="outline" size="sm" onClick={() => setConfirmRestoreId(null)}>
                        {t("no")}
                      </Button>
                    </>
                  ) : (
                    <Button
                      variant="outline"
                      size="sm"
                      onClick={() => setConfirmRestoreId(backup.id)}
                    >
                      <span
                        className="material-symbols-outlined text-[14px] mr-1"
                        aria-hidden="true"
                      >
                        restore
                      </span>
                      {t("restore")}
                    </Button>
                  )}
                </div>
              </div>
            ))}
          </>
        )}
      </div>
    );
  };

  const renderRetentionSettings = () => {
    if (dbSettingsLoading || !dbSettings) return null;

    const retentionFields = [
      ["quotaSnapshots", t("retentionQuotaSnapshots"), 7],
      ["compressionAnalytics", t("retentionCompressionAnalytics"), 30],
      ["mcpAudit", t("retentionMcpAudit"), 30],
      ["a2aEvents", t("retentionA2aEvents"), 30],
      ["callLogs", t("retentionCallLogs"), 30],
      ["usageHistory", t("retentionUsageHistory"), 30],
      ["memoryEntries", t("retentionMemoryEntries"), 30],
    ];

    return (
      <div className="mt-6 p-4 rounded-lg border border-border bg-bg">
        <div className="flex items-start justify-between gap-3 flex-wrap mb-3">
          <div>
            <h4 className="text-sm font-semibold flex items-center gap-2">
              <span className="material-symbols-outlined text-[18px]" aria-hidden="true">
                schedule
              </span>
              {t("storageRetentionCleanup")}
            </h4>
            <p className="mt-1 text-xs text-text-muted">{t("storageRetentionCleanupDesc")}</p>
          </div>
          <div className="flex items-center gap-2">
            <Badge variant="default" size="sm">
              {t("retentionCallDays", { count: storageHealth.retentionDays.call })}
            </Badge>
            <Badge variant="default" size="sm">
              {t("retentionAppDays", { count: storageHealth.retentionDays.app })}
            </Badge>
            <Badge variant="default" size="sm">
              {t("retentionRows", {
                count: (storageHealth.tableMaxRows?.callLogs ?? 100000).toLocaleString(),
              })}
            </Badge>
          </div>
        </div>
        <div className="grid grid-cols-1 sm:grid-cols-2 gap-4">
          {retentionFields.map(([key, label, fallback]) => (
            <div key={String(key)}>
              <label className="block text-xs text-text-muted mb-1">{label}</label>
              <input
                type="number"
                min="1"
                max="365"
                value={dbSettings.retention[key]}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    retention: {
                      ...dbSettings.retention,
                      [key]: parseInt(e.target.value) || fallback,
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              />
            </div>
          ))}
        </div>
        <div className="mt-3">
          <Button
            variant="primary"
            size="sm"
            onClick={saveDatabaseSettings}
            loading={dbSettingsSaving}
          >
            {t("saveRetentionSettings")}
          </Button>
        </div>
        <div className="mt-5 border-t border-border/50 pt-4">
          <DatabaseBackupRetentionCard
            title={t("storageDatabaseBackups")}
            className="mb-0"
            storageHealth={storageHealth}
            backupCleanupOptions={backupCleanupOptions}
            setBackupCleanupOptions={setBackupCleanupOptions}
            saveBackupRetentionLoading={saveBackupRetentionLoading}
            backupRetentionStatus={backupRetentionStatus}
            setBackupRetentionStatus={setBackupRetentionStatus}
            cleanupBackupsLoading={cleanupBackupsLoading}
            cleanupBackupsStatus={cleanupBackupsStatus}
            onSaveRetention={handleSaveBackupRetention}
            onCleanupBackups={handleCleanupBackups}
          />
        </div>
      </div>
    );
  };

  const renderOptimizationSettings = () => {
    if (dbSettingsLoading || !dbSettings) return null;

    return (
      <div className="mt-6 p-4 rounded-lg border border-border bg-bg">
        <h4 className="text-sm font-semibold mb-3 flex items-center gap-2">
          <span className="material-symbols-outlined text-[18px]" aria-hidden="true">
            tune
          </span>
          Optimization Settings
        </h4>
        <div className="space-y-4">
          <div className="grid grid-cols-1 sm:grid-cols-2 gap-4">
            <div>
              <label className="block text-xs text-text-muted mb-1">
                {t("storageAutoVacuumMode")}
              </label>
              <select
                value={dbSettings.optimization.autoVacuumMode}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    optimization: {
                      ...dbSettings.optimization,
                      autoVacuumMode: e.target.value as "NONE" | "FULL" | "INCREMENTAL",
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              >
                <option value="NONE">None</option>
                <option value="FULL">Full</option>
                <option value="INCREMENTAL">Incremental</option>
              </select>
            </div>
            <div>
              <label className="block text-xs text-text-muted mb-1">
                {t("storageScheduledVacuum")}
              </label>
              <select
                value={dbSettings.optimization.scheduledVacuum}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    optimization: {
                      ...dbSettings.optimization,
                      scheduledVacuum: e.target.value as "never" | "daily" | "weekly" | "monthly",
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              >
                <option value="never">Never</option>
                <option value="daily">Daily</option>
                <option value="weekly">Weekly</option>
                <option value="monthly">Monthly</option>
              </select>
            </div>
            <div>
              <label className="block text-xs text-text-muted mb-1">{t("storageVacuumHour")}</label>
              <input
                type="number"
                min="0"
                max="23"
                value={dbSettings.optimization.vacuumHour}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    optimization: {
                      ...dbSettings.optimization,
                      vacuumHour: parseInt(e.target.value) || 2,
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              />
            </div>
            <div>
              <label className="block text-xs text-text-muted mb-1">{t("storagePageSize")}</label>
              <input
                type="number"
                min="512"
                max="65536"
                step="512"
                value={dbSettings.optimization.pageSize}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    optimization: {
                      ...dbSettings.optimization,
                      pageSize: parseInt(e.target.value) || 4096,
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              />
            </div>
            <div>
              <label className="block text-xs text-text-muted mb-1">Cache Size (KB)</label>
              <input
                type="number"
                min="1"
                step="1024"
                value={dbSettings.optimization.cacheSize}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    optimization: {
                      ...dbSettings.optimization,
                      cacheSize: parseInt(e.target.value) || 16384,
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              />
            </div>
          </div>
          <div className="flex items-center gap-3">
            <input
              type="checkbox"
              id="optimize-on-startup"
              checked={dbSettings.optimization.optimizeOnStartup}
              onChange={(e) =>
                setDbSettings({
                  ...dbSettings,
                  optimization: {
                    ...dbSettings.optimization,
                    optimizeOnStartup: e.target.checked,
                  },
                })
              }
              className="w-4 h-4 rounded border-border text-primary focus:ring-2 focus:ring-primary"
            />
            <label htmlFor="optimize-on-startup" className="text-sm">
              Optimize on Startup
            </label>
          </div>
        </div>
        <div className="mt-3">
          <Button
            variant="primary"
            size="sm"
            onClick={saveDatabaseSettings}
            loading={dbSettingsSaving}
          >
            Save Optimization Settings
          </Button>
        </div>
      </div>
    );
  };

  const renderCompressionAggregationSettings = () => {
    if (dbSettingsLoading || !dbSettings) return null;

    return (
      <div className="mt-6 p-4 rounded-lg border border-border bg-bg">
        <h4 className="text-sm font-semibold mb-3 flex items-center gap-2">
          <span className="material-symbols-outlined text-[18px]" aria-hidden="true">
            compress
          </span>
          Compression & Aggregation Settings
        </h4>
        <div className="space-y-4">
          <div className="flex items-center gap-3">
            <input
              type="checkbox"
              id="aggregation-enabled"
              checked={dbSettings.aggregation.enabled}
              onChange={(e) =>
                setDbSettings({
                  ...dbSettings,
                  aggregation: { ...dbSettings.aggregation, enabled: e.target.checked },
                })
              }
              className="w-4 h-4 rounded border-border text-primary focus:ring-2 focus:ring-primary"
            />
            <label htmlFor="aggregation-enabled" className="text-sm">
              Enable Data Aggregation
            </label>
          </div>
          <div className="grid grid-cols-1 sm:grid-cols-2 gap-4">
            <div>
              <label className="block text-xs text-text-muted mb-1">
                Raw Data Retention (days)
              </label>
              <input
                type="number"
                min="1"
                max="365"
                value={dbSettings.aggregation.rawDataRetentionDays}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    aggregation: {
                      ...dbSettings.aggregation,
                      rawDataRetentionDays: parseInt(e.target.value) || 30,
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              />
            </div>
            <div>
              <label className="block text-xs text-text-muted mb-1">Granularity</label>
              <select
                value={dbSettings.aggregation.granularity}
                onChange={(e) =>
                  setDbSettings({
                    ...dbSettings,
                    aggregation: {
                      ...dbSettings.aggregation,
                      granularity: e.target.value as "hourly" | "daily" | "weekly",
                    },
                  })
                }
                className="w-full px-3 py-2 text-sm rounded-lg border border-border bg-bg focus:outline-none focus:ring-2 focus:ring-primary"
              >
                <option value="hourly">Hourly</option>
                <option value="daily">Daily</option>
                <option value="weekly">Weekly</option>
              </select>
            </div>
          </div>
        </div>
        <div className="mt-3">
          <Button
            variant="primary"
            size="sm"
            onClick={saveDatabaseSettings}
            loading={dbSettingsSaving}
          >
            Save Aggregation Settings
          </Button>
        </div>
      </div>
    );
  };

  return (
    <Card>
      <div className="flex items-center gap-3 mb-4">
        <div className="p-2 rounded-lg bg-green-500/10 text-green-500">
          <span className="material-symbols-outlined text-[20px]" aria-hidden="true">
            database
          </span>
        </div>
        <div className="flex-1">
          <h3 className="text-lg font-semibold">{t("systemStorage")}</h3>
          <p className="text-xs text-text-muted">{t("allDataLocal")}</p>
        </div>
        <Badge variant="success" size="sm">
          {storageHealth.driver || "json"}
        </Badge>
      </div>

      <div className="grid grid-cols-1 gap-3 mb-4">
        <div className="p-3 rounded-lg bg-bg border border-border">
          <p className="text-[11px] text-text-muted uppercase tracking-wide mb-1">
            {t("databasePath")}
          </p>
          <p className="text-sm font-mono text-text-main break-all">
            {storageHealth.dbPath || "~/.omniroute/storage.sqlite"}
          </p>
        </div>
      </div>

      {renderDatabaseStatistics()}

      <div className="pt-3 border-t border-border/50 mb-4">
        <div className="flex items-center gap-2 mb-3">
          <span
            className="material-symbols-outlined text-[18px] text-emerald-500"
            aria-hidden="true"
          >
            file_export
          </span>
          <p className="font-medium">{t("export")}</p>
        </div>
        <div className="flex flex-wrap items-center gap-2">
          <Button variant="outline" size="sm" onClick={handleExport} loading={exportLoading}>
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              download
            </span>
            {t("exportDatabase")}
          </Button>
          <Button
            variant="outline"
            size="sm"
            onClick={async () => {
              setExportLoading(true);
              try {
                await fetchAndDownload(
                  "/api/db-backups/exportAll",
                  "omniroute-full-backup.tar.gz",
                  t("exportFailed")
                );
              } catch (err) {
                setImportStatus({
                  type: "error",
                  message: t("fullExportFailedWithError", { error: (err as Error).message }),
                });
              } finally {
                setExportLoading(false);
              }
            }}
            loading={exportLoading}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              folder_zip
            </span>
            {t("exportAll")}
          </Button>
          <Button variant="outline" size="sm" onClick={handleImportClick} loading={importLoading}>
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              upload
            </span>
            {t("importDatabase")}
          </Button>
          <input
            ref={fileInputRef}
            type="file"
            accept=".sqlite"
            className="hidden"
            onChange={handleFileSelected}
          />
          <Button variant="outline" size="sm" onClick={handleExportJson} loading={exportLoading}>
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              data_object
            </span>
            Export JSON
          </Button>
          <Button
            variant="outline"
            size="sm"
            onClick={handleImportJsonClick}
            loading={importLoading}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              data_object
            </span>
            Import JSON
          </Button>
          <input
            ref={jsonInputRef}
            type="file"
            accept=".json"
            className="hidden"
            onChange={handleJsonSelected}
          />
        </div>

        {confirmImport && pendingImportFile && (
          <div className="p-4 rounded-lg mt-3 bg-amber-500/10 border border-amber-500/30">
            <div className="flex items-start gap-3">
              <span
                className="material-symbols-outlined text-[20px] text-amber-500 mt-0.5"
                aria-hidden="true"
              >
                warning
              </span>
              <div className="flex-1">
                <p className="text-sm font-medium text-amber-500 mb-1">{t("confirmDbImport")}</p>
                <p className="text-xs text-text-muted mb-2">
                  {t("confirmDbImportDesc", { file: pendingImportFile.name })}
                </p>
                <div className="flex items-center gap-2">
                  <Button
                    variant="primary"
                    size="sm"
                    onClick={handleImportConfirm}
                    className="!bg-amber-500 hover:!bg-amber-600"
                  >
                    {t("yesImport")}
                  </Button>
                  <Button variant="outline" size="sm" onClick={handleImportCancel}>
                    {tc("cancel")}
                  </Button>
                </div>
              </div>
            </div>
          </div>
        )}

        {importStatus.message && <div className="mt-3">{renderStatusAlert(importStatus, 0)}</div>}
      </div>

      <div className="pt-3 border-t border-border/50 mb-4">
        <div className="flex items-center gap-2 mb-3">
          <span className="material-symbols-outlined text-[18px] text-blue-500" aria-hidden="true">
            build
          </span>
          <p className="font-medium">{t("maintenance") || "Maintenance"}</p>
        </div>
        <div className="flex flex-wrap items-center gap-2">
          <Button
            variant="outline"
            size="sm"
            loading={clearCacheLoading}
            onClick={handleClearCache}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              delete_sweep
            </span>
            {t("clearCache") || "Clear Cache"}
          </Button>
          <Button
            variant="outline"
            size="sm"
            loading={purgeLogsLoading}
            onClick={handlePurgeExpiredLogs}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              auto_delete
            </span>
            {t("purgeExpiredLogs") || "Purge Expired Logs"}
          </Button>
          <Button
            variant="outline"
            size="sm"
            loading={manualVacuumLoading}
            onClick={handleManualVacuum}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              cleaning_services
            </span>
            Manual VACUUM
          </Button>
          <Button
            variant="outline"
            size="sm"
            loading={purgeQuotaSnapshotsLoading}
            onClick={handlePurgeQuotaSnapshots}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              delete_forever
            </span>
            Purge Quota Snapshots
          </Button>
          <Button
            variant="outline"
            size="sm"
            loading={purgeCallLogsLoading}
            onClick={handlePurgeCallLogs}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              delete_forever
            </span>
            Purge Call Logs
          </Button>
          <Button
            variant="outline"
            size="sm"
            loading={purgeDetailedLogsLoading}
            onClick={handlePurgeDetailedLogs}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              delete_forever
            </span>
            Purge Detailed Logs
          </Button>
        </div>
        <div className="mt-4 border-t border-border/50 pt-3">
          <div className="flex flex-col gap-2">
            {[
              clearCacheStatus,
              purgeLogsStatus,
              manualVacuumStatus,
              purgeQuotaSnapshotsStatus,
              purgeCallLogsStatus,
              purgeDetailedLogsStatus,
            ].map(renderStatusAlert)}
          </div>
        </div>
      </div>

      <div className="flex items-center justify-between p-3 rounded-lg bg-bg border border-border mb-4">
        <div className="flex items-center gap-2">
          <span className="material-symbols-outlined text-[16px] text-amber-500" aria-hidden="true">
            schedule
          </span>
          <div>
            <p className="text-sm font-medium">{t("lastBackup")}</p>
            <p className="text-xs text-text-muted">
              {storageHealth.lastBackupAt
                ? new Date(storageHealth.lastBackupAt).toLocaleString(locale) +
                  " (" +
                  formatRelativeTime(storageHealth.lastBackupAt) +
                  ")"
                : t("noBackupYet")}
            </p>
          </div>
        </div>
        <div className="flex flex-wrap items-center justify-end gap-2">
          <Button
            variant="outline"
            size="sm"
            onClick={handleManualBackup}
            loading={manualBackupLoading}
          >
            <span className="material-symbols-outlined text-[14px] mr-1" aria-hidden="true">
              backup
            </span>
            {t("backupNow")}
          </Button>
          <Button
            variant="outline"
            size="sm"
            onClick={() => {
              setBackupsExpanded(!backupsExpanded);
              if (!backupsExpanded && backups.length === 0) loadBackups();
            }}
          >
            {backupsExpanded ? t("hide") : t("viewBackups")}
          </Button>
        </div>
      </div>

      {manualBackupStatus.message && (
        <div className="mb-4">{renderStatusAlert(manualBackupStatus, 0)}</div>
      )}
      {restoreStatus.message && <div className="mb-4">{renderStatusAlert(restoreStatus, 1)}</div>}
      {renderBackupList()}

      {renderRetentionSettings()}
      {renderOptimizationSettings()}
      {renderCompressionAggregationSettings()}
    </Card>
  );
}