import React, { useState, useMemo } from 'react';
import { InvestigationCase } from '../../types/forensics';
import { RelationalTable, QueryResult, PivotConfig, PivotResult } from '../../types/database';
import { SqlEngine, computePivotTable } from '../../services/sqlEngine';
import { generateRelationalDatasets } from '../../services/relationalDataGenerator';
import { 
  Database, 
  Table as TableIcon, 
  Play, 
  Download, 
  Copy, 
  Check, 
  Layers, 
  Sliders, 
  Search, 
  FileSpreadsheet, 
  Key, 
  Code, 
  Sparkles,
  RefreshCw,
  Clock,
  ArrowUpDown,
  Filter,
  BarChart2,
  ChevronRight,
  ExternalLink
} from 'lucide-react';

interface WorkbookDatabaseTabProps {
  allCases: InvestigationCase[];
  currentCase: InvestigationCase;
  initialQuery?: string;
}

const PRESET_SQL_QUERIES = [
  {
    name: 'BSA Structuring Smurf Detection ($8k-$10k)',
    sql: `SELECT tx_id, source_node, target_node, amount_usd, asset, hop_type, risk_tag 
FROM transactions_fact 
WHERE amount_usd BETWEEN 8000 AND 9999 
ORDER BY amount_usd DESC;`,
  },
  {
    name: 'Upstream Risk Concentration by Jurisdiction',
    sql: `SELECT n.jurisdiction, 
       COUNT(t.tx_id) AS tx_count, 
       SUM(t.amount_usd) AS total_volume_usd, 
       AVG(n.risk_score) AS avg_risk_score
FROM nodes_dimension n 
JOIN transactions_fact t ON n.node_id = t.source_node 
GROUP BY n.jurisdiction 
ORDER BY total_volume_usd DESC;`,
  },
  {
    name: 'Speed-of-Light & STUN Leak Physical Anomalies',
    sql: `SELECT case_id, claimed_ip, claimed_country, unmasked_real_ip, propagation_delta_ms, ja4_fingerprint, detected_client_engine 
FROM telemetry_audit 
WHERE speed_of_light_violation = true OR webrtc_leak_detected = true 
ORDER BY propagation_delta_ms DESC;`,
  },
  {
    name: 'High-Volume Intermediary Sinks & Mixers by Depth',
    sql: `SELECT node_id, label, entity_type, jurisdiction, upstream_depth, total_inflow_usd, in_degree, out_degree, risk_score 
FROM nodes_dimension 
WHERE total_inflow_usd > 10000 
ORDER BY upstream_depth ASC, total_inflow_usd DESC;`,
  },
  {
    name: 'Cross-Tabulated Liquidity Flows by Asset & Hop',
    sql: `SELECT asset, hop_type, 
       COUNT(*) AS tx_count, 
       SUM(amount_usd) AS total_volume_usd, 
       AVG(amount_usd) AS avg_ticket_usd 
FROM transactions_fact 
GROUP BY asset, hop_type 
ORDER BY total_volume_usd DESC;`,
  },
  {
    name: 'Academic AML Hypothesis Test Summary',
    sql: `SELECT metric_id, metric_name, test_statistic, p_value, critical_threshold, verdict 
FROM statistical_metrics 
ORDER BY test_statistic DESC;`,
  },
];

export const WorkbookDatabaseTab: React.FC<WorkbookDatabaseTabProps> = ({
  allCases,
  currentCase,
  initialQuery,
}) => {
  const [activeViewMode, setActiveViewMode] = useState<'SQL_STUDIO' | 'PIVOT_STUDIO' | 'TABLE_EXPLORER'>('SQL_STUDIO');
  
  // Build Relational Database
  const { tables: initialTables } = useMemo(() => {
    return generateRelationalDatasets(allCases, currentCase);
  }, [allCases, currentCase]);

  const [customTables, setCustomTables] = useState<RelationalTable[]>([]);
  const allTables = useMemo(() => [...initialTables, ...customTables], [initialTables, customTables]);

  const sqlEngine = useMemo(() => new SqlEngine(allTables), [allTables]);

  // SQL Studio State
  const [sqlQuery, setSqlQuery] = useState<string>(
    initialQuery || PRESET_SQL_QUERIES[0].sql
  );
  const [queryResult, setQueryResult] = useState<QueryResult>(() => {
    return sqlEngine.execute(initialQuery || PRESET_SQL_QUERIES[0].sql);
  });
  const [copied, setCopied] = useState(false);
  const [resultFilter, setResultFilter] = useState('');
  const [newViewName, setNewViewName] = useState('');
  const [isCreatingView, setIsCreatingView] = useState(false);

  // Pivot Studio State
  const [pivotTableSelection, setPivotTableSelection] = useState<string>('transactions_fact');
  const [pivotConfig, setPivotConfig] = useState<PivotConfig>({
    rowField: 'hop_type',
    colField: 'asset',
    valField: 'amount_usd',
    aggFunc: 'SUM',
  });

  // Table Explorer State
  const [selectedExplorerTable, setSelectedExplorerTable] = useState<string>('nodes_dimension');

  // Execute SQL
  const handleExecuteSql = (queryToRun?: string) => {
    const q = queryToRun || sqlQuery;
    const res = sqlEngine.execute(q);
    setQueryResult(res);
  };

  // Create materialized custom table from SQL query
  const handleCreateView = () => {
    if (!newViewName.trim() || queryResult.rows.length === 0) return;
    const safeName = newViewName.trim().replace(/[^a-zA-Z0-9_]/g, '_').toLowerCase();
    
    const newTable: RelationalTable = {
      id: `view_${Date.now()}`,
      name: safeName,
      displayName: `Custom View: ${safeName}`,
      description: `Materialized view from query result (${queryResult.rowCount} rows)`,
      rowCount: queryResult.rowCount,
      columns: queryResult.columns.map(col => ({
        name: col,
        type: typeof queryResult.rows[0]?.[col] === 'number' ? 'number' : typeof queryResult.rows[0]?.[col] === 'boolean' ? 'boolean' : 'string',
        description: `Derived column ${col}`,
      })),
      rows: queryResult.rows,
    };

    setCustomTables(prev => [...prev.filter(t => t.name !== safeName), newTable]);
    setIsCreatingView(false);
    setNewViewName('');
  };

  // Export functions
  const exportToCsv = (rows: Record<string, any>[], columns: string[], filename: string) => {
    if (rows.length === 0) return;
    const header = columns.join(',');
    const body = rows.map(r => columns.map(c => JSON.stringify(r[c] ?? '')).join(',')).join('\n');
    const csvContent = `data:text/csv;charset=utf-8,${encodeURIComponent(header + '\n' + body)}`;
    const link = document.createElement('a');
    link.setAttribute('href', csvContent);
    link.setAttribute('download', `${filename}.csv`);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
  };

  const exportToJson = (rows: Record<string, any>[], filename: string) => {
    const jsonContent = `data:text/json;charset=utf-8,${encodeURIComponent(JSON.stringify(rows, null, 2))}`;
    const link = document.createElement('a');
    link.setAttribute('href', jsonContent);
    link.setAttribute('download', `${filename}.json`);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
  };

  const copyAsGoogleSheetsTsv = (rows: Record<string, any>[], columns: string[]) => {
    if (rows.length === 0) return;
    const header = columns.join('\t');
    const body = rows.map(r => columns.map(c => String(r[c] ?? '')).join('\t')).join('\n');
    navigator.clipboard.writeText(header + '\n' + body);
    setCopied(true);
    setTimeout(() => setCopied(false), 2000);
  };

  // Compute Pivot Result
  const activePivotTable = allTables.find(t => t.name === pivotTableSelection) || allTables[0];
  const pivotResult: PivotResult = useMemo(() => {
    if (!activePivotTable) {
      return {
        rowKeys: [],
        colKeys: [],
        matrix: {},
        rowTotals: {},
        colTotals: {},
        grandTotal: 0,
        totalRecords: 0,
        maxVal: 0,
        minVal: 0,
      };
    }
    return computePivotTable(activePivotTable.rows, pivotConfig);
  }, [activePivotTable, pivotConfig]);

  // Filtered Query Result Rows
  const filteredResultRows = useMemo(() => {
    if (!resultFilter.trim()) return queryResult.rows;
    const lower = resultFilter.toLowerCase();
    return queryResult.rows.filter(r => 
      Object.values(r).some(v => String(v).toLowerCase().includes(lower))
    );
  }, [queryResult.rows, resultFilter]);

  const explorerTable = allTables.find(t => t.name === selectedExplorerTable) || allTables[0];

  return (
    <div className="space-y-4 p-4 text-slate-100">
      {/* Top Banner: Relational Analytics Workbook & SQL Studio */}
      <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-lg flex flex-wrap items-center justify-between gap-3">
        <div className="flex items-center gap-3">
          <div className="p-2.5 rounded-lg bg-cyan-950/80 border border-cyan-800 text-cyan-400">
            <Database className="w-5 h-5" />
          </div>
          <div>
            <div className="flex items-center gap-2">
              <h3 className="text-sm font-bold uppercase tracking-wider font-mono">
                Relational Analytics Workbook & SQL Pivot Database
              </h3>
              <span className="text-[10px] font-mono px-2 py-0.5 rounded bg-cyan-950 text-cyan-300 border border-cyan-800 font-bold">
                {allTables.length} RELATIONAL TABLES • IN-MEMORY ENGINE
              </span>
            </div>
            <p className="text-xs text-slate-400 font-mono">
              Perform SQL queries, relational joins, cross-tabulations, and custom visual pivot tables directly on forensic telemetry and multi-hop ledgers.
            </p>
          </div>
        </div>

        {/* View Mode Tabs */}
        <div className="flex items-center bg-slate-950 border border-slate-800 rounded-lg p-1 text-xs font-mono">
          <button
            onClick={() => setActiveViewMode('SQL_STUDIO')}
            className={`flex items-center gap-1.5 px-3 py-1.5 rounded cursor-pointer transition-all ${
              activeViewMode === 'SQL_STUDIO'
                ? 'bg-cyan-900/70 text-cyan-200 font-bold shadow'
                : 'text-slate-400 hover:text-slate-200'
            }`}
          >
            <Code className="w-3.5 h-3.5" />
            <span>Interactive SQL Studio</span>
          </button>

          <button
            onClick={() => setActiveViewMode('PIVOT_STUDIO')}
            className={`flex items-center gap-1.5 px-3 py-1.5 rounded cursor-pointer transition-all ${
              activeViewMode === 'PIVOT_STUDIO'
                ? 'bg-cyan-900/70 text-cyan-200 font-bold shadow'
                : 'text-slate-400 hover:text-slate-200'
            }`}
          >
            <FileSpreadsheet className="w-3.5 h-3.5" />
            <span>Visual Pivot Studio</span>
          </button>

          <button
            onClick={() => setActiveViewMode('TABLE_EXPLORER')}
            className={`flex items-center gap-1.5 px-3 py-1.5 rounded cursor-pointer transition-all ${
              activeViewMode === 'TABLE_EXPLORER'
                ? 'bg-cyan-900/70 text-cyan-200 font-bold shadow'
                : 'text-slate-400 hover:text-slate-200'
            }`}
          >
            <TableIcon className="w-3.5 h-3.5" />
            <span>Schema & Tables ({allTables.length})</span>
          </button>
        </div>
      </div>

      {/* ========================================================================= */}
      {/* MODE 1: INTERACTIVE SQL STUDIO */}
      {/* ========================================================================= */}
      {activeViewMode === 'SQL_STUDIO' && (
        <div className="grid grid-cols-1 lg:grid-cols-4 gap-4">
          {/* Schema Explorer & Pre-baked Forensic Templates (1 Col) */}
          <div className="space-y-4">
            {/* Relational Schema Browser */}
            <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-3.5 shadow-md space-y-3 font-mono">
              <div className="flex items-center justify-between border-b border-slate-800 pb-2">
                <div className="flex items-center gap-1.5 text-xs font-bold text-slate-200">
                  <Database className="w-4 h-4 text-cyan-400" />
                  <span>RELATIONAL SCHEMA</span>
                </div>
                <span className="text-[10px] text-slate-400">{allTables.length} Tables</span>
              </div>

              <div className="space-y-2 max-h-[220px] overflow-y-auto pr-1">
                {allTables.map(t => (
                  <div
                    key={t.name}
                    className="p-2 rounded-lg bg-slate-950/80 border border-slate-800/80 hover:border-slate-700 transition-all text-xs"
                  >
                    <div className="flex items-center justify-between mb-1">
                      <span className="font-bold text-cyan-300">{t.name}</span>
                      <span className="text-[10px] text-slate-400">{t.rowCount} rows</span>
                    </div>
                    <div className="flex flex-wrap gap-1">
                      {t.columns.slice(0, 5).map(c => (
                        <span
                          key={c.name}
                          onClick={() => setSqlQuery(prev => prev + ` ${c.name}`)}
                          className="bg-slate-900 hover:bg-slate-800 text-slate-400 hover:text-cyan-200 px-1 py-0.5 rounded text-[9px] cursor-pointer"
                          title={`${c.name} (${c.type}): ${c.description}`}
                        >
                          {c.primaryKey && <Key className="w-2.5 h-2.5 inline mr-0.5 text-amber-400" />}
                          {c.name}
                        </span>
                      ))}
                      {t.columns.length > 5 && (
                        <span className="text-[9px] text-slate-500">+{t.columns.length - 5} more</span>
                      )}
                    </div>
                  </div>
                ))}
              </div>
            </div>

            {/* Forensic SQL Templates */}
            <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-3.5 shadow-md space-y-2.5 font-mono">
              <div className="flex items-center gap-1.5 text-xs font-bold text-slate-200 border-b border-slate-800 pb-2">
                <Sparkles className="w-4 h-4 text-amber-400" />
                <span>FORENSIC SQL PRESETS</span>
              </div>

              <div className="space-y-1.5">
                {PRESET_SQL_QUERIES.map(preset => (
                  <button
                    key={preset.name}
                    onClick={() => {
                      setSqlQuery(preset.sql);
                      handleExecuteSql(preset.sql);
                    }}
                    className="w-full text-left p-2 rounded-lg bg-slate-950/60 hover:bg-slate-800/90 border border-slate-800/70 hover:border-cyan-700/60 transition-all text-xs group cursor-pointer"
                  >
                    <div className="text-[11px] font-bold text-slate-300 group-hover:text-cyan-300 flex items-center justify-between">
                      <span>{preset.name}</span>
                      <ChevronRight className="w-3 h-3 text-slate-500 group-hover:text-cyan-400" />
                    </div>
                  </button>
                ))}
              </div>
            </div>
          </div>

          {/* SQL Editor & Interactive Query Results Grid (3 Cols) */}
          <div className="lg:col-span-3 space-y-4">
            {/* SQL Query Editor Card */}
            <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-md space-y-3 font-mono">
              <div className="flex items-center justify-between">
                <div className="flex items-center gap-2 text-xs font-bold text-slate-200">
                  <Code className="w-4 h-4 text-cyan-400" />
                  <span>SQL QUERY EDITOR</span>
                </div>
                <div className="flex items-center gap-2">
                  <button
                    onClick={() => setIsCreatingView(!isCreatingView)}
                    className="bg-slate-800 hover:bg-slate-700 border border-slate-700 text-slate-300 px-2.5 py-1 rounded text-xs transition-all cursor-pointer"
                  >
                    Save as Virtual View
                  </button>
                  <button
                    onClick={() => handleExecuteSql()}
                    className="bg-cyan-600 hover:bg-cyan-500 text-slate-950 font-bold px-4 py-1 rounded text-xs transition-all flex items-center gap-1.5 shadow-md shadow-cyan-950/60 active:scale-95 cursor-pointer"
                  >
                    <Play className="w-3.5 h-3.5 fill-current" />
                    <span>Run Query</span>
                  </button>
                </div>
              </div>

              {/* View Name Materialization Prompt */}
              {isCreatingView && (
                <div className="bg-slate-950 p-2.5 rounded-lg border border-cyan-800/80 flex items-center gap-2">
                  <input
                    type="text"
                    placeholder="Enter new view table name (e.g. smurf_clusters_view)..."
                    value={newViewName}
                    onChange={(e) => setNewViewName(e.target.value)}
                    className="flex-1 bg-slate-900 border border-slate-700 text-slate-200 px-2.5 py-1 text-xs rounded focus:outline-none focus:border-cyan-500 font-mono"
                  />
                  <button
                    onClick={handleCreateView}
                    className="bg-cyan-700 hover:bg-cyan-600 text-slate-100 px-3 py-1 text-xs rounded font-bold cursor-pointer"
                  >
                    Materialize View
                  </button>
                  <button
                    onClick={() => setIsCreatingView(false)}
                    className="text-slate-400 hover:text-slate-200 text-xs px-2 cursor-pointer"
                  >
                    Cancel
                  </button>
                </div>
              )}

              {/* Textarea Code Area */}
              <div className="relative">
                <textarea
                  value={sqlQuery}
                  onChange={(e) => setSqlQuery(e.target.value)}
                  onKeyDown={(e) => {
                    if ((e.ctrlKey || e.metaKey) && e.key === 'Enter') {
                      e.preventDefault();
                      handleExecuteSql();
                    }
                  }}
                  rows={5}
                  className="w-full bg-slate-950 border border-slate-800 rounded-lg p-3 text-xs text-cyan-300 font-mono focus:outline-none focus:border-cyan-500 shadow-inner resize-y leading-relaxed"
                  placeholder="SELECT * FROM transactions_fact WHERE amount_usd > 10000;"
                  spellCheck={false}
                />
                <span className="absolute bottom-2 right-2 text-[10px] text-slate-600 font-mono select-none">
                  Ctrl + Enter to execute
                </span>
              </div>

              {/* Query Execution Status Banner */}
              <div className="flex flex-wrap items-center justify-between text-xs bg-slate-950 p-2 rounded-lg border border-slate-800 text-slate-400">
                <div className="flex items-center gap-3">
                  <span className="flex items-center gap-1">
                    <Clock className="w-3.5 h-3.5 text-cyan-400" />
                    <span>Exec Time: <strong className="text-cyan-300">{queryResult.executionTimeMs} ms</strong></span>
                  </span>
                  <span className="flex items-center gap-1">
                    <TableIcon className="w-3.5 h-3.5 text-emerald-400" />
                    <span>Rows: <strong className="text-emerald-300">{queryResult.rowCount}</strong></span>
                  </span>
                </div>

                {queryResult.queryPlan && (
                  <span className="text-[10px] text-slate-500 truncate max-w-[400px]" title={queryResult.queryPlan}>
                    Plan: {queryResult.queryPlan}
                  </span>
                )}
              </div>
            </div>

            {/* Query Results Table Card */}
            <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-md space-y-3 font-mono">
              <div className="flex flex-wrap items-center justify-between gap-2 border-b border-slate-800 pb-3">
                <div className="flex items-center gap-2">
                  <TableIcon className="w-4 h-4 text-emerald-400" />
                  <span className="text-xs font-bold text-slate-200 uppercase">
                    QUERY RESULT SET ({filteredResultRows.length} RECORDS)
                  </span>
                </div>

                {/* Filter and Export Tools */}
                <div className="flex items-center gap-2">
                  <div className="relative">
                    <Search className="w-3.5 h-3.5 text-slate-400 absolute left-2.5 top-2" />
                    <input
                      type="text"
                      placeholder="Filter results..."
                      value={resultFilter}
                      onChange={(e) => setResultFilter(e.target.value)}
                      className="bg-slate-950 border border-slate-800 text-slate-200 text-xs pl-8 pr-2.5 py-1 rounded focus:outline-none focus:border-cyan-500 w-36 sm:w-44"
                    />
                  </div>

                  <button
                    onClick={() => copyAsGoogleSheetsTsv(filteredResultRows, queryResult.columns)}
                    className="flex items-center gap-1 bg-slate-800 hover:bg-slate-700 text-slate-200 border border-slate-700 px-2.5 py-1 rounded text-xs font-bold transition-all cursor-pointer"
                    title="Copy directly to paste into Google Sheets / Excel"
                  >
                    {copied ? <Check className="w-3.5 h-3.5 text-emerald-400" /> : <Copy className="w-3.5 h-3.5" />}
                    <span>{copied ? 'Copied TSV' : 'Copy TSV'}</span>
                  </button>

                  <button
                    onClick={() => exportToCsv(filteredResultRows, queryResult.columns, 'sql_query_results')}
                    className="flex items-center gap-1 bg-slate-800 hover:bg-slate-700 text-slate-200 border border-slate-700 px-2.5 py-1 rounded text-xs font-bold transition-all cursor-pointer"
                  >
                    <Download className="w-3.5 h-3.5" />
                    <span>CSV</span>
                  </button>

                  <button
                    onClick={() => exportToJson(filteredResultRows, 'sql_query_results')}
                    className="flex items-center gap-1 bg-slate-800 hover:bg-slate-700 text-slate-200 border border-slate-700 px-2.5 py-1 rounded text-xs font-bold transition-all cursor-pointer"
                  >
                    <Download className="w-3.5 h-3.5" />
                    <span>JSON</span>
                  </button>
                </div>
              </div>

              {/* Error Message if Query Failed */}
              {queryResult.error && (
                <div className="p-3 bg-rose-950/80 border border-rose-800 rounded-lg text-xs text-rose-300">
                  <strong>SQL Execution Error:</strong> {queryResult.error}
                </div>
              )}

              {/* Result Table Grid */}
              {!queryResult.error && (
                <div className="border border-slate-800 rounded-lg overflow-x-auto max-h-[360px] bg-slate-950">
                  {filteredResultRows.length === 0 ? (
                    <div className="p-8 text-center text-slate-500 text-xs">
                      No records matched the SQL query criteria.
                    </div>
                  ) : (
                    <table className="w-full text-left text-xs border-collapse font-mono">
                      <thead className="bg-slate-900 border-b border-slate-800 sticky top-0 z-10 text-[11px] text-slate-400 uppercase">
                        <tr>
                          <th className="py-2 px-3 text-slate-500 w-10">#</th>
                          {queryResult.columns.map(col => (
                            <th key={col} className="py-2 px-3 font-semibold text-cyan-300 whitespace-nowrap">
                              {col}
                            </th>
                          ))}
                        </tr>
                      </thead>
                      <tbody className="divide-y divide-slate-800/60">
                        {filteredResultRows.map((row, idx) => (
                          <tr key={idx} className="hover:bg-slate-900/60 transition-colors">
                            <td className="py-2 px-3 text-slate-600 text-[10px]">{idx + 1}</td>
                            {queryResult.columns.map(col => {
                              const val = row[col];
                              const isNum = typeof val === 'number';
                              const isBool = typeof val === 'boolean';
                              return (
                                <td key={col} className="py-2 px-3 whitespace-nowrap">
                                  {isBool ? (
                                    <span className={`px-1.5 py-0.5 rounded text-[10px] font-bold ${val ? 'bg-emerald-950 text-emerald-300 border border-emerald-800' : 'bg-slate-900 text-slate-500'}`}>
                                      {val ? 'TRUE' : 'FALSE'}
                                    </span>
                                  ) : isNum ? (
                                    <span className="text-emerald-300 font-bold">
                                      {col.includes('usd') || col.includes('volume') || col.includes('amount') ? `$${val.toLocaleString()}` : val}
                                    </span>
                                  ) : (
                                    <span className="text-slate-200">{String(val ?? 'NULL')}</span>
                                  )}
                                </td>
                              );
                            })}
                          </tr>
                        ))}
                      </tbody>
                    </table>
                  )}
                </div>
              )}
            </div>
          </div>
        </div>
      )}

      {/* ========================================================================= */}
      {/* MODE 2: VISUAL PIVOT TABLE STUDIO */}
      {/* ========================================================================= */}
      {activeViewMode === 'PIVOT_STUDIO' && (
        <div className="space-y-4">
          {/* Pivot Configuration Bar */}
          <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-md font-mono space-y-3">
            <div className="flex items-center justify-between border-b border-slate-800 pb-2">
              <div className="flex items-center gap-2">
                <FileSpreadsheet className="w-4 h-4 text-cyan-400" />
                <span className="text-xs font-bold uppercase text-slate-200">
                  CUSTOM VISUAL PIVOT CONFIGURATOR (2D CROSS-TABULATION MATRIX)
                </span>
              </div>
              <div className="flex items-center gap-2">
                <select
                  value={pivotTableSelection}
                  onChange={(e) => setPivotTableSelection(e.target.value)}
                  className="bg-slate-950 border border-slate-700 text-cyan-300 text-xs px-2.5 py-1 rounded font-bold focus:outline-none focus:border-cyan-500"
                >
                  {allTables.map(t => (
                    <option key={t.name} value={t.name}>
                      Table: {t.name} ({t.rowCount} rows)
                    </option>
                  ))}
                </select>
              </div>
            </div>

            {/* Selectors Grid */}
            <div className="grid grid-cols-1 md:grid-cols-4 gap-3 text-xs">
              {/* Row Dimension */}
              <div className="space-y-1">
                <label className="text-[10px] text-slate-400 uppercase font-bold">Row Dimension (Rows)</label>
                <select
                  value={pivotConfig.rowField}
                  onChange={(e) => setPivotConfig({ ...pivotConfig, rowField: e.target.value })}
                  className="w-full bg-slate-950 border border-slate-700 text-slate-200 p-1.5 rounded focus:outline-none focus:border-cyan-500"
                >
                  {activePivotTable.columns.map(c => (
                    <option key={c.name} value={c.name}>{c.name} ({c.type})</option>
                  ))}
                </select>
              </div>

              {/* Column Dimension */}
              <div className="space-y-1">
                <label className="text-[10px] text-slate-400 uppercase font-bold">Column Dimension (Columns)</label>
                <select
                  value={pivotConfig.colField}
                  onChange={(e) => setPivotConfig({ ...pivotConfig, colField: e.target.value })}
                  className="w-full bg-slate-950 border border-slate-700 text-slate-200 p-1.5 rounded focus:outline-none focus:border-cyan-500"
                >
                  {activePivotTable.columns.map(c => (
                    <option key={c.name} value={c.name}>{c.name} ({c.type})</option>
                  ))}
                </select>
              </div>

              {/* Value Metric */}
              <div className="space-y-1">
                <label className="text-[10px] text-slate-400 uppercase font-bold">Value Metric (Values)</label>
                <select
                  value={pivotConfig.valField}
                  onChange={(e) => setPivotConfig({ ...pivotConfig, valField: e.target.value })}
                  className="w-full bg-slate-950 border border-slate-700 text-slate-200 p-1.5 rounded focus:outline-none focus:border-cyan-500"
                >
                  {activePivotTable.columns.map(c => (
                    <option key={c.name} value={c.name}>{c.name} ({c.type})</option>
                  ))}
                </select>
              </div>

              {/* Aggregation Function */}
              <div className="space-y-1">
                <label className="text-[10px] text-slate-400 uppercase font-bold">Aggregation Method</label>
                <select
                  value={pivotConfig.aggFunc}
                  onChange={(e) => setPivotConfig({ ...pivotConfig, aggFunc: e.target.value as any })}
                  className="w-full bg-slate-950 border border-slate-700 text-slate-200 p-1.5 rounded focus:outline-none focus:border-cyan-500 font-bold"
                >
                  <option value="SUM">SUM (Total Value)</option>
                  <option value="AVG">AVG (Average Value)</option>
                  <option value="COUNT">COUNT (Record Frequency)</option>
                  <option value="MIN">MIN (Minimum Value)</option>
                  <option value="MAX">MAX (Maximum Value)</option>
                </select>
              </div>
            </div>
          </div>

          {/* Pivot Table Rendered Matrix */}
          <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-lg space-y-3 font-mono">
            <div className="flex items-center justify-between">
              <div className="flex items-center gap-2 text-xs font-bold text-slate-200">
                <BarChart2 className="w-4 h-4 text-cyan-400" />
                <span>
                  PIVOT GRID: {pivotConfig.aggFunc}({pivotConfig.valField}) BY [{pivotConfig.rowField}] × [{pivotConfig.colField}]
                </span>
              </div>

              <div className="flex items-center gap-3 text-xs">
                <span className="text-slate-400">
                  Grand Total: <strong className="text-emerald-400">${pivotResult.grandTotal.toLocaleString()}</strong>
                </span>
                <button
                  onClick={() => {
                    const tsvHeader = `${pivotConfig.rowField}\t` + pivotResult.colKeys.join('\t') + '\tTOTAL';
                    const tsvRows = pivotResult.rowKeys.map(rk => {
                      const cells = pivotResult.colKeys.map(ck => pivotResult.matrix[rk][ck]?.value || 0).join('\t');
                      return `${rk}\t${cells}\t${pivotResult.rowTotals[rk]}`;
                    }).join('\n');
                    navigator.clipboard.writeText(tsvHeader + '\n' + tsvRows);
                    setCopied(true);
                    setTimeout(() => setCopied(false), 2000);
                  }}
                  className="bg-slate-800 hover:bg-slate-700 text-slate-200 border border-slate-700 px-2.5 py-1 rounded text-xs font-bold cursor-pointer"
                >
                  {copied ? 'Copied Pivot TSV' : 'Copy Pivot TSV'}
                </button>
              </div>
            </div>

            {/* 2D Matrix Table */}
            <div className="border border-slate-800 rounded-lg overflow-x-auto max-h-[460px] bg-slate-950">
              <table className="w-full text-left text-xs border-collapse">
                <thead className="bg-slate-900 border-b border-slate-800 sticky top-0 z-10 text-[11px] text-slate-300">
                  <tr>
                    <th className="py-2.5 px-4 font-bold bg-slate-900/90 border-r border-slate-800 text-cyan-300">
                      {pivotConfig.rowField} \ {pivotConfig.colField}
                    </th>
                    {pivotResult.colKeys.map(ck => (
                      <th key={ck} className="py-2.5 px-4 font-semibold text-slate-200 border-r border-slate-800 text-right whitespace-nowrap">
                        {ck}
                      </th>
                    ))}
                    <th className="py-2.5 px-4 font-bold text-amber-300 bg-slate-900/90 text-right">
                      ROW TOTAL
                    </th>
                  </tr>
                </thead>
                <tbody className="divide-y divide-slate-800/80">
                  {pivotResult.rowKeys.map(rk => (
                    <tr key={rk} className="hover:bg-slate-900/40 transition-colors">
                      <td className="py-2.5 px-4 font-bold text-slate-200 bg-slate-900/40 border-r border-slate-800 whitespace-nowrap">
                        {rk}
                      </td>
                      {pivotResult.colKeys.map(ck => {
                        const cell = pivotResult.matrix[rk]?.[ck];
                        const ratio = cell?.ratio || 0;
                        // Heatmap background calculation
                        const bgStyle = ratio > 0 ? {
                          backgroundColor: `rgba(6, 182, 212, ${Math.min(Math.max(ratio * 0.45, 0.08), 0.5)})`,
                        } : {};

                        return (
                          <td
                            key={ck}
                            style={bgStyle}
                            className="py-2.5 px-4 text-right border-r border-slate-800/80 whitespace-nowrap font-mono"
                          >
                            <span className={cell?.value && cell.value > 0 ? 'text-slate-100 font-bold' : 'text-slate-600'}>
                              {cell ? cell.formatted : '-'}
                            </span>
                            {cell && cell.count > 0 && (
                              <span className="block text-[9px] text-slate-400">
                                n={cell.count}
                              </span>
                            )}
                          </td>
                        );
                      })}
                      <td className="py-2.5 px-4 text-right font-bold text-emerald-300 bg-slate-900/40 whitespace-nowrap">
                        ${pivotResult.rowTotals[rk]?.toLocaleString()}
                      </td>
                    </tr>
                  ))}
                </tbody>
                <tfoot className="bg-slate-900 border-t-2 border-slate-700 font-bold text-slate-100 sticky bottom-0 z-10">
                  <tr>
                    <td className="py-2.5 px-4 text-amber-300 border-r border-slate-800 uppercase">
                      COLUMN TOTALS
                    </td>
                    {pivotResult.colKeys.map(ck => (
                      <td key={ck} className="py-2.5 px-4 text-right text-emerald-300 border-r border-slate-800 whitespace-nowrap">
                        ${pivotResult.colTotals[ck]?.toLocaleString()}
                      </td>
                    ))}
                    <td className="py-2.5 px-4 text-right text-cyan-300 font-extrabold bg-cyan-950/60 whitespace-nowrap">
                      ${pivotResult.grandTotal?.toLocaleString()}
                    </td>
                  </tr>
                </tfoot>
              </table>
            </div>
          </div>
        </div>
      )}

      {/* ========================================================================= */}
      {/* MODE 3: TABLE SCHEMA EXPLORER (RAW DATA VIEW) */}
      {/* ========================================================================= */}
      {activeViewMode === 'TABLE_EXPLORER' && (
        <div className="grid grid-cols-1 lg:grid-cols-4 gap-4">
          {/* Table List (1 Col) */}
          <div className="bg-slate-900/90 border border-slate-800 rounded-xl p-3.5 shadow-md space-y-2 font-mono">
            <div className="text-xs font-bold text-slate-200 border-b border-slate-800 pb-2">
              DATABASE TABLES ({allTables.length})
            </div>
            <div className="space-y-1.5">
              {allTables.map(t => (
                <button
                  key={t.name}
                  onClick={() => setSelectedExplorerTable(t.name)}
                  className={`w-full text-left p-2.5 rounded-lg border transition-all text-xs cursor-pointer ${
                    selectedExplorerTable === t.name
                      ? 'bg-cyan-950/80 border-cyan-500 text-cyan-200 shadow'
                      : 'bg-slate-950/60 border-slate-800/80 text-slate-400 hover:text-slate-200'
                  }`}
                >
                  <div className="font-bold">{t.name}</div>
                  <div className="text-[10px] text-slate-500">{t.rowCount} rows • {t.columns.length} cols</div>
                </button>
              ))}
            </div>
          </div>

          {/* Table Data View (3 Cols) */}
          <div className="lg:col-span-3 bg-slate-900/90 border border-slate-800 rounded-xl p-4 shadow-md space-y-3 font-mono">
            <div className="flex items-center justify-between border-b border-slate-800 pb-3">
              <div>
                <div className="text-sm font-bold text-cyan-300">{explorerTable.displayName}</div>
                <p className="text-xs text-slate-400">{explorerTable.description}</p>
              </div>
              <div className="flex items-center gap-2">
                <button
                  onClick={() => exportToCsv(explorerTable.rows, explorerTable.columns.map(c => c.name), explorerTable.name)}
                  className="flex items-center gap-1 bg-slate-800 hover:bg-slate-700 text-slate-200 border border-slate-700 px-2.5 py-1 rounded text-xs font-bold cursor-pointer"
                >
                  <Download className="w-3.5 h-3.5" />
                  <span>Export CSV</span>
                </button>
              </div>
            </div>

            {/* Schema Columns Chips */}
            <div className="flex flex-wrap gap-1.5 p-2 bg-slate-950 rounded-lg border border-slate-800">
              {explorerTable.columns.map(col => (
                <span
                  key={col.name}
                  className="bg-slate-900 text-slate-300 border border-slate-800 px-2 py-0.5 rounded text-[10px]"
                >
                  {col.primaryKey && <Key className="w-2.5 h-2.5 inline mr-1 text-amber-400" />}
                  <strong>{col.name}</strong> <span className="text-cyan-400">({col.type})</span>
                </span>
              ))}
            </div>

            {/* Raw Table Grid */}
            <div className="border border-slate-800 rounded-lg overflow-x-auto max-h-[380px] bg-slate-950">
              <table className="w-full text-left text-xs border-collapse">
                <thead className="bg-slate-900 border-b border-slate-800 sticky top-0 z-10 text-[11px] text-slate-300">
                  <tr>
                    <th className="py-2 px-3 text-slate-500 w-10">#</th>
                    {explorerTable.columns.map(col => (
                      <th key={col.name} className="py-2 px-3 font-semibold text-cyan-300 whitespace-nowrap">
                        {col.name}
                      </th>
                    ))}
                  </tr>
                </thead>
                <tbody className="divide-y divide-slate-800/60">
                  {explorerTable.rows.map((row, idx) => (
                    <tr key={idx} className="hover:bg-slate-900/60 transition-colors">
                      <td className="py-2 px-3 text-slate-600 text-[10px]">{idx + 1}</td>
                      {explorerTable.columns.map(col => {
                        const val = row[col.name];
                        return (
                          <td key={col.name} className="py-2 px-3 whitespace-nowrap">
                            {typeof val === 'boolean' ? (
                              <span className={`px-1.5 py-0.5 rounded text-[10px] font-bold ${val ? 'bg-emerald-950 text-emerald-300 border border-emerald-800' : 'bg-slate-900 text-slate-500'}`}>
                                {val ? 'TRUE' : 'FALSE'}
                              </span>
                            ) : typeof val === 'number' ? (
                              <span className="text-emerald-300 font-bold">
                                {col.name.includes('usd') || col.name.includes('amount') ? `$${val.toLocaleString()}` : val}
                              </span>
                            ) : (
                              <span className="text-slate-200">{String(val ?? 'NULL')}</span>
                            )}
                          </td>
                        );
                      })}
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          </div>
        </div>
      )}
    </div>
  );
};