frontend/src/components/NLtoSQL.js

import React, { useState, useRef, useCallback } from 'react';
import { apiCall } from '../utils/api';
import { streamText } from '../utils/streamText';
import { FALLBACK_NL2SQL } from '../constants/fallbacks';

const EXAMPLE_QUERIES = [
  'Angry customers who still kept buying',
  "Products similar to 'wireless headphones' + bad reviews",
  'Transactions with fraud risk > 0.7 this week',
  'Which customer cohort churns fastest?',
  'Find customers semantically similar to our top 10 buyers',
];

const LOADING_STEPS = [
  'Classifying intent...',
  'Generating SQL...',
  'Running AST validation...',
  'Executing on read-only Postgres...',
  'Done',
];

function highlightSQL(sql) {
  if (!sql) return '';
  const keywords = /\b(WITH|SELECT|FROM|WHERE|JOIN|LEFT JOIN|RIGHT JOIN|INNER JOIN|FULL JOIN|ORDER BY|GROUP BY|HAVING|LIMIT|INSERT|INTO|VALUES|UPDATE|SET|DELETE|CREATE|TABLE|INDEX|ON|USING|AS|AND|OR|NOT|IN|EXISTS|BETWEEN|LIKE|IS|NULL|TRUE|FALSE|CASE|WHEN|THEN|ELSE|END|DISTINCT|UNION|ALL|DESC|ASC|OVER|PARTITION BY|OFFSET|RETURNING|DEFAULT|REFERENCES|PRIMARY KEY|UNIQUE|CASCADE|CONCURRENTLY|MATERIALIZED VIEW|REFRESH)\b/gi;
  const functions = /\b(AVG|COUNT|SUM|MIN|MAX|RANK|DENSE_RANK|ROW_NUMBER|LAG|LEAD|PERCENT_RANK|ROUND|COALESCE|NOW|EXTRACT|DATE_TRUNC|to_tsvector|to_tsquery|ts_rank|gen_random_uuid|ARRAY_AGG|STRING_AGG)\b/gi;
  const operators = /(<->|<=>|@@|->>'?|::vector|::DATE|::TIMESTAMPTZ|@>)/g;
  const comments = /(--.*$)/gm;
  const strings = /('(?:[^']|'')*')/g;
  const numbers = /\b(\d+\.?\d*)\b/g;

  let highlighted = sql;
  highlighted = highlighted.replace(comments, '<span style="color:var(--text3)">$1</span>');
  highlighted = highlighted.replace(strings, '<span style="color:#CE9178">$1</span>');
  highlighted = highlighted.replace(keywords, '<span style="color:#569CD6">$1</span>');
  highlighted = highlighted.replace(functions, '<span style="color:#DCDCAA">$1</span>');
  highlighted = highlighted.replace(operators, '<span style="color:var(--purple-l)">$1</span>');

  return highlighted;
}

export default function NLtoSQL() {
  const [query, setQuery] = useState("Show me customers who left angry reviews but still kept buying — ranked by lifetime value");
  const [loading, setLoading] = useState(false);
  const [currentStep, setCurrentStep] = useState(-1);
  const [sqlOutput, setSqlOutput] = useState('');
  const [explanation, setExplanation] = useState('');
  const [error, setError] = useState(null);
  const [usedFallback, setUsedFallback] = useState(false);
  const [validation, setValidation] = useState(null);
  const [execution, setExecution] = useState(null);
  const [intentClass, setIntentClass] = useState(null);
  const [retryCount, setRetryCount] = useState(0);
  const [copied, setCopied] = useState(null);
  const abortRef = useRef(null);

  const handleGenerate = useCallback(async () => {
    setLoading(true);
    setCurrentStep(0);
    setSqlOutput('');
    setExplanation('');
    setError(null);
    setUsedFallback(false);
    setValidation(null);
    setExecution(null);
    setIntentClass(null);
    setRetryCount(0);

    // Animate loading steps
    for (let i = 0; i < LOADING_STEPS.length - 1; i++) {
      await new Promise((r) => setTimeout(r, 600));
      setCurrentStep(i + 1);
    }

    try {
      const result = await apiCall('/api/demo/nl2sql', { query }, { timeout: 45000 });
      if (result.ok) {
        setCurrentStep(LOADING_STEPS.length - 1);
        // Stream the SQL output
        abortRef.current = new AbortController();
        await streamText(result.sql || '', (text) => setSqlOutput(text), 6, abortRef.current.signal);
        setExplanation(result.explanation || '');
        setValidation(result.validation || null);
        setExecution(result.execution || null);
        setIntentClass(result.intent_class || null);
        setRetryCount(result.retry_count || 0);
      } else {
        throw new Error(result.error || 'API returned an error');
      }
    } catch (err) {
      setError(err.message || 'Request failed');
      setUsedFallback(true);
      setCurrentStep(LOADING_STEPS.length - 1);
      abortRef.current = new AbortController();
      await streamText(FALLBACK_NL2SQL.sql, (text) => setSqlOutput(text), 4, abortRef.current.signal);
      setExplanation(FALLBACK_NL2SQL.explanation);
    } finally {
      setLoading(false);
    }
  }, [query]);

  const copyCsv = () => {
    const rows = execution?.rows || [];
    const columns = execution?.columns || [];
    if (!rows.length || !columns.length) return;
    const escape = (value) => `"${String(value ?? '').replaceAll('"', '""')}"`;
    const csv = [columns.join(','), ...rows.map((row) => columns.map((column) => escape(row[column])).join(','))].join('\n');
    handleCopy(csv, 'csv');
  };

  const handleCopy = (text, type) => {
    navigator.clipboard.writeText(text);
    setCopied(type);
    setTimeout(() => setCopied(null), 2000);
  };

  return (
    <div style={{ display: 'grid', gridTemplateColumns: '1fr 1fr', gap: 24 }} className="nl2sql-grid">
      {/* Left Panel - Input */}
      <div>
        <h3 style={{ fontFamily: 'var(--font-heading)', fontSize: 20, marginBottom: 16, color: 'var(--text)' }}>Ask in Plain English</h3>
        <label style={{ fontSize: 13, color: 'var(--text2)', marginBottom: 8, display: 'block' }}>Your question</label>
        <textarea
          className="nv-textarea"
          rows={5}
          value={query}
          onChange={(e) => setQuery(e.target.value)}
          placeholder="Ask anything about your customer data..."
          data-testid="live-demo-input"
          style={{ marginBottom: 12 }}
        />

        {/* Example chips */}
        <div style={{ display: 'flex', flexWrap: 'wrap', gap: 8, marginBottom: 16 }}>
          {EXAMPLE_QUERIES.map((eq, i) => (
            <button key={i} className="nv-chip" onClick={() => setQuery(eq)} data-testid={`example-query-${i}`}>
              {eq}
            </button>
          ))}
        </div>

        <button
          className="nv-btn-primary"
          onClick={handleGenerate}
          disabled={loading || !query.trim()}
          data-testid="live-demo-submit-button"
          style={{ width: '100%', justifyContent: 'center' }}
        >
          {loading ? 'Generating...' : 'Generate SQL with Gemini →'}
        </button>

        {/* Loading steps */}
        {loading && (
          <div style={{ marginTop: 16 }}>
            {LOADING_STEPS.map((step, i) => (
              <div key={i} className={`nv-step ${i <= currentStep ? (i < currentStep ? 'done' : 'active') : ''}`}>
                {i < currentStep ? (
                  <span style={{ color: 'var(--green)' }}>✓</span>
                ) : i === currentStep ? (
                  <span className="nv-spinner" />
                ) : (
                  <span style={{ width: 14, height: 14, display: 'inline-block' }} />
                )}
                {step}
              </div>
            ))}
          </div>
        )}
      </div>

      {/* Right Panel - Output */}
      <div>
        <h3 style={{ fontFamily: 'var(--font-heading)', fontSize: 20, marginBottom: 16, color: 'var(--text)' }}>Generated SQL</h3>

        {usedFallback && (
          <div style={{ background: 'rgba(245,158,11,0.08)', border: '1px solid rgba(245,158,11,0.3)', borderRadius: 8, padding: '10px 14px', marginBottom: 12, fontSize: 13, color: 'var(--amber)' }}>
            Live API unavailable — showing cached example response{error ? ` (${error})` : ''}
          </div>
        )}

        {/* SQL code block */}
        <div data-testid="live-demo-output" style={{ position: 'relative' }}>
          <div className="nv-code-block" style={{ minHeight: 200, marginBottom: 16 }}>
            {sqlOutput ? (
              <pre style={{ margin: 0, whiteSpace: 'pre-wrap', wordBreak: 'break-word' }} dangerouslySetInnerHTML={{ __html: highlightSQL(sqlOutput) }} />
            ) : (
              <span style={{ color: 'var(--text3)', fontStyle: 'italic' }}>SQL will appear here...</span>
            )}
            {loading && <span className="nv-cursor" />}
          </div>
        </div>

        {/* Explanation */}
        {explanation && (
          <div style={{ borderLeft: '3px solid var(--teal)', background: 'rgba(20,184,166,0.05)', borderRadius: '0 8px 8px 0', padding: 16, marginBottom: 16 }}>
            <div style={{ fontSize: 12, fontWeight: 600, color: 'var(--teal)', marginBottom: 6 }}>Query Explanation</div>
            <p style={{ fontSize: 13, color: 'var(--text2)', lineHeight: 1.6, margin: 0 }}>{explanation}</p>
          </div>
        )}

        {/* Execution badges */}
        {sqlOutput && !loading && (
          <div style={{ display: 'flex', gap: 8, flexWrap: 'wrap', marginBottom: 16 }}>
            {intentClass && <span className="nv-badge nv-badge-purple">Intent: {intentClass}</span>}
            {validation && (
              <span className={`nv-badge ${validation.valid ? 'nv-badge-green' : 'nv-badge-red'}`}>
                {validation.valid ? 'AST validation passed' : 'Validation rejected'}
              </span>
            )}
            {execution?.success && <span className="nv-badge nv-badge-teal">Executed in {execution.execution_ms}ms</span>}
            {execution?.success && <span className="nv-badge nv-badge-gray">Returned {execution.row_count} rows</span>}
            {retryCount > 0 && <span className="nv-badge nv-badge-amber">Self-corrected after {retryCount} retry</span>}
          </div>
        )}

        {execution && !loading && !execution.success && (
          <div style={{ background: 'rgba(107,107,138,0.10)', border: '1px solid var(--border)', borderRadius: 8, padding: '10px 14px', marginBottom: 16, fontSize: 13, color: 'var(--text2)' }}>
            {execution.skipped || execution.error || 'Execution did not run.'}
          </div>
        )}

        {execution?.success && execution.rows?.length > 0 && (
          <div style={{ marginBottom: 16 }}>
            <div style={{ display: 'flex', justifyContent: 'space-between', alignItems: 'center', gap: 12, marginBottom: 8 }}>
              <div style={{ fontSize: 13, color: 'var(--text2)', fontWeight: 600 }}>Query Results</div>
              <button className="nv-btn-ghost" onClick={copyCsv} style={{ fontSize: 12, padding: '6px 10px' }}>
                {copied === 'csv' ? 'Copied CSV' : 'Copy as CSV'}
              </button>
            </div>
            <div style={{ overflowX: 'auto', border: '1px solid var(--border)', borderRadius: 8 }}>
              <table style={{ width: '100%', borderCollapse: 'collapse', fontSize: 12 }}>
                <thead>
                  <tr style={{ background: 'var(--surface2)' }}>
                    {execution.columns.map((column) => (
                      <th key={column} style={{ padding: '8px 10px', color: 'var(--text3)', textAlign: 'left', fontWeight: 600 }}>{column}</th>
                    ))}
                  </tr>
                </thead>
                <tbody>
                  {execution.rows.slice(0, 20).map((row, rowIndex) => (
                    <tr key={rowIndex} style={{ borderTop: '1px solid var(--border)' }}>
                      {execution.columns.map((column) => (
                        <td key={column} style={{ padding: '8px 10px', color: 'var(--text2)', fontFamily: 'var(--font-mono)' }}>
                          {String(row[column] ?? '')}
                        </td>
                      ))}
                    </tr>
                  ))}
                </tbody>
              </table>
            </div>
          </div>
        )}

        {execution?.success && execution.execution_plan && (
          <details style={{ marginBottom: 16 }}>
            <summary style={{ cursor: 'pointer', color: 'var(--text2)', fontSize: 13 }}>EXPLAIN plan</summary>
            <div className="nv-code-block" style={{ marginTop: 8, maxHeight: 180, overflow: 'auto' }}>
              <pre style={{ margin: 0 }}>{typeof execution.execution_plan === 'string' ? execution.execution_plan : JSON.stringify(execution.execution_plan, null, 2)}</pre>
            </div>
          </details>
        )}

        {/* Copy buttons */}
        {sqlOutput && !loading && (
          <div style={{ display: 'flex', gap: 8 }}>
            <button className="nv-btn-ghost" onClick={() => handleCopy(sqlOutput, 'sql')} data-testid="copy-sql-button" style={{ fontSize: 13, padding: '8px 14px' }}>
              {copied === 'sql' ? '✓ Copied!' : 'Copy SQL'}
            </button>
            {explanation && (
              <button className="nv-btn-ghost" onClick={() => handleCopy(explanation, 'explanation')} style={{ fontSize: 13, padding: '8px 14px' }}>
                {copied === 'explanation' ? '✓ Copied!' : 'Copy Explanation'}
              </button>
            )}
          </div>
        )}
      </div>

      <style>{`
        @media (max-width: 768px) {
          .nl2sql-grid { grid-template-columns: 1fr !important; }
        }
      `}</style>
    </div>
  );
}