/**
 * Database & Server Settings - Unified Design with TypeScript
 * Enhanced with Autoload Management features
 */

import {  useState, useEffect  } from '@wordpress/element';
import { __, sprintf } from '@wordpress/i18n';
import {
  Spinner,
  SelectControl,
} from '@wordpress/components';
import apiFetch from '@wordpress/api-fetch';
import ProrankToggleSlider from '../../../components/ProrankToggleSlider';
import ProrankButton from '../../../components/ProrankButton';
import { useNotification } from '../../../contexts/NotificationContext';
import BloatControlPanel from './BloatControlPanel';
import OrphanedTablesPanel from './OrphanedTablesPanel';
import * as React from 'react';

// TypeScript Interfaces
interface CleanupItems {
  revisions: boolean;
  drafts: boolean;
  trash: boolean;
  spam_comments: boolean;
  unapproved_comments: boolean;
  transients: boolean;
  expired_transients: boolean;
  orphaned_meta: boolean;
  action_scheduler: boolean;
  wpmailsmtp_logs: boolean;
}

interface DatabaseSettings {
  auto_cleanup_enabled: boolean;
  cleanup_schedule: 'daily' | 'weekly' | 'monthly';
  cleanup_items: CleanupItems;
  revision_limit: number;
  trash_days: number;
  action_scheduler_retention_days: number;
  wpmailsmtp_log_retention_days: number;
  heartbeat_control: boolean;
  heartbeat_frequency: number;
  heartbeat_frequency_front: number;
  heartbeat_frequency_admin: number;
  heartbeat_frequency_editor: number;
}

interface DatabaseStats {
  table_count?: number;
  total_size?: string;
  revisions_count?: number;
  trash_count?: number;
  trash_posts_count?: number;
  trash_comments_count?: number;
  pending_comments_count?: number;
  spam_comments_count?: number;
  transients_count?: number;
  orphaned_meta?: number;
}

interface ServerInfo {
  php_version: string;
  memory_limit: string;
  max_execution_time: string;
  upload_max_filesize: string;
  post_max_size: string;
  opcache_enabled: boolean;
  opcache_info?: {
    enabled: boolean;
    memory_usage: string;
    hit_rate: string;
  };
  mysql_version?: string;
  mysql_type?: string;
  server_software?: string;
  object_cache_enabled?: boolean;
  object_cache_type?: string;
  object_cache_info?: {
    version?: string;
    memory?: string;
  };
  object_cache_dropin_exists?: boolean;
  object_cache_available_backends?: string[];
}

// Autoload Analysis Interfaces
interface AutoloadOption {
  name: string;
  size: number;
  reason?: string;
}

interface AutoloadGuardReport {
  last_run: string | null;
  threshold_kb: number;
  found: number;
  disabled_count: number;
  disabled: { name: string; size_kb: number }[];
}

interface AutoloadAnalysis {
  total_size: number;
  total_count: number;
  large_options: AutoloadOption[];
  suspicious_options: AutoloadOption[];
  recommendations: string[];
  guard?: AutoloadGuardReport;
}

// Table Management Interface
interface TableInfo {
  name: string;
  rows: number;
  data_size: number;
  index_size: number;
  total_size: number;
  overhead: number;
  engine: string;
  collation: string;
  created: string | null;
  updated: string | null;
  is_core: boolean;
  can_optimize: boolean;
  can_repair: boolean;
  can_convert_to_innodb?: boolean;
  conversion_note?: string;
}

// Cron Job Interface
interface CronJob {
  hook: string;
  timestamp: number;
  next_run: string;
  schedule: string;
  interval: number;
  args: Record<string, any> | any[];
  is_orphaned: boolean;
  can_delete?: boolean;
}

// Backup Interface
interface DatabaseBackup {
  filename: string;
  size: number;
  size_formatted?: string;
  created?: string;
  date?: string;
  tables?: number | string[];
  storage?: string;
  hash?: string;
}

interface DatabaseServerSettingsProps {
  onClose?: () => void;
  embedded?: boolean;
}

interface ApiSettingsResponse {
  settings: Partial<DatabaseSettings>;
}

interface ApiStatsResponse {
  stats: DatabaseStats;
}

interface ApiServerInfoResponse {
  info: ServerInfo;
}

interface CleanupResult {
  success: boolean;
  partial_success?: boolean;
  deleted: number;
  table: string | null;
  tables?: Array<{
    table: string;
    deleted: number;
    optimized: boolean;
    error?: string | null;
  }>;
  optimized: boolean;
  optimized_tables?: string[];
  error?: string | null;
}

interface ApiOptimizeResponse {
  success?: boolean;
  message?: string;
  cleaned?: number;
  tasks_run?: number;
  tables_optimized?: number;
  cleanup_results?: Record<string, CleanupResult>;
}

interface ApiAutoloadResponse {
  total_size: number;
  total_count: number;
  large_options: AutoloadOption[];
  suspicious_options: AutoloadOption[];
  recommendations: string[];
  guard?: AutoloadGuardReport;
}

const normalizeBooleanSetting = (value: unknown): boolean => {
  if (typeof value === 'boolean') {
    return value;
  }

  if (typeof value === 'number') {
    return value !== 0;
  }

  if (typeof value === 'string') {
    const normalized = value.trim().toLowerCase();

    if (['1', 'true', 'yes', 'on', 'enabled'].includes(normalized)) {
      return true;
    }

    if (['0', 'false', 'no', 'off', 'disabled', ''].includes(normalized)) {
      return false;
    }
  }

  return Boolean(value);
};

const normalizeNumberSetting = (value: unknown, fallback: number): number => {
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : fallback;
};

const normalizeDatabaseSettings = (value: Partial<DatabaseSettings>): Partial<DatabaseSettings> => ({
  ...value,
  auto_cleanup_enabled: normalizeBooleanSetting(value.auto_cleanup_enabled),
  heartbeat_control: normalizeBooleanSetting(value.heartbeat_control),
  heartbeat_frequency: normalizeNumberSetting(value.heartbeat_frequency, 60),
  heartbeat_frequency_front: normalizeNumberSetting(value.heartbeat_frequency_front, 60),
  heartbeat_frequency_admin: normalizeNumberSetting(value.heartbeat_frequency_admin, 60),
  heartbeat_frequency_editor: normalizeNumberSetting(value.heartbeat_frequency_editor, 60),
  cleanup_items: {
    revisions: normalizeBooleanSetting(value.cleanup_items?.revisions),
    drafts: normalizeBooleanSetting(value.cleanup_items?.drafts),
    trash: normalizeBooleanSetting(value.cleanup_items?.trash),
    spam_comments: normalizeBooleanSetting(value.cleanup_items?.spam_comments),
    unapproved_comments: normalizeBooleanSetting(value.cleanup_items?.unapproved_comments),
    transients: normalizeBooleanSetting(value.cleanup_items?.transients),
    expired_transients: normalizeBooleanSetting(value.cleanup_items?.expired_transients),
    orphaned_meta: normalizeBooleanSetting(value.cleanup_items?.orphaned_meta),
    action_scheduler: normalizeBooleanSetting(value.cleanup_items?.action_scheduler),
    wpmailsmtp_logs: normalizeBooleanSetting(value.cleanup_items?.wpmailsmtp_logs),
  },
});

const formatObjectCacheBackendName = (backend: string): string => {
  switch (backend) {
    case 'redis':
      return 'Redis';
    case 'memcached':
      return 'Memcached';
    case 'memcache':
      return 'Memcache';
    case 'apcu':
      return 'APCu';
    case 'external':
      return __('External', 'prorank-seo');
    default:
      return backend ? backend.charAt(0).toUpperCase() + backend.slice(1) : __('None', 'prorank-seo');
  }
};

const DatabaseServerSettings: React.FC<DatabaseServerSettingsProps> = ({
  onClose,
  embedded = false
}) => {
  const [settings, setSettings] = useState<DatabaseSettings>({
    // Database settings
    auto_cleanup_enabled: false,
    cleanup_schedule: 'weekly',
    cleanup_items: {
      revisions: true,
      drafts: false,
      trash: true,
      spam_comments: true,
      unapproved_comments: false,
      transients: true,
      expired_transients: true,
      orphaned_meta: false,
      action_scheduler: false,
      wpmailsmtp_logs: false,
    },
    revision_limit: 5,
    trash_days: 30,
    action_scheduler_retention_days: 7,
    wpmailsmtp_log_retention_days: 30,
    heartbeat_control: false,
    heartbeat_frequency: 60,
    heartbeat_frequency_front: 60,
    heartbeat_frequency_admin: 60,
    heartbeat_frequency_editor: 60,
  });

  const [loading, setLoading] = useState<boolean>(true);
  const [saving, setSaving] = useState<boolean>(false);
  const [optimizing, setOptimizing] = useState<boolean>(false);
  const [lastCleanupResult, setLastCleanupResult] = useState<ApiOptimizeResponse | null>(null);
  const [databaseStats, setDatabaseStats] = useState<DatabaseStats | null>(null);
  const [serverInfo, setServerInfo] = useState<ServerInfo | null>(null);

  // Autoload state
  const [autoloadAnalysis, setAutoloadAnalysis] = useState<AutoloadAnalysis | null>(null);
  const [tablesInfo, setTablesInfo] = useState<TableInfo[]>([]);
  const [tablesLoading, setTablesLoading] = useState(false);
  const [tableActionLoading, setTableActionLoading] = useState<string | null>(null);
  const [cronJobs, setCronJobs] = useState<CronJob[]>([]);
  const [cronLoading, setCronLoading] = useState(false);
  const [cronDeleteLoading, setCronDeleteLoading] = useState<string | null>(null);
  const [backups, setBackups] = useState<DatabaseBackup[]>([]);
  const [backupsLoading, setBackupsLoading] = useState(false);
  const [backupCreating, setBackupCreating] = useState(false);
  const [backupActionLoading, setBackupActionLoading] = useState<string | null>(null);
  const [loadingAutoload, setLoadingAutoload] = useState<boolean>(false);
  const [togglingAutoload, setTogglingAutoload] = useState<string | null>(null);
  const selectedCleanupCount = Object.values(settings.cleanup_items || {}).filter(Boolean).length;
  const objectCacheActive = Boolean(
    serverInfo?.object_cache_enabled && serverInfo?.object_cache_type && serverInfo.object_cache_type !== 'none'
  );
  const objectCacheAvailableBackends = serverInfo?.object_cache_available_backends || [];
  const objectCacheAvailable = objectCacheAvailableBackends.length > 0;
  const objectCacheBadgeBackground = objectCacheActive ? '#e8f5e9' : objectCacheAvailable ? '#fff8e1' : '#f3f4f6';
  const objectCacheBadgeColor = objectCacheActive ? '#2e7d32' : objectCacheAvailable ? '#b26a00' : '#6b7280';
  const objectCacheBadgeLabel = objectCacheActive
    ? formatObjectCacheBackendName(serverInfo?.object_cache_type || 'external')
    : objectCacheAvailable
      ? __('Available', 'prorank-seo')
      : __('Unavailable', 'prorank-seo');
  const objectCacheHelpText = objectCacheActive
    ? sprintf(
        __('Active via %1$s. %2$s', 'prorank-seo'),
        formatObjectCacheBackendName(serverInfo?.object_cache_type || 'external'),
        serverInfo?.object_cache_dropin_exists
          ? __('A valid object-cache drop-in is loaded.', 'prorank-seo')
          : __('The external object cache is active.', 'prorank-seo')
      )
    : objectCacheAvailable
      ? sprintf(
          __('%1$s is available on this server, but no active object-cache drop-in is loaded. Enable it through your hosting stack or a compatible cache plugin.', 'prorank-seo'),
          objectCacheAvailableBackends.map(formatObjectCacheBackendName).join(', ')
        )
      : __('No supported object-cache backend was detected on this server.', 'prorank-seo');

  const { showNotification } = useNotification();

  useEffect(() => {
    loadSettings();
    loadDatabaseStats();
    loadServerInfo();
    loadAutoloadAnalysis();
    loadTablesInfo();
    loadCronJobs();
    loadBackups();
  }, []);

  const loadSettings = async () => {
    try {
      const response = await apiFetch<ApiSettingsResponse>({
        path: '/prorank-seo/v1/settings/database',
      });
      if (response?.settings) {
        const normalizedSettings = normalizeDatabaseSettings(response.settings);
        // Ensure cleanup_items exists with defaults
        const mergedSettings = {
          ...settings,
          ...normalizedSettings,
          cleanup_items: {
            ...settings.cleanup_items,
            ...(normalizedSettings.cleanup_items || {})
          }
        };
        setSettings(mergedSettings);
      }
    } catch (error) {
      // Silently fail
    } finally {
      setLoading(false);
    }
  };

  const loadDatabaseStats = async () => {
    try {
      const response = await apiFetch<ApiStatsResponse>({
        path: '/prorank-seo/v1/database/stats',
      });
      if (response?.stats) {
        setDatabaseStats(response.stats);
      }
    } catch (error) {
      // Silently fail
    }
  };

  const loadServerInfo = async () => {
    try {
      const response = await apiFetch<ApiServerInfoResponse>({
        path: '/prorank-seo/v1/server/info',
      });
      if (response?.info) {
        setServerInfo(response.info);
      }
    } catch (error) {
      // Set default values if API fails
      setServerInfo({
        php_version: 'Unknown',
        memory_limit: 'Unknown',
        max_execution_time: 'Unknown',
        upload_max_filesize: 'Unknown',
        post_max_size: 'Unknown',
        opcache_enabled: false,
      });
    }
  };

  // Autoload Analysis
  const loadAutoloadAnalysis = async () => {
    setLoadingAutoload(true);
    try {
      const response = await apiFetch<ApiAutoloadResponse>({
        path: '/prorank-seo/v1/database-optimization/analyze-autoload',
      });
      if (response) {
        setAutoloadAnalysis({
          total_size: response.total_size || 0,
          total_count: response.total_count || 0,
          large_options: response.large_options || [],
          suspicious_options: response.suspicious_options || [],
          recommendations: response.recommendations || [],
          guard: response.guard,
        });
      }
    } catch (error) {
      // Silently fail - feature may not be available
    } finally {
      setLoadingAutoload(false);
    }
  };

  const toggleAutoload = async (optionName: string, currentAutoload: boolean) => {
    setTogglingAutoload(optionName);
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/option/autoload',
        method: 'POST',
        data: {
          option_name: optionName,
          autoload: currentAutoload ? 'no' : 'yes'
        },
      });
      showNotification(
        currentAutoload
          ? __('Autoload disabled for this option', 'prorank-seo')
          : __('Autoload enabled for this option', 'prorank-seo'),
        'success'
      );
      // Reload autoload analysis
      await loadAutoloadAnalysis();
    } catch (error: any) {
      showNotification(error.message || __('Failed to update autoload', 'prorank-seo'), 'error');
    } finally {
      setTogglingAutoload(null);
    }
  };



  // Load table information
  const loadTablesInfo = async () => {
    setTablesLoading(true);
    try {
      const response = await apiFetch<TableInfo[]>({
        path: '/prorank-seo/v1/database-optimization/tables',
      });
      setTablesInfo(response || []);
    } catch (error) {
      console.error('Failed to load tables info:', error);
    } finally {
      setTablesLoading(false);
    }
  };

  // Handle table operations (optimize, repair, convert)
  const handleTableOperation = async (tableName: string, operation: 'optimize' | 'repair' | 'convert') => {
    setTableActionLoading(tableName + '-' + operation);
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/table/' + operation,
        method: 'POST',
        data: { table_name: tableName },
      });
      await loadTablesInfo();
    } catch (error) {
      console.error('Table operation failed:', error);
    } finally {
      setTableActionLoading(null);
    }
  };

  // Load cron jobs
  const loadCronJobs = async () => {
    setCronLoading(true);
    try {
      const response = await apiFetch<CronJob[]>({
        path: '/prorank-seo/v1/database-optimization/cron',
      });
      setCronJobs(response || []);
    } catch (error) {
      console.error('Failed to load cron jobs:', error);
    } finally {
      setCronLoading(false);
    }
  };

  // Delete cron job
  const handleDeleteCron = async (hook: string, timestamp: number, args: Record<string, any> | any[] = []) => {
    setCronDeleteLoading(hook + '-' + timestamp);
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/cron/delete',
        method: 'DELETE',
        data: { hook, timestamp, args },
      });
      await loadCronJobs();
      showNotification(__('Orphaned cron job deleted', 'prorank-seo'), 'success');
    } catch (error) {
      console.error('Failed to delete cron job:', error);
      showNotification(__('Failed to delete cron job', 'prorank-seo'), 'error');
    } finally {
      setCronDeleteLoading(null);
    }
  };

  // Load backups list
  const loadBackups = async () => {
    setBackupsLoading(true);
    try {
      const response = await apiFetch<DatabaseBackup[]>({
        path: '/prorank-seo/v1/database-optimization/backup/list',
      });
      setBackups(response || []);
    } catch (error) {
      console.error('Failed to load backups:', error);
    } finally {
      setBackupsLoading(false);
    }
  };

  // Create backup
  const handleCreateBackup = async () => {
    setBackupCreating(true);
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/backup/create',
        method: 'POST',
      });
      await loadBackups();
    } catch (error) {
      console.error('Failed to create backup:', error);
    } finally {
      setBackupCreating(false);
    }
  };

  // Restore backup
  const handleRestoreBackup = async (filename: string) => {
    if (!confirm(__('Are you sure you want to restore this backup? This will overwrite current data.', 'prorank-seo'))) {
      return;
    }
    setBackupActionLoading(filename + '-restore');
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/backup/restore',
        method: 'POST',
        data: { backup_file: filename },
      });
      alert(__('Backup restored successfully!', 'prorank-seo'));
    } catch (error) {
      console.error('Failed to restore backup:', error);
    } finally {
      setBackupActionLoading(null);
    }
  };

  // Delete backup
  const handleDeleteBackup = async (filename: string) => {
    if (!confirm(__('Are you sure you want to delete this backup?', 'prorank-seo'))) {
      return;
    }
    setBackupActionLoading(filename + '-delete');
    try {
      await apiFetch({
        path: '/prorank-seo/v1/database-optimization/backup/delete',
        method: 'DELETE',
        data: { filename },
      });
      await loadBackups();
    } catch (error) {
      console.error('Failed to delete backup:', error);
    } finally {
      setBackupActionLoading(null);
    }
  };

  const saveSettings = async () => {
    setSaving(true);
    try {
      await apiFetch({
        path: '/prorank-seo/v1/settings/database',
        method: 'POST',
        data: { settings },
      });
      showNotification(__('Settings saved successfully', 'prorank-seo'), 'success');
    } catch (error: any) {
      showNotification(error.message || __('Failed to save settings', 'prorank-seo'), 'error');
    } finally {
      setSaving(false);
    }
  };

  const runOptimization = async () => {
    if (selectedCleanupCount === 0) {
      showNotification(
        __('Select at least one cleanup item first.', 'prorank-seo'),
        'warning'
      );
      return;
    }

    setOptimizing(true);
    setLastCleanupResult(null);
    try {
      const response = await apiFetch<ApiOptimizeResponse>({
        path: '/prorank-seo/v1/database/optimize',
        method: 'POST',
        data: { items: settings.cleanup_items || {} },
      });
      setLastCleanupResult(response);
      showNotification(
        response.message || __('Database cleanup completed successfully', 'prorank-seo'),
        (response.cleaned ?? 0) > 0 ? 'success' : 'info'
      );
      await loadDatabaseStats();
      await loadAutoloadAnalysis();
      await loadTablesInfo();
    } catch (error: any) {
      showNotification(error.message || __('Failed to run selected cleanup', 'prorank-seo'), 'error');
    } finally {
      setOptimizing(false);
    }
  };


  // Helper function to update settings
  const updateSetting = <K extends keyof DatabaseSettings>(key: K, value: DatabaseSettings[K]) => {
    setSettings(prev => ({ ...prev, [key]: value }));
  };

  const updateCleanupItem = (item: keyof CleanupItems, value: boolean) => {
    setSettings(prev => ({
      ...prev,
      cleanup_items: {
        ...prev.cleanup_items,
        [item]: value,
      }
    }));
  };

  // Helper to format bytes
  const formatBytes = (bytes: number): string => {
    if (bytes === 0) return '0 B';
    const k = 1024;
    const sizes = ['B', 'KB', 'MB', 'GB'];
    const i = Math.floor(Math.log(bytes) / Math.log(k));
    return parseFloat((bytes / Math.pow(k, i)).toFixed(2)) + ' ' + sizes[i];
  };

  // Get autoload health status
  const getAutoloadHealth = (): { status: 'healthy' | 'warning' | 'critical'; label: string; color: string } => {
    if (!autoloadAnalysis) return { status: 'healthy', label: __('Unknown', 'prorank-seo'), color: '#6b7280' };

    const sizeKB = autoloadAnalysis.total_size;
    if (sizeKB < 500) {
      return { status: 'healthy', label: __('Healthy', 'prorank-seo'), color: '#16a34a' };
    } else if (sizeKB < 800) {
      return { status: 'warning', label: __('Needs Attention', 'prorank-seo'), color: '#d97706' };
    } else {
      return { status: 'critical', label: __('Critical - May cause 502 errors', 'prorank-seo'), color: '#dc2626' };
    }
  };

  if (loading) {
    return (
      <div className="prorank-performance-container prorank-database-server-settings">
        <div className="prorank-flex-center" style={{ padding: '50px' }}>
          <Spinner />
          <p>{__('Loading database settings...', 'prorank-seo')}</p>
        </div>
      </div>
    );
  }

  const autoloadHealth = getAutoloadHealth();
  const autoloadGuard = autoloadAnalysis?.guard;
  const getCleanupTaskLabel = (type: string): string => {
    const labels: Record<string, string> = {
      revisions: __('Post Revisions', 'prorank-seo'),
      auto_drafts: __('Auto-Drafts', 'prorank-seo'),
      trash: __('Trash', 'prorank-seo'),
      spam: __('Comments Marked as Spam', 'prorank-seo'),
      pending: __('Pending / Unapproved Comments', 'prorank-seo'),
      transients: __('All Transients', 'prorank-seo'),
      expired_transients: __('Expired Transients', 'prorank-seo'),
      orphaned: __('Orphaned Meta', 'prorank-seo'),
      action_scheduler: __('Action Scheduler History', 'prorank-seo'),
      wpmailsmtp_logs: __('WP Mail SMTP Email Logs', 'prorank-seo'),
    };

    return labels[type] || type.replace(/_/g, ' ');
  };
  const getCleanupTaskStatus = (result: CleanupResult): { color: string; icon: string } => {
    if (result.success) {
      return { color: '#16a34a', icon: '\u2713' };
    }

    if (result.partial_success) {
      return { color: '#d97706', icon: '\u26a0' };
    }

    return { color: '#dc2626', icon: '\u2717' };
  };

  return (
    <div className="prorank-performance-container prorank-database-server-settings">
      {/* Unified WordPress Bloat Control (reuses existing settings/runtime).
          Replaces the former "Bloat Cleanup Preset" card, which called the
          destructive /performance/presets/bloat-cleanup endpoint (scheduled
          DB cleanup + heartbeat changes) while mislabelled "Safe preset". */}
      <BloatControlPanel />

      {/* Leftover plugin tables (report-only detection) */}
      <OrphanedTablesPanel />

      {/* Database Stats Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-chart-pie"></span>
            {__('Database Statistics', 'prorank-seo')}
          </h3>
        </div>
        <div className="prorank-card-body">
          <div className="prorank-stats-grid">
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Total Tables', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.table_count || 0}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Database Size', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.total_size || '0 MB'}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Post Revisions', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.revisions_count || 0}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Trashed Items', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.trash_count || 0}</span>
              <span className="prorank-text-muted" style={{ fontSize: '11px', marginTop: '4px' }}>
                {(databaseStats?.trash_posts_count || 0)} {__('posts', 'prorank-seo')} • {(databaseStats?.trash_comments_count || 0)} {__('comments', 'prorank-seo')}
              </span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Pending Comments', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.pending_comments_count || 0}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Comments Marked as Spam', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.spam_comments_count || 0}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Transients', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.transients_count || 0}</span>
            </div>
            <div className="prorank-stat-item">
              <span className="prorank-stat-label">{__('Orphaned Meta', 'prorank-seo')}</span>
              <span className="prorank-stat-value">{databaseStats?.orphaned_meta || 0}</span>
            </div>
          </div>
        </div>
      </div>

      {/* Autoload Management Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-performance"></span>
            {__('Autoload Management', 'prorank-seo')}
          </h3>
          <div className={`prorank-badge`} style={{ background: autoloadHealth.color + '20', color: autoloadHealth.color }}>
            {autoloadHealth.label}
          </div>
        </div>
        <div className="prorank-card-body">
          {loadingAutoload ? (
            <div className="prorank-flex-center" style={{ padding: '20px' }}>
              <Spinner />
              <span style={{ marginLeft: '10px' }}>{__('Analyzing autoload data...', 'prorank-seo')}</span>
            </div>
          ) : autoloadAnalysis ? (
            <>
              {/* Autoload Size Indicator */}
              <div style={{ marginBottom: '24px' }}>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', marginBottom: '8px' }}>
                  <span style={{ fontWeight: 500 }}>{__('Total Autoload Size', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 600, color: autoloadHealth.color }}>
                    {formatBytes(autoloadAnalysis.total_size * 1024)} / 800 KB
                  </span>
                </div>
                <div style={{
                  background: '#e5e7eb',
                  borderRadius: '8px',
                  height: '12px',
                  overflow: 'hidden',
                  position: 'relative',
                }}>
                  {/* Warning threshold marker at 500KB */}
                  <div style={{
                    position: 'absolute',
                    left: '62.5%',
                    top: 0,
                    bottom: 0,
                    width: '2px',
                    background: '#d97706',
                    opacity: 0.5,
                  }} />
                  {/* Critical threshold marker at 800KB */}
                  <div style={{
                    position: 'absolute',
                    left: '100%',
                    top: 0,
                    bottom: 0,
                    width: '2px',
                    background: '#dc2626',
                  }} />
                  <div style={{
                    background: autoloadHealth.color,
                    height: '100%',
                    width: `${Math.min((autoloadAnalysis.total_size / 800) * 100, 100)}%`,
                    borderRadius: '8px',
                    transition: 'width 0.3s ease',
                  }} />
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', marginTop: '4px', fontSize: '11px', color: '#6b7280' }}>
                  <span>0 KB</span>
                  <span style={{ color: '#d97706' }}>500 KB</span>
                  <span style={{ color: '#dc2626' }}>800 KB</span>
                </div>
              </div>

              {/* Stats Row */}
              <div style={{ display: 'flex', gap: '16px', marginBottom: '20px' }}>
                <div style={{ flex: 1, textAlign: 'center', padding: '12px', background: '#f9fafb', borderRadius: '8px' }}>
                  <div style={{ fontSize: '20px', fontWeight: 600, color: '#374151' }}>
                    {autoloadAnalysis.total_count}
                  </div>
                  <div style={{ fontSize: '12px', color: '#6b7280' }}>{__('Options', 'prorank-seo')}</div>
                </div>
                <div style={{ flex: 1, textAlign: 'center', padding: '12px', background: autoloadAnalysis.large_options.length > 0 ? '#fef3c7' : '#f0fdf4', borderRadius: '8px' }}>
                  <div style={{ fontSize: '20px', fontWeight: 600, color: autoloadAnalysis.large_options.length > 0 ? '#d97706' : '#16a34a' }}>
                    {autoloadAnalysis.large_options.length}
                  </div>
                  <div style={{ fontSize: '12px', color: '#6b7280' }}>{__('Large (>100KB)', 'prorank-seo')}</div>
                </div>
                <div style={{ flex: 1, textAlign: 'center', padding: '12px', background: autoloadAnalysis.suspicious_options.length > 0 ? '#fee2e2' : '#f0fdf4', borderRadius: '8px' }}>
                  <div style={{ fontSize: '20px', fontWeight: 600, color: autoloadAnalysis.suspicious_options.length > 0 ? '#dc2626' : '#16a34a' }}>
                    {autoloadAnalysis.suspicious_options.length}
                  </div>
                  <div style={{ fontSize: '12px', color: '#6b7280' }}>{__('Suspicious', 'prorank-seo')}</div>
                </div>
              </div>

              {/* Autoload Guard status */}
              {autoloadGuard && (
                <div className="prorank-notice prorank-notice-info" style={{ marginBottom: '16px' }}>
                  <span className="dashicons dashicons-shield"></span>
                  <div>
                    <strong>{__('Autoload Guard', 'prorank-seo')}</strong>
                    <div style={{ marginTop: '6px', fontSize: '13px' }}>
                      {autoloadGuard.last_run
                        ? `${__('Last run', 'prorank-seo')}: ${autoloadGuard.last_run}`
                        : __('Runs daily using WordPress core autoload controls to disable oversized entries safely.', 'prorank-seo')}
                    </div>
                    <div style={{ fontSize: '13px', color: '#4b5563' }}>
                      {__('Threshold', 'prorank-seo')}: {autoloadGuard.threshold_kb} KB • {__('Auto-disabled', 'prorank-seo')}: {autoloadGuard.disabled_count}
                    </div>
                    {autoloadGuard.disabled && autoloadGuard.disabled.length > 0 && (
                      <div style={{ marginTop: '6px', fontSize: '12px', color: '#4b5563' }}>
                        {__('Recent fixes:', 'prorank-seo')} {autoloadGuard.disabled.slice(0, 3).map(item => item.name).join(', ')}
                      </div>
                    )}
                  </div>
                </div>
              )}

              {/* Recommendations */}
              {autoloadAnalysis.recommendations.length > 0 && (
                <div className="prorank-notice prorank-notice-warning" style={{ marginBottom: '20px' }}>
                  <span className="dashicons dashicons-warning"></span>
                  <div>
                    <strong>{__('Recommendations:', 'prorank-seo')}</strong>
                    <ul style={{ margin: '8px 0 0 0', paddingLeft: '20px' }}>
                      {autoloadAnalysis.recommendations.map((rec, idx) => (
                        <li key={idx}>{rec}</li>
                      ))}
                    </ul>
                  </div>
                </div>
              )}

              {/* Large Options List */}
              {autoloadAnalysis.large_options.length > 0 && (
                <div style={{ marginBottom: '16px' }}>
                  <h4 style={{ marginBottom: '12px', color: '#374151' }}>
                    {__('Large Autoloaded Options', 'prorank-seo')}
                  </h4>
                  <div style={{ background: '#f9fafb', borderRadius: '8px', overflow: 'hidden' }}>
                    {autoloadAnalysis.large_options.slice(0, 5).map((option, idx) => (
                      <div
                        key={option.name}
                        style={{
                          display: 'flex',
                          justifyContent: 'space-between',
                          alignItems: 'center',
                          padding: '12px 16px',
                          borderBottom: idx < autoloadAnalysis.large_options.length - 1 ? '1px solid #e5e7eb' : 'none',
                        }}
                      >
                        <div style={{ flex: 1, minWidth: 0 }}>
                          <div style={{ fontFamily: 'monospace', fontSize: '12px', wordBreak: 'break-all' }}>
                            {option.name}
                          </div>
                          <div style={{ fontSize: '11px', color: '#6b7280', marginTop: '2px' }}>
                            {formatBytes(option.size * 1024)}
                          </div>
                        </div>
                        <ProrankButton
                          variant="ghost"
                          onClick={() => toggleAutoload(option.name, true)}
                          disabled={togglingAutoload === option.name}
                          style={{ marginLeft: '12px', fontSize: '12px', padding: '4px 8px' }}
                        >
                          {togglingAutoload === option.name ? (
                            <Spinner style={{ width: '14px', height: '14px' }} />
                          ) : (
                            __('Disable Autoload', 'prorank-seo')
                          )}
                        </ProrankButton>
                      </div>
                    ))}
                  </div>
                  {autoloadAnalysis.large_options.length > 5 && (
                    <p style={{ fontSize: '12px', color: '#6b7280', marginTop: '8px', textAlign: 'center' }}>
                      {__('And', 'prorank-seo')} {autoloadAnalysis.large_options.length - 5} {__('more...', 'prorank-seo')}
                    </p>
                  )}
                </div>
              )}

              {/* Suspicious Options List */}
              {autoloadAnalysis.suspicious_options.length > 0 && (
                <div style={{ marginBottom: '16px' }}>
                  <h4 style={{ marginBottom: '12px', color: '#374151' }}>
                    {__('Suspicious Autoloaded Options', 'prorank-seo')}
                  </h4>
                  <div style={{ background: '#fef2f2', borderRadius: '8px', overflow: 'hidden' }}>
                    {autoloadAnalysis.suspicious_options.slice(0, 5).map((option, idx) => (
                      <div
                        key={option.name}
                        style={{
                          display: 'flex',
                          justifyContent: 'space-between',
                          alignItems: 'center',
                          padding: '12px 16px',
                          borderBottom: idx < autoloadAnalysis.suspicious_options.length - 1 ? '1px solid #fecaca' : 'none',
                        }}
                      >
                        <div style={{ flex: 1, minWidth: 0 }}>
                          <div style={{ fontFamily: 'monospace', fontSize: '12px', wordBreak: 'break-all' }}>
                            {option.name}
                          </div>
                          <div style={{ fontSize: '11px', color: '#dc2626', marginTop: '2px' }}>
                            {option.reason || __('Should not be autoloaded', 'prorank-seo')}
                          </div>
                        </div>
                        <ProrankButton
                          variant="ghost"
                          onClick={() => toggleAutoload(option.name, true)}
                          disabled={togglingAutoload === option.name}
                          style={{ marginLeft: '12px', fontSize: '12px', padding: '4px 8px' }}
                        >
                          {togglingAutoload === option.name ? (
                            <Spinner style={{ width: '14px', height: '14px' }} />
                          ) : (
                            __('Disable', 'prorank-seo')
                          )}
                        </ProrankButton>
                      </div>
                    ))}
                  </div>
                </div>
              )}

              {/* Refresh Button */}
              <div className="prorank-actions" style={{ marginTop: '16px' }}>
                <ProrankButton
                  variant="ghost"
                  onClick={loadAutoloadAnalysis}
                  disabled={loadingAutoload}
                >
                  <span className="dashicons dashicons-update" style={{ marginRight: '6px' }}></span>
                  {__('Refresh Analysis', 'prorank-seo')}
                </ProrankButton>
              </div>
            </>
          ) : (
            <div className="prorank-notice prorank-notice-info">
              <span className="dashicons dashicons-info"></span>
              <div>
                {__('Autoload analysis is not available. Please check that the database optimization module is enabled.', 'prorank-seo')}
              </div>
            </div>
          )}
        </div>
      </div>

      {/* Database Cleanup Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-database"></span>
            {__('Database Cleanup', 'prorank-seo')}
          </h3>
        </div>
        <div className="prorank-card-body">
          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Automatic Cleanup', 'prorank-seo')}</strong>
              <span>{__('Schedule regular cleanup of the selected WordPress data types', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.auto_cleanup_enabled}
              onChange={(value) => updateSetting('auto_cleanup_enabled', value)}
              label=""
            />
          </div>

          {settings.auto_cleanup_enabled && (
            <div className="prorank-control-group">
              <SelectControl
              __next40pxDefaultSize
              __nextHasNoMarginBottom
                label={__('Cleanup Schedule', 'prorank-seo')}
                value={settings.cleanup_schedule}
                options={[
                  { label: __('Daily', 'prorank-seo'), value: 'daily' },
                  { label: __('Weekly', 'prorank-seo'), value: 'weekly' },
                  { label: __('Monthly', 'prorank-seo'), value: 'monthly' },
                ]}
                onChange={(value: any) => updateSetting('cleanup_schedule', value)}
              />
            </div>
          )}

          <h4 style={{ marginTop: '20px', marginBottom: '15px' }}>
            {__('Cleanup Items', 'prorank-seo')}
          </h4>

          <p className="prorank-text-muted" style={{ marginTop: 0, marginBottom: '16px' }}>
            {__('Run only the selected cleanup tasks. Any WordPress tables that change are optimized automatically after deletion.', 'prorank-seo')}
          </p>

          <div className="prorank-notice prorank-notice-info" style={{ marginBottom: '16px' }}>
            <span className="dashicons dashicons-info"></span>
            <div>
              {__('Comment cleanup follows native WordPress moderation states. Spam comments and pending / unapproved comments are separate cleanup tasks.', 'prorank-seo')}
            </div>
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Post Revisions', 'prorank-seo')}</strong>
              <span>{__('Remove old post revisions', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.revisions}
              onChange={(value) => updateCleanupItem('revisions', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Auto-Drafts', 'prorank-seo')}</strong>
              <span>{__('Remove auto-saved drafts', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.drafts}
              onChange={(value) => updateCleanupItem('drafts', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Trash', 'prorank-seo')}</strong>
              <span>{__('Empty trash for posts, pages, and comments', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.trash}
              onChange={(value) => updateCleanupItem('trash', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Comments Marked as Spam', 'prorank-seo')}</strong>
              <span>{__('Delete comments currently marked as spam by WordPress', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.spam_comments}
              onChange={(value) => updateCleanupItem('spam_comments', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Pending / Unapproved Comments', 'prorank-seo')}</strong>
              <span>{__('Delete comments waiting for moderation approval', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.unapproved_comments}
              onChange={(value) => updateCleanupItem('unapproved_comments', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Expired Transients', 'prorank-seo')}</strong>
              <span>{__('Remove expired temporary data', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.expired_transients}
              onChange={(value) => updateCleanupItem('expired_transients', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('All Transients', 'prorank-seo')}</strong>
              <span>{__('Remove all temporary data', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.transients}
              onChange={(value) => updateCleanupItem('transients', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Orphaned Meta', 'prorank-seo')}</strong>
              <span>{__('Clean up unused metadata and broken term relationships', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.orphaned_meta}
              onChange={(value) => updateCleanupItem('orphaned_meta', value)}
              label=""
            />
          </div>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Action Scheduler History', 'prorank-seo')}</strong>
              <span>{__('Delete completed, failed, and canceled Action Scheduler jobs plus their logs after the retention window.', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.action_scheduler}
              onChange={(value) => updateCleanupItem('action_scheduler', value)}
              label=""
            />
          </div>

          {settings.cleanup_items.action_scheduler && (
            <div className="prorank-control-group">
              <label style={{ display: 'block', fontWeight: 500, marginBottom: '8px' }}>
                {__('Action Scheduler Retention (days)', 'prorank-seo')}
              </label>
              <input
                type="number"
                min={1}
                max={365}
                value={settings.action_scheduler_retention_days}
                onChange={(event) => updateSetting('action_scheduler_retention_days', Math.max(1, Math.min(365, parseInt(event.target.value || '7', 10) || 7)))}
                style={{ width: '120px' }}
              />
            </div>
          )}

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('WP Mail SMTP Email Logs', 'prorank-seo')}</strong>
              <span>{__('Delete WP Mail SMTP email log rows after the retention window.', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.cleanup_items.wpmailsmtp_logs}
              onChange={(value) => updateCleanupItem('wpmailsmtp_logs', value)}
              label=""
            />
          </div>

          {settings.cleanup_items.wpmailsmtp_logs && (
            <div className="prorank-control-group">
              <label style={{ display: 'block', fontWeight: 500, marginBottom: '8px' }}>
                {__('WP Mail SMTP Log Retention (days)', 'prorank-seo')}
              </label>
              <input
                type="number"
                min={1}
                max={365}
                value={settings.wpmailsmtp_log_retention_days}
                onChange={(event) => updateSetting('wpmailsmtp_log_retention_days', Math.max(1, Math.min(365, parseInt(event.target.value || '30', 10) || 30)))}
                style={{ width: '120px' }}
              />
            </div>
          )}

          <div className="prorank-actions" style={{ marginTop: '20px' }}>
            <ProrankButton
              variant="secondary"
              onClick={runOptimization}
              disabled={optimizing || selectedCleanupCount === 0}
            >
              {optimizing ? __('Running Cleanup...', 'prorank-seo') : __('Run Selected Cleanup', 'prorank-seo')}
            </ProrankButton>
            {selectedCleanupCount === 0 && (
              <span className="prorank-text-muted" style={{ marginLeft: '10px', fontSize: '12px' }}>
                {__('Select at least one cleanup item first.', 'prorank-seo')}
              </span>
            )}
          </div>

          {lastCleanupResult && (
            <div style={{ marginTop: '16px', padding: '16px', background: (lastCleanupResult.cleaned ?? 0) > 0 ? '#f0fdf4' : '#f8fafc', borderRadius: '8px', border: `1px solid ${(lastCleanupResult.cleaned ?? 0) > 0 ? '#bbf7d0' : '#e2e8f0'}` }}>
              {lastCleanupResult.message && (
                <div style={{ marginBottom: '12px', fontWeight: 600, color: '#111827' }}>
                  {lastCleanupResult.message}
                </div>
              )}
              <div style={{ display: 'flex', gap: '24px', marginBottom: '12px', flexWrap: 'wrap' }}>
                <div>
                  <div style={{ fontSize: '20px', fontWeight: 700, color: '#111827' }}>{lastCleanupResult.cleaned ?? 0}</div>
                  <div style={{ fontSize: '11px', color: '#6b7280' }}>{__('Rows deleted', 'prorank-seo')}</div>
                </div>
                <div>
                  <div style={{ fontSize: '20px', fontWeight: 700, color: '#111827' }}>{lastCleanupResult.tasks_run ?? 0}</div>
                  <div style={{ fontSize: '11px', color: '#6b7280' }}>{__('Tasks run', 'prorank-seo')}</div>
                </div>
                <div>
                  <div style={{ fontSize: '20px', fontWeight: 700, color: '#111827' }}>{lastCleanupResult.tables_optimized ?? 0}</div>
                  <div style={{ fontSize: '11px', color: '#6b7280' }}>{__('Tables optimized', 'prorank-seo')}</div>
                </div>
              </div>
              {lastCleanupResult.cleanup_results && (
                <div style={{ fontSize: '12px', color: '#374151' }}>
                  {Object.entries(lastCleanupResult.cleanup_results).map(([type, result]) => (
                    <div key={type} style={{ padding: '8px 0', borderTop: '1px solid #e5e7eb' }}>
                      <div style={{ display: 'flex', alignItems: 'center', gap: '8px' }}>
                        <span style={{ color: getCleanupTaskStatus(result).color, fontWeight: 600 }}>
                          {getCleanupTaskStatus(result).icon}
                        </span>
                        <span style={{ fontWeight: 600 }}>{getCleanupTaskLabel(type)}</span>
                        <span style={{ color: '#6b7280' }}>
                          {result.deleted > 0
                            ? `${result.deleted} ${__('rows deleted', 'prorank-seo')}${(result.tables?.length || 0) > 1 ? ` ${__('across', 'prorank-seo')} ${result.tables?.length} ${__('tables', 'prorank-seo')}` : ''}`
                            : result.partial_success
                              ? __('Some tables failed to clean', 'prorank-seo')
                              : __('No matching records', 'prorank-seo')}
                        </span>
                      </div>
                      {result.tables && result.tables.length > 0 && (
                        <div style={{ marginTop: '6px', paddingLeft: '20px' }}>
                          {result.tables.map((tableResult) => (
                            <div key={`${type}-${tableResult.table}`} style={{ padding: '2px 0', color: '#6b7280' }}>
                              <code style={{ fontSize: '11px' }}>{tableResult.table}</code>
                              {': '}
                              {tableResult.error
                                ? tableResult.error
                                : tableResult.deleted > 0
                                  ? `${tableResult.deleted} ${__('rows', 'prorank-seo')}${tableResult.optimized ? ` • ${__('optimized', 'prorank-seo')}` : ''}`
                                  : __('No matching records', 'prorank-seo')}
                            </div>
                          ))}
                        </div>
                      )}
                      {result.error && !result.tables?.some((tableResult) => tableResult.error === result.error) && (
                        <div style={{ marginTop: '4px', paddingLeft: '20px', color: '#dc2626' }}>
                          {result.error}
                        </div>
                      )}
                    </div>
                  ))}
                </div>
              )}
            </div>
          )}
        </div>
      </div>

      {/* Table Management Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-database-view"></span>
            {__('Table Management', 'prorank-seo')}
          </h3>
          <button
            className="prorank-btn prorank-btn-secondary prorank-btn-sm"
            onClick={loadTablesInfo}
            disabled={tablesLoading}
          >
            {tablesLoading ? __('Loading...', 'prorank-seo') : __('Refresh', 'prorank-seo')}
          </button>
        </div>
        <div className="prorank-card-body">
          {tablesLoading ? (
            <div className="prorank-loading">
              <span className="dashicons dashicons-update spin"></span>
              {__('Loading tables...', 'prorank-seo')}
            </div>
          ) : tablesInfo.length === 0 ? (
            <p>{__('No tables found.', 'prorank-seo')}</p>
          ) : (
            <>
              <p className="prorank-text-muted" style={{ marginTop: 0, marginBottom: '12px' }}>
                {__('Use Optimize to reclaim table overhead. InnoDB conversion is only offered for legacy disk-based engines such as MyISAM or Aria.', 'prorank-seo')}
              </p>
              <p className="prorank-text-muted" style={{ marginBottom: '15px' }}>
                {__('Total tables:', 'prorank-seo')} <strong>{tablesInfo.length}</strong> | 
                {__('Total size:', 'prorank-seo')} <strong>{tablesInfo.reduce((sum, t) => sum + t.total_size, 0).toFixed(2)} MB</strong>
              </p>
              <div className="prorank-table-wrapper" style={{ maxHeight: '400px', overflow: 'auto' }}>
                <table className="prorank-table prorank-table-sm">
                  <thead>
                    <tr>
                      <th>{__('Table', 'prorank-seo')}</th>
                      <th>{__('Rows', 'prorank-seo')}</th>
                      <th>{__('Size', 'prorank-seo')}</th>
                      <th>{__('Engine', 'prorank-seo')}</th>
                      <th>{__('Overhead', 'prorank-seo')}</th>
                      <th>{__('Actions', 'prorank-seo')}</th>
                    </tr>
                  </thead>
                  <tbody>
                    {tablesInfo.map((table) => (
                      <tr key={table.name}>
                        <td>
                          <code style={{ fontSize: '11px' }}>{table.name}</code>
                        </td>
                        <td>{table.rows.toLocaleString()}</td>
                        <td>{table.total_size.toFixed(2)} MB</td>
                        <td>
                          <span className={table.engine === 'InnoDB' ? 'prorank-badge prorank-badge-success' : 'prorank-badge prorank-badge-warning'}>
                            {table.engine}
                          </span>
                        </td>
                        <td>
                          {table.overhead > 0 ? (
                            <span className="prorank-text-warning">{table.overhead.toFixed(2)} MB</span>
                          ) : (
                            <span className="prorank-text-muted">-</span>
                          )}
                        </td>
                        <td>
                          <div style={{ display: 'flex', gap: '5px' }}>
                            {table.can_optimize && (
                              <button
                                className="prorank-btn prorank-btn-xs prorank-btn-primary"
                                onClick={() => handleTableOperation(table.name, 'optimize')}
                                disabled={tableActionLoading === table.name + '-optimize'}
                                title={__('Optimize table to reclaim space', 'prorank-seo')}
                              >
                                {tableActionLoading === table.name + '-optimize' ? '...' : __('Optimize', 'prorank-seo')}
                              </button>
                            )}
                            {table.can_repair && (
                              <button
                                className="prorank-btn prorank-btn-xs prorank-btn-secondary"
                                onClick={() => handleTableOperation(table.name, 'repair')}
                                disabled={tableActionLoading === table.name + '-repair'}
                                title={__('Repair table', 'prorank-seo')}
                              >
                                {tableActionLoading === table.name + '-repair' ? '...' : __('Repair', 'prorank-seo')}
                              </button>
                            )}
                            {table.can_convert_to_innodb && (
                              <button
                                className="prorank-btn prorank-btn-xs prorank-btn-warning"
                                onClick={() => handleTableOperation(table.name, 'convert')}
                                disabled={tableActionLoading === table.name + '-convert'}
                                title={table.conversion_note || __('Convert this legacy engine to InnoDB', 'prorank-seo')}
                              >
                                {tableActionLoading === table.name + '-convert' ? '...' : __('Convert to InnoDB', 'prorank-seo')}
                              </button>
                            )}
                            {!table.can_optimize && !table.can_repair && !table.can_convert_to_innodb && (
                              <span
                                className="prorank-text-muted"
                                style={{ fontSize: '11px' }}
                                title={table.conversion_note || ''}
                              >
                                {table.engine === 'InnoDB' ? __('Healthy', 'prorank-seo') : __('Keep Engine', 'prorank-seo')}
                              </span>
                            )}
                          </div>
                        </td>
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>
            </>
          )}
        </div>
      </div>

      {/* Cron Job Manager Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-clock"></span>
            {__('Cron Job Manager', 'prorank-seo')}
          </h3>
          <button
            className="prorank-btn prorank-btn-secondary prorank-btn-sm"
            onClick={loadCronJobs}
            disabled={cronLoading}
          >
            {cronLoading ? __('Loading...', 'prorank-seo') : __('Refresh', 'prorank-seo')}
          </button>
        </div>
        <div className="prorank-card-body">
          <p className="prorank-text-muted" style={{ marginBottom: '15px' }}>
            {__('Only orphaned cron events can be deleted here. Active jobs stay read-only so you can inspect schedules without removing live automations.', 'prorank-seo')}
          </p>
          {cronLoading ? (
            <div className="prorank-loading">
              <span className="dashicons dashicons-update spin"></span>
              {__('Loading cron jobs...', 'prorank-seo')}
            </div>
          ) : cronJobs.length === 0 ? (
            <p>{__('No scheduled cron jobs found.', 'prorank-seo')}</p>
          ) : (
            <>
              <p className="prorank-text-muted" style={{ marginBottom: '15px' }}>
                {__('Scheduled tasks:', 'prorank-seo')} <strong>{cronJobs.length}</strong>
                {cronJobs.filter(c => c.is_orphaned).length > 0 && (
                  <span className="prorank-text-warning"> | {__('Orphaned:', 'prorank-seo')} <strong>{cronJobs.filter(c => c.is_orphaned).length}</strong></span>
                )}
              </p>
              <div className="prorank-table-wrapper" style={{ maxHeight: '350px', overflow: 'auto' }}>
                <table className="prorank-table prorank-table-sm">
                  <thead>
                    <tr>
                      <th>{__('Hook', 'prorank-seo')}</th>
                      <th>{__('Schedule', 'prorank-seo')}</th>
                      <th>{__('Next Run', 'prorank-seo')}</th>
                      <th>{__('Status', 'prorank-seo')}</th>
                      <th>{__('Actions', 'prorank-seo')}</th>
                    </tr>
                  </thead>
                  <tbody>
                    {cronJobs.map((cron, idx) => (
                      <tr key={cron.hook + '-' + cron.timestamp + '-' + idx}>
                        <td>
                          <code style={{ fontSize: '11px' }}>{cron.hook}</code>
                        </td>
                        <td>
                          <span className="prorank-badge prorank-badge-info">
                            {cron.schedule || __('Single', 'prorank-seo')}
                          </span>
                        </td>
                        <td style={{ fontSize: '12px' }}>{cron.next_run}</td>
                        <td>
                          {cron.is_orphaned ? (
                            <span className="prorank-badge prorank-badge-warning">{__('Orphaned', 'prorank-seo')}</span>
                          ) : (
                            <span className="prorank-badge prorank-badge-success">{__('Active', 'prorank-seo')}</span>
                          )}
                        </td>
                        <td>
                          {cron.can_delete && (
                            <button
                              className="prorank-btn prorank-btn-xs prorank-btn-danger"
                              onClick={() => handleDeleteCron(cron.hook, cron.timestamp, cron.args || [])}
                              disabled={cronDeleteLoading === cron.hook + '-' + cron.timestamp}
                              title={__('Delete orphaned cron job', 'prorank-seo')}
                            >
                              {cronDeleteLoading === cron.hook + '-' + cron.timestamp ? '...' : __('Delete', 'prorank-seo')}
                            </button>
                          )}
                        </td>
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>
            </>
          )}
        </div>
      </div>

      {/* Database Backup Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-backup"></span>
            {__('Database Backup', 'prorank-seo')}
          </h3>
          <div style={{ display: 'flex', gap: '10px' }}>
            <button
              className="prorank-btn prorank-btn-secondary prorank-btn-sm"
              onClick={loadBackups}
              disabled={backupsLoading}
            >
              {backupsLoading ? __('Loading...', 'prorank-seo') : __('Refresh', 'prorank-seo')}
            </button>
            <button
              className="prorank-btn prorank-btn-primary prorank-btn-sm"
              onClick={handleCreateBackup}
              disabled={backupCreating}
            >
              {backupCreating ? __('Creating...', 'prorank-seo') : __('Create Backup', 'prorank-seo')}
            </button>
          </div>
        </div>
        <div className="prorank-card-body">
          <p className="prorank-text-muted" style={{ marginBottom: '15px' }}>
            {__('Create local SQL snapshots stored in a secure, non-public folder inside wp-content. Restoring a backup overwrites the current database, and ProRank keeps the 10 most recent backups automatically.', 'prorank-seo')}
          </p>
          {backupsLoading ? (
            <div className="prorank-loading">
              <span className="dashicons dashicons-update spin"></span>
              {__('Loading backups...', 'prorank-seo')}
            </div>
          ) : backups.length === 0 ? (
            <div className="prorank-notice prorank-notice-info">
              <span className="dashicons dashicons-info"></span>
              {__('No backups found. Click "Create Backup" to create your first backup.', 'prorank-seo')}
            </div>
          ) : (
            <div className="prorank-table-wrapper">
              <table className="prorank-table prorank-table-sm">
                <thead>
                  <tr>
                    <th>{__('Filename', 'prorank-seo')}</th>
                    <th>{__('Size', 'prorank-seo')}</th>
                    <th>{__('Created', 'prorank-seo')}</th>
                    <th>{__('Actions', 'prorank-seo')}</th>
                  </tr>
                </thead>
                <tbody>
                  {backups.map((backup) => (
                    <tr key={backup.filename}>
                      <td>
                        <code style={{ fontSize: '11px' }}>{backup.filename}</code>
                      </td>
                      <td>{backup.size_formatted || formatBytes(backup.size || 0)}</td>
                      <td style={{ fontSize: '12px' }}>{backup.created || backup.date || ''}</td>
                      <td>
                        <div style={{ display: 'flex', gap: '5px' }}>
                          <button
                            className="prorank-btn prorank-btn-xs prorank-btn-warning"
                            onClick={() => handleRestoreBackup(backup.filename)}
                            disabled={backupActionLoading === backup.filename + '-restore'}
                            title={__('Restore this backup', 'prorank-seo')}
                          >
                            {backupActionLoading === backup.filename + '-restore' ? '...' : __('Restore', 'prorank-seo')}
                          </button>
                          <button
                            className="prorank-btn prorank-btn-xs prorank-btn-danger"
                            onClick={() => handleDeleteBackup(backup.filename)}
                            disabled={backupActionLoading === backup.filename + '-delete'}
                            title={__('Delete this backup', 'prorank-seo')}
                          >
                            {backupActionLoading === backup.filename + '-delete' ? '...' : __('Delete', 'prorank-seo')}
                          </button>
                        </div>
                      </td>
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          )}
        </div>
      </div>

      {/* Server Info Card - Redesigned */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-dashboard"></span>
            {__('Server Information', 'prorank-seo')}
          </h3>
        </div>
        <div className="prorank-card-body">
          <div style={{ display: 'grid', gridTemplateColumns: 'repeat(auto-fit, minmax(280px, 1fr))', gap: '20px' }}>
            {/* PHP Section */}
            <div style={{ background: '#f8f9fa', borderRadius: '8px', padding: '16px' }}>
              <div style={{ display: 'flex', alignItems: 'center', marginBottom: '12px', borderBottom: '1px solid #e2e4e7', paddingBottom: '8px' }}>
                <span className="dashicons dashicons-editor-code" style={{ color: '#8892BF', marginRight: '8px' }}></span>
                <strong style={{ fontSize: '14px', color: '#1e1e1e' }}>{__('PHP Environment', 'prorank-seo')}</strong>
              </div>
              <div style={{ display: 'flex', flexDirection: 'column', gap: '8px' }}>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Version', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px', background: '#e3f2fd', color: '#1565c0', padding: '2px 8px', borderRadius: '4px' }}>
                    {serverInfo?.php_version || 'Unknown'}
                  </span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Memory Limit', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>{serverInfo?.memory_limit || 'Unknown'}</span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Max Execution', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>{serverInfo?.max_execution_time || 'Unknown'}</span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Upload Limit', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>{serverInfo?.upload_max_filesize || 'Unknown'}</span>
                </div>
              </div>
            </div>

            {/* Database Section */}
            <div style={{ background: '#f8f9fa', borderRadius: '8px', padding: '16px' }}>
              <div style={{ display: 'flex', alignItems: 'center', marginBottom: '12px', borderBottom: '1px solid #e2e4e7', paddingBottom: '8px' }}>
                <span className="dashicons dashicons-database" style={{ color: '#00758f', marginRight: '8px' }}></span>
                <strong style={{ fontSize: '14px', color: '#1e1e1e' }}>{__('Database', 'prorank-seo')}</strong>
              </div>
              <div style={{ display: 'flex', flexDirection: 'column', gap: '8px' }}>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Engine', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px', background: '#e8f5e9', color: '#2e7d32', padding: '2px 8px', borderRadius: '4px' }}>
                    {serverInfo?.mysql_type || 'MySQL'}
                  </span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Version', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>
                    {serverInfo?.mysql_version ? serverInfo.mysql_version.split('-')[0] : 'Unknown'}
                  </span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Tables', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>{databaseStats?.table_count || '0'}</span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Total Size', 'prorank-seo')}</span>
                  <span style={{ fontWeight: 500, fontSize: '13px' }}>{databaseStats?.total_size || '0 MB'}</span>
                </div>
              </div>
            </div>

            {/* Cache Section */}
            <div style={{ background: '#f8f9fa', borderRadius: '8px', padding: '16px' }}>
              <div style={{ display: 'flex', alignItems: 'center', marginBottom: '12px', borderBottom: '1px solid #e2e4e7', paddingBottom: '8px' }}>
                <span className="dashicons dashicons-performance" style={{ color: '#f57c00', marginRight: '8px' }}></span>
                <strong style={{ fontSize: '14px', color: '#1e1e1e' }}>{__('Caching', 'prorank-seo')}</strong>
              </div>
              <div style={{ display: 'flex', flexDirection: 'column', gap: '8px' }}>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('OPcache', 'prorank-seo')}</span>
                  <span style={{
                    fontWeight: 500,
                    fontSize: '12px',
                    background: serverInfo?.opcache_enabled ? '#e8f5e9' : '#fff3e0',
                    color: serverInfo?.opcache_enabled ? '#2e7d32' : '#ef6c00',
                    padding: '2px 8px',
                    borderRadius: '4px',
                    display: 'flex',
                    alignItems: 'center',
                    gap: '4px'
                  }}>
                    <span className={`dashicons dashicons-${serverInfo?.opcache_enabled ? 'yes' : 'warning'}`} style={{ fontSize: '14px' }}></span>
                    {serverInfo?.opcache_enabled ? __('Enabled', 'prorank-seo') : __('Disabled', 'prorank-seo')}
                  </span>
                </div>
                <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center' }}>
                  <span style={{ color: '#757575', fontSize: '13px' }}>{__('Object Cache', 'prorank-seo')}</span>
                  <span style={{
                    fontWeight: 500,
                    fontSize: '12px',
                    background: objectCacheBadgeBackground,
                    color: objectCacheBadgeColor,
                    padding: '2px 8px',
                    borderRadius: '4px',
                    display: 'flex',
                    alignItems: 'center',
                    gap: '4px'
                  }}>
                    <span className={`dashicons dashicons-${objectCacheActive ? 'yes' : objectCacheAvailable ? 'warning' : 'minus'}`} style={{ fontSize: '14px' }}></span>
                    {objectCacheBadgeLabel}
                  </span>
                </div>
                <p style={{ margin: '4px 0 0', color: '#757575', fontSize: '12px', lineHeight: 1.5 }}>
                  {objectCacheHelpText}
                </p>
              </div>
            </div>
          </div>
        </div>
      </div>

      {/* Server Optimization Card */}
      <div className="prorank-card">
        <div className="prorank-card-header">
          <h3>
            <span className="dashicons dashicons-performance"></span>
            {__('Server Optimization', 'prorank-seo')}
          </h3>
        </div>
        <div className="prorank-card-body">
          <p className="prorank-field-help" style={{ marginTop: 0, marginBottom: '16px' }}>
            {__('Heartbeat Control is the active server-side optimization on this screen. It reduces admin AJAX churn without relying on host-specific behavior.', 'prorank-seo')}
          </p>

          <div className="prorank-toggle-row">
            <div className="prorank-toggle-label">
              <strong>{__('Heartbeat Control', 'prorank-seo')}</strong>
              <span>{__('Reduce admin-ajax.php requests', 'prorank-seo')}</span>
            </div>
            <ProrankToggleSlider
              checked={settings.heartbeat_control}
              onChange={(value) => updateSetting('heartbeat_control', value)}
              label=""
            />
          </div>

          {settings.heartbeat_control && (
            <div className="prorank-control-group">
              <SelectControl
                __next40pxDefaultSize
                __nextHasNoMarginBottom
                label={__('Default Heartbeat Frequency', 'prorank-seo')}
                value={String(settings.heartbeat_frequency)}
                options={[
                  { label: __('15 seconds', 'prorank-seo'), value: '15' },
                  { label: __('30 seconds', 'prorank-seo'), value: '30' },
                  { label: __('60 seconds', 'prorank-seo'), value: '60' },
                  { label: __('120 seconds', 'prorank-seo'), value: '120' },
                  { label: __('300 seconds', 'prorank-seo'), value: '300' },
                ]}
                onChange={(value) => updateSetting('heartbeat_frequency', parseInt(value, 10) || 60)}
              />
              <SelectControl
                __next40pxDefaultSize
                __nextHasNoMarginBottom
                label={__('Frontend Heartbeat', 'prorank-seo')}
                value={String(settings.heartbeat_frequency_front)}
                help={__('Use disabled only for public pages where no front-end forms or live widgets rely on Heartbeat.', 'prorank-seo')}
                options={[
                  { label: __('Disabled', 'prorank-seo'), value: '0' },
                  { label: __('15 seconds', 'prorank-seo'), value: '15' },
                  { label: __('30 seconds', 'prorank-seo'), value: '30' },
                  { label: __('60 seconds', 'prorank-seo'), value: '60' },
                  { label: __('120 seconds', 'prorank-seo'), value: '120' },
                  { label: __('300 seconds', 'prorank-seo'), value: '300' },
                ]}
                onChange={(value) => updateSetting('heartbeat_frequency_front', parseInt(value, 10) || 0)}
              />
              <SelectControl
                __next40pxDefaultSize
                __nextHasNoMarginBottom
                label={__('Admin Dashboard Heartbeat', 'prorank-seo')}
                value={String(settings.heartbeat_frequency_admin)}
                options={[
                  { label: __('Disabled', 'prorank-seo'), value: '0' },
                  { label: __('15 seconds', 'prorank-seo'), value: '15' },
                  { label: __('30 seconds', 'prorank-seo'), value: '30' },
                  { label: __('60 seconds', 'prorank-seo'), value: '60' },
                  { label: __('120 seconds', 'prorank-seo'), value: '120' },
                  { label: __('300 seconds', 'prorank-seo'), value: '300' },
                ]}
                onChange={(value) => updateSetting('heartbeat_frequency_admin', parseInt(value, 10) || 0)}
              />
              <SelectControl
                __next40pxDefaultSize
                __nextHasNoMarginBottom
                label={__('Post Editor Heartbeat', 'prorank-seo')}
                value={String(settings.heartbeat_frequency_editor)}
                help={__('Editor heartbeat cannot be disabled; WordPress uses it for autosave and lock checks.', 'prorank-seo')}
                options={[
                  { label: __('15 seconds', 'prorank-seo'), value: '15' },
                  { label: __('30 seconds', 'prorank-seo'), value: '30' },
                  { label: __('60 seconds', 'prorank-seo'), value: '60' },
                  { label: __('120 seconds', 'prorank-seo'), value: '120' },
                  { label: __('300 seconds', 'prorank-seo'), value: '300' },
                ]}
                onChange={(value) => updateSetting('heartbeat_frequency_editor', parseInt(value, 10) || 60)}
              />
            </div>
          )}
        </div>
      </div>

      {/* Save Button */}
      <div className="prorank-actions prorank-actions-center prorank-mt-32">
        <ProrankButton
          variant="primary"
          onClick={saveSettings}
          disabled={saving}
          style={{ minWidth: '200px' }}
        >
          {saving ? __('Saving...', 'prorank-seo') : __('Save Settings', 'prorank-seo')}
        </ProrankButton>
      </div>
    </div>
  );
};

export default DatabaseServerSettings;
