algospaced-ui/src/components/MySQLPlayground.tsx

import { useState, useEffect } from 'react';
import { supabase } from '../supabaseClient';

interface MySQLStatusData {
  active?: boolean;
  title: string;
  objective: string;
  points: number;
  challenge_type: string;
  schema: Record<string, Array<{ name: string; type: string }>>;
  completed: boolean;
  total_score: number;
}

export default function MySQLPlayground() {
  const [status, setStatus] = useState<MySQLStatusData | null>(null);
  const [query, setQuery] = useState('');
  const [running, setRunning] = useState(false);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState('');
  
  const [results, setResults] = useState<{
    columns: string[];
    rows: any[];
    rowsAffected: number;
    isCorrect?: boolean;
  } | null>(null);

  const fetchStatus = async () => {
    setLoading(true);
    setError('');
    try {
      const { data: { session } } = await supabase.auth.getSession();
      if (!session) {
        setError('Unauthenticated session');
        return;
      }
      const res = await fetch(`${import.meta.env.VITE_API_BASE_URL}/api/challenges/mysql/status`, {
        headers: { 'Authorization': `Bearer ${session.access_token}` }
      });
      if (res.ok) {
        const data = await res.json();
        if (data.active) {
          setStatus(data);
        } else {
          setStatus(null);
        }
      } else {
        setError('Failed to fetch playground status.');
      }
    } catch {
      setError('Connection timed out.');
    } finally {
      setLoading(false);
    }
  };

  const handleInit = async () => {
    setLoading(true);
    setError('');
    setResults(null);
    try {
      const { data: { session } } = await supabase.auth.getSession();
      if (!session) {
        setError('Unauthenticated session');
        return;
      }
      const res = await fetch(`${import.meta.env.VITE_API_BASE_URL}/api/challenges/mysql/init`, {
        method: 'POST',
        headers: { 'Authorization': `Bearer ${session.access_token}` }
      });
      if (res.ok) {
        const data = await res.json();
        setStatus({
          active: true,
          title: data.title,
          objective: data.objective,
          points: data.points,
          schema: data.schema,
          challenge_type: data.challenge_type,
          completed: false,
          total_score: data.total_score
        });
      } else {
        setError('Failed to initialize mock database playground.');
      }
    } catch {
      setError('Server connection error.');
    } finally {
      setLoading(false);
    }
  };

  const handleRunQuery = async () => {
    if (!query.trim()) return;
    setRunning(true);
    setError('');
    setResults(null);
    try {
      const { data: { session } } = await supabase.auth.getSession();
      if (!session) {
        setError('Unauthenticated session');
        return;
      }
      const res = await fetch(`${import.meta.env.VITE_API_BASE_URL}/api/challenges/mysql/query`, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${session.access_token}`
        },
        body: JSON.stringify({ query })
      });
      if (res.ok) {
        const data = await res.json();
        if (data.success) {
          setResults({
            columns: data.columns,
            rows: data.rows,
            rowsAffected: data.rows_affected,
            isCorrect: data.is_correct
          });
          if (data.is_correct) {
            setStatus(prev => prev ? { ...prev, completed: true, total_score: data.total_score } : null);
          }
          if (data.schema) {
            setStatus(prev => prev ? { ...prev, schema: data.schema } : null);
          }
        } else {
          setError(data.error);
        }
      } else {
        setError('Database server query failure.');
      }
    } catch {
      setError('Query connection lost.');
    } finally {
      setRunning(false);
    }
  };

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

  if (loading) {
    return (
      <div className="w-full h-full bg-paper-white p-6 flex flex-col items-center justify-center font-arcade text-[10px]">
        <div className="animate-bounce mb-2">INITIALIZING SQL PLAYGROUND...</div>
        <div className="w-48 h-4 bg-white border-[3px] border-ink-black overflow-hidden relative">
          <div className="absolute inset-y-0 left-0 bg-[#0ea5e9] w-1/2 animate-pulse border-r-2 border-ink-black" />
        </div>
      </div>
    );
  }

  // Dashboard state: no active database initialized
  if (!status) {
    return (
      <div className="w-full h-full bg-paper-white p-6 flex flex-col justify-between items-center text-center text-ink-black text-xs md:text-sm font-sans select-none">
        <div className="flex-1 flex flex-col justify-center items-center gap-4">
          <div className="w-16 h-16 bg-white border-[3px] border-ink-black flex items-center justify-center shadow-[4px_4px_0_0_#1E293B]">
            <span className="material-symbols-outlined text-4xl text-[#0ea5e9]" style={{ fontVariationSettings: "'FILL' 1" }}>
              database
            </span>
          </div>
          <h3 className="font-headline-md font-bold text-lg">Interactive MySQL Playground</h3>
          <p className="max-w-md text-slate-600 leading-relaxed font-semibold">
            Test and run relational queries against isolated schemas dynamically built by the AI generator. Includes full support for:
          </p>
          <div className="flex flex-wrap justify-center gap-2 font-mono text-[10px] text-slate-700 font-bold uppercase mt-1">
            <span className="bg-blue-100 border border-blue-400 px-2 py-0.5">SELECT / JOIN</span>
            <span className="bg-emerald-100 border border-emerald-400 px-2 py-0.5">UPDATE / DELETE</span>
            <span className="bg-purple-100 border border-purple-400 px-2 py-0.5">WINDOW FUNCTIONS</span>
          </div>
        </div>
        
        <div className="w-full border-t border-slate-200 pt-4 flex flex-col gap-2 shrink-0">
          <button
            onClick={handleInit}
            className="w-full py-2.5 bg-[#0ea5e9] hover:bg-sky-400 text-white font-window-title font-bold text-xs uppercase border-[3px] border-ink-black shadow-[4px_4px_0px_rgba(30,41,59,1)] active:translate-y-0.5 active:shadow-none transition-all cursor-pointer text-center"
          >
            CREATE NEW DATABASE INSTANCE
          </button>
        </div>
      </div>
    );
  }

  return (
    <div className="w-full h-full bg-paper-white text-ink-black flex flex-col overflow-hidden text-xs md:text-sm">
      {/* Play Workspace Scrollable Body */}
      <div className="flex-1 overflow-y-auto p-4 custom-scrollbar flex flex-col gap-4">
        
        {/* Objectives Badge Header */}
        <div className="flex justify-between items-center bg-slate-100 border-[3px] border-ink-black p-3 shrink-0">
          <div className="flex gap-2">
            <span className="bg-blue-100 text-blue-800 px-2 py-0.5 border-2 border-ink-black font-bold text-[9px] uppercase">
              {status.challenge_type}
            </span>
            <span className="bg-yellow-100 text-yellow-800 px-2 py-0.5 border-2 border-ink-black font-bold text-[9px]">
              +{status.points} PTS
            </span>
            {status.completed ? (
              <span className="bg-emerald-500 text-white px-2 py-0.5 border-2 border-ink-black font-bold text-[9px]">
                SOLVED
              </span>
            ) : (
              <span className="bg-rose-500 text-white px-2 py-0.5 border-2 border-ink-black font-bold text-[9px]">
                UNSOLVED
              </span>
            )}
          </div>
          <div className="font-arcade text-[8px] text-sky-700 font-bold bg-white border-2 border-ink-black px-2 py-0.5 shadow-[1px_1px_0_0_#1E293B]">
            SCORE: {status.total_score}
          </div>
        </div>

        {/* Objective banner */}
        <div className="border-[3px] border-yellow-400 bg-yellow-50 p-3 rounded font-sans leading-relaxed text-xs">
          <h4 className="font-arcade text-[9px] text-yellow-800 mb-1">MISSION OBJECTIVE</h4>
          <p className="font-bold text-slate-800">{status.objective}</p>
        </div>

        {/* Schema Tree */}
        <div className="border-[3px] border-ink-black bg-white p-3">
          <div className="font-bold text-[10px] border-b border-slate-300 pb-1 mb-2 flex items-center gap-1.5">
            <span className="material-symbols-outlined text-sm">schema</span>
            <span>SCHEMA EXPLORER (MOCK INSTANCE)</span>
          </div>
          <div className="grid grid-cols-2 gap-3 max-h-[140px] overflow-y-auto custom-scrollbar font-mono text-[10px]">
            {Object.entries(status.schema).map(([tableName, cols]) => (
              <div key={tableName} className="border border-slate-300 p-2 bg-slate-50">
                <div className="font-bold text-blue-700 border-b border-slate-200 pb-0.5 mb-1 flex items-center gap-1">
                  <span className="material-symbols-outlined text-[12px]">table_chart</span>
                  {tableName}
                </div>
                <ul className="space-y-0.5 text-slate-600 text-[9px]">
                  {cols.map((col, cIdx) => (
                    <li key={cIdx} className="flex justify-between">
                      <span>{col.name}</span>
                      <span className="text-slate-400 italic text-[8px]">{col.type}</span>
                    </li>
                  ))}
                </ul>
              </div>
            ))}
          </div>
        </div>

        {/* Query Input Editor */}
        <div className="flex flex-col min-h-[120px]">
          <div className="font-bold text-[10px] mb-1 tracking-wide uppercase flex items-center gap-1">
            <span className="material-symbols-outlined text-sm text-sky-600">terminal</span>
            <span>Enter SQL Query:</span>
          </div>
          <textarea
            value={query}
            onChange={(e) => setQuery(e.target.value)}
            className="flex-1 w-full bg-slate-900 text-[#4ADE80] font-mono p-3 border-[3px] border-ink-black rounded outline-none focus:ring-2 focus:ring-sky-500 text-xs leading-relaxed resize-none"
            placeholder="SELECT * FROM customers WHERE score > 100;"
            spellCheck="false"
          />
        </div>

        {/* Run Button bar */}
        <div className="flex gap-3">
          <button
            onClick={handleInit}
            className="flex-1 bg-white hover:bg-slate-100 text-ink-black border-[3px] border-ink-black font-bold font-window-title text-xs py-2 shadow-[3px_3px_0px_rgba(30,41,59,1)] active:translate-y-0.5 active:shadow-none cursor-pointer"
          >
            NEW PROBLEM
          </button>
          <button
            disabled={running}
            onClick={handleRunQuery}
            className="flex-[2] bg-highlight-pink text-white font-window-title font-bold py-2 border-[3px] border-ink-black shadow-[3px_3px_0px_rgba(30,41,59,1)] active:translate-y-0.5 active:shadow-none hover:bg-pink-400 transition-all disabled:opacity-50 cursor-pointer text-center text-xs"
          >
            {running ? 'EXECUTING SQL...' : 'EXECUTE SQL QUERY'}
          </button>
        </div>

        {/* Query Error Console */}
        {error && (
          <div className="border-[3px] border-red-500 bg-red-50 text-red-800 p-3 font-mono text-xs whitespace-pre-wrap">
            <div className="font-bold mb-1">SQL RUNTIME / SYNTAX ERROR:</div>
            {error}
          </div>
        )}

        {/* Tabular Result Grid */}
        {results && (
          <div className="border-[3px] border-ink-black bg-white p-3 flex flex-col">
            <div className="font-bold text-[10px] border-b border-slate-300 pb-1 mb-2 flex justify-between items-center">
              <span>QUERY OUTPUT BUFFER</span>
              {results.isCorrect && (
                <span className="text-green-600 font-bold animate-bounce text-[9px]">
                  ✓ MISSION ACHIEVED!
                </span>
              )}
            </div>

            <div className="text-[9px] text-slate-500 mb-2 font-mono">
              Rows Affected: {results.rowsAffected} | Total Returned: {results.rows.length}
            </div>

            {results.rows.length > 0 ? (
              <div className="overflow-x-auto border border-slate-300 max-h-[180px] custom-scrollbar">
                <table className="border-collapse w-full font-mono text-[9px] min-w-full">
                  <thead>
                    <tr className="bg-slate-100 border-b border-slate-300">
                      {results.columns.map((col, idx) => (
                        <th key={idx} className="border-r border-slate-300 p-1.5 text-left font-bold text-slate-700">
                          {col}
                        </th>
                      ))}
                    </tr>
                  </thead>
                  <tbody className="divide-y divide-slate-200">
                    {results.rows.map((row, rIdx) => (
                      <tr key={rIdx} className="hover:bg-slate-50">
                        {results.columns.map((col, cIdx) => (
                          <td key={cIdx} className="border-r border-slate-200 p-1.5 text-slate-800 whitespace-nowrap">
                            {row[col] !== null ? String(row[col]) : <span className="text-slate-300 italic">NULL</span>}
                          </td>
                        ))}
                      </tr>
                    ))}
                  </tbody>
                </table>
              </div>
            ) : (
              <div className="p-3 border border-dashed border-slate-300 text-center text-slate-400 italic font-sans text-xs">
                Query executed successfully. Result set is empty (or modification completed).
              </div>
            )}
          </div>
        )}
      </div>
    </div>
  );
}