frontend/components/DBManager.tsx

import { useState, useEffect } from "react";
import BrandLoader from "./BrandLoader";
import { Language, translations } from "../lib/translations";
import { clearDatabase, fetchDetailedDbStats, syncDatabase } from "../lib/api";

type Props = {
  onFilterClick?: (type: "sector" | "region" | "buyer", value: string) => void;
  lang: Language;
};

export default function DBManager({ onFilterClick, lang }: Props) {
  const t = translations[lang];
  const [stats, setStats] = useState<any>(null);
  const [isLoading, setIsLoading] = useState(true);
  const [isActionInProgress, setIsActionInProgress] = useState(false);
  const [message, setMessage] = useState<{ type: 'success' | 'error', text: string } | null>(null);

  const loadStats = async () => {
    setIsLoading(true);
    const data = await fetchDetailedDbStats();
    setStats(data);
    setIsLoading(false);
  };

  useEffect(() => {
    loadStats();
  }, []);

  const handleSync = async () => {
    setIsActionInProgress(true);
    setMessage(null);
    try {
      const result = await syncDatabase();
      setMessage({
        type: 'success',
        text: `Sync complete! New: ${result.tenders?.new || 0} tenders, ${result.purchase_orders?.new || 0} OCs.`
      });
      await loadStats();
    } catch (e) {
      setMessage({ type: 'error', text: 'Synchronization failed.' });
    } finally {
      setIsActionInProgress(false);
    }
  };

  const handleClear = async () => {
    if (!confirm("Are you sure you want to delete ALL local tenders and purchase orders? This cannot be undone.")) return;

    setIsActionInProgress(true);
    setMessage(null);
    try {
      await clearDatabase();
      setMessage({ type: 'success', text: 'Local database cleared successfully.' });
      await loadStats();
    } catch (e) {
      setMessage({ type: 'error', text: 'Failed to clear database.' });
    } finally {
      setIsActionInProgress(false);
    }
  };

  if (isLoading) return (
    <div className="flex items-center justify-center min-h-[400px]">
      <div className="animate-spin rounded-full h-12 w-12 border-t-2 border-b-2 border-cyan"></div>
    </div>
  );

  return (
    <div className="space-y-8 animate-in fade-in slide-in-from-bottom-4 duration-500">
      {isActionInProgress && <BrandLoader />}

      <div className="flex flex-col md:flex-row md:items-center justify-between gap-6">
        <div>
          <h2 className="text-3xl font-black text-white tracking-tight">{t.databaseTitle}</h2>
          <p className="text-slate-400 mt-1">{t.databaseDesc}</p>
        </div>

        <div className="flex items-center gap-3">
          <button
            onClick={handleSync}
            disabled={isActionInProgress}
            className="px-6 py-3 rounded-2xl bg-cyan text-slate-950 font-bold hover:bg-sky transition-all active:scale-95 disabled:opacity-50 flex items-center gap-2"
          >
            <span>🔄 {lang === 'es' ? 'Sincronizar Todo' : 'Sync Everything'}</span>
          </button>
          <button
            onClick={handleClear}
            disabled={isActionInProgress}
            className="px-6 py-3 rounded-2xl bg-red-500/10 border border-red-500/30 text-red-400 font-bold hover:bg-red-500/20 transition-all active:scale-95 disabled:opacity-50 flex items-center gap-2"
          >
            <span>🗑️ {t.cleanDatabase}</span>
          </button>
        </div>
      </div>

      {message && (
        <div className={`p-4 rounded-2xl border ${message.type === 'success' ? 'bg-green-500/10 border-green-500/30 text-green-400' : 'bg-red-500/10 border-red-500/30 text-red-400'} animate-in zoom-in-95 duration-300`}>
          {message.text}
        </div>
      )}

      {/* Stats Grid */}
      <div className="grid grid-cols-1 md:grid-cols-3 gap-6">
        <div className="glass-card p-8 border border-white/5 bg-white/[0.02] rounded-3xl relative overflow-hidden group">
          <div className="absolute top-0 right-0 p-4 text-4xl opacity-10 group-hover:scale-110 transition-transform">📄</div>
          <p className="text-xs font-black uppercase tracking-[0.2em] text-slate-500 mb-2">{t.tenderCount}</p>
          <h3 className="text-5xl font-black text-white">{stats?.total_records || 0}</h3>
          <p className="text-[10px] text-cyan mt-4 font-mono">{lang === 'es' ? 'Sincronización:' : 'Last Sync:'} {stats?.last_sync ? new Date(stats.last_sync).toLocaleString() : (lang === 'es' ? 'Nunca' : 'Never')}</p>
        </div>

        <div className="glass-card p-8 border border-white/5 bg-white/[0.02] rounded-3xl relative overflow-hidden group">
          <div className="absolute top-0 right-0 p-4 text-4xl opacity-10 group-hover:scale-110 transition-transform">🛒</div>
          <p className="text-xs font-black uppercase tracking-[0.2em] text-slate-500 mb-2">{lang === 'es' ? 'Órdenes de Compra' : 'Purchase Orders'}</p>
          <h3 className="text-5xl font-black text-white">{stats?.total_ocs || 0}</h3>
          <p className="text-[10px] text-sky mt-4 font-mono">{lang === 'es' ? 'Seguimiento en tiempo real' : 'Real-time local tracking'}</p>
        </div>

        <div className="glass-card p-8 border border-white/5 bg-white/[0.02] rounded-3xl relative overflow-hidden group">
          <div className="absolute top-0 right-0 p-4 text-4xl opacity-10 group-hover:scale-110 transition-transform">🧠</div>
          <p className="text-xs font-black uppercase tracking-[0.2em] text-slate-500 mb-2">{t.analysisCount}</p>
          <h3 className="text-5xl font-black text-white">{stats?.total_analysis || 0}</h3>
          <p className="text-[10px] text-purple-400 mt-4 font-mono">{lang === 'es' ? 'Inteligencia de IA persistente' : 'AI Intelligence persistence'}</p>
        </div>
      </div>

      {/* Top Buyers List */}
      <div className="grid grid-cols-1 lg:grid-cols-2 gap-6">
        <div className="glass-card p-8 border border-white/5 bg-white/[0.02] rounded-3xl">
          <h3 className="text-sm font-black uppercase tracking-widest text-slate-400 mb-6 flex items-center gap-2">
            <span>🏛️</span> {lang === 'es' ? 'Instituciones Top Locales' : 'Top Local Institutions'}
          </h3>
          <div className="space-y-4">
            {stats?.top_buyers?.map((buyer: any, idx: number) => (
              <button
                key={idx}
                onClick={() => onFilterClick?.("buyer", buyer.name)}
                className="w-full flex items-center justify-between p-4 rounded-2xl bg-white/[0.03] border border-white/5 hover:bg-white/[0.08] hover:border-cyan/30 transition-all group/row cursor-pointer text-left"
              >
                <span className="text-sm text-slate-300 truncate max-w-[250px] font-medium group-hover/row:text-white transition-colors">
                  {buyer.name}
                </span>
                <div className="flex items-center gap-3">
                  <span className="text-lg font-black text-cyan font-mono">{buyer.count}</span>
                  <span className="opacity-0 group-hover/row:opacity-100 transition-opacity text-cyan">📡</span>
                </div>
              </button>
            ))}
            {(!stats?.top_buyers || stats.top_buyers.length === 0) && (
              <p className="text-slate-600 italic text-sm py-4">No institutions found in local database.</p>
            )}
          </div>
        </div>

        <div className="glass-card p-8 border border-white/5 bg-white/[0.02] rounded-3xl">
          <h3 className="text-sm font-black uppercase tracking-widest text-slate-400 mb-6 flex items-center gap-2">
            <span>💡</span> Persistence Insights
          </h3>
          <div className="space-y-6">
            <div className="p-4 rounded-2xl bg-blue-500/5 border border-blue-500/10">
              <p className="text-xs text-blue-400 font-bold mb-1">Local Mode Active</p>
              <p className="text-xs text-slate-400 leading-relaxed">System is prioritizing local database for faster search. Global sync updates the local cache with the latest Mercado Público data.</p>
            </div>
            <div className="p-4 rounded-2xl bg-purple-500/5 border border-purple-500/10">
              <p className="text-xs text-purple-400 font-bold mb-1">Integrity Check</p>
              <p className="text-xs text-slate-400 leading-relaxed">All nested data (attachments, items, criteria) is successfully serialized as JSON in the SQLite storage.</p>
            </div>
          </div>
        </div>
      </div>
    </div>
  );
}