PlainSQL-Agent / index.html
LalitChaudhari3's picture
Update index.html
b5ffc55 verified
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PlainSQL | Enterprise Data Assistant</title>
<script src="https://cdn.tailwindcss.com"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<link href="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/themes/prism-tomorrow.min.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/prism.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/components/prism-sql.min.js"></script>
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600&family=JetBrains+Mono:wght@400;500&display=swap" rel="stylesheet">
<script>
tailwind.config = {
theme: {
extend: {
fontFamily: { sans: ['Inter', 'sans-serif'], mono: ['JetBrains Mono', 'monospace'] },
colors: {
dark: { 900: '#0B0C10', 800: '#15171E', 700: '#1F222E' },
brand: { 500: '#38bdf8', 400: '#0ea5e9' }
}
}
}
}
</script>
<style>
body { background-color: #0B0C10; color: #E2E8F0; }
.scrollbar-custom::-webkit-scrollbar { width: 8px; }
.scrollbar-custom::-webkit-scrollbar-track { background: #15171E; }
.scrollbar-custom::-webkit-scrollbar-thumb { background: #334155; border-radius: 4px; }
.bubble-user { background: linear-gradient(135deg, #38bdf8 0%, #0284c7 100%); color: white; border-radius: 16px 16px 4px 16px; }
.bubble-ai { background: #1F222E; border: 1px solid #2D3142; border-radius: 16px 16px 16px 4px; }
.custom-table th { background: #262A3B; color: #94A3B8; font-size: 0.75rem; padding: 12px; text-transform: uppercase; letter-spacing: 0.05em; }
.custom-table td { border-bottom: 1px solid #1F222E; color: #CBD5E1; padding: 12px; font-size: 0.9rem; }
.custom-table tr:last-child td { border-bottom: none; }
@keyframes fadeIn { from { opacity: 0; transform: translateY(10px); } to { opacity: 1; transform: translateY(0); } }
.animate-fade { animation: fadeIn 0.3s ease-out forwards; }
.typing-cursor::after { content: '▋'; animation: blink 1s step-start infinite; color: #38bdf8; margin-left: 2px; }
@keyframes blink { 50% { opacity: 0; } }
.scrollbar-hide::-webkit-scrollbar { display: none; }
.scrollbar-hide { -ms-overflow-style: none; scrollbar-width: none; }
</style>
</head>
<body class="flex h-screen overflow-hidden selection:bg-brand-500 selection:text-white relative font-sans">
<audio id="sound-welcome" src="https://assets.mixkit.co/active_storage/sfx/2568/2568-preview.mp3" preload="auto"></audio>
<audio id="sound-message" src="https://assets.mixkit.co/active_storage/sfx/2346/2346-preview.mp3" preload="auto"></audio>
<div id="splash-screen" class="fixed inset-0 z-[100] bg-dark-900 flex flex-col items-center justify-center transition-opacity duration-700">
<div class="text-center space-y-6 animate-fade">
<div class="w-20 h-20 bg-brand-500 rounded-2xl flex items-center justify-center shadow-2xl shadow-brand-500/50 mx-auto mb-6">
<svg class="w-10 h-10 text-white" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 7v10c0 2.21 3.58 4 8 4s8-1.79 8-4V7M4 7c0 2.21 3.58 4 8 4s8-1.79 8-4M4 7c0-2.21 3.58-4 8-4s8 1.79 8 4m0 5c0 2.21-3.58 4-8 4s-8-1.79-8-4"/></svg>
</div>
<h1 class="text-4xl font-bold text-white tracking-tight">Plain<span class="text-brand-400">SQL</span></h1>
<p class="text-gray-400 text-sm">Enterprise Text-to-SQL Engine</p>
<button onclick="enterSystem()" class="mt-8 px-8 py-3 bg-white text-dark-900 font-bold rounded-full hover:bg-gray-100 transition-all shadow-lg hover:scale-105 transform">
Initialize System
</button>
</div>
</div>
<div id="chart-modal" class="fixed inset-0 z-50 flex items-center justify-center bg-black/80 backdrop-blur-sm hidden opacity-0 transition-opacity duration-300">
<div class="bg-dark-800 border border-dark-700 w-full max-w-4xl h-[600px] rounded-2xl shadow-2xl flex flex-col p-6 transform scale-95 transition-transform duration-300" id="chart-content">
<div class="flex justify-between items-center mb-4">
<h3 class="text-lg font-semibold text-white">Data Visualization</h3>
<button onclick="closeChart()" class="text-gray-500 hover:text-white p-2 text-xl"></button>
</div>
<div class="flex-1 relative w-full h-full"><canvas id="myChart"></canvas></div>
</div>
</div>
<aside class="w-64 bg-dark-800 border-r border-dark-700 flex flex-col hidden md:flex">
<div class="p-6 flex items-center gap-3">
<div class="w-8 h-8 bg-brand-500 rounded-lg flex items-center justify-center shadow-lg">
<svg class="w-5 h-5 text-white" fill="none" stroke="currentColor" viewBox="0 0 24 24"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 7v10c0 2.21 3.58 4 8 4s8-1.79 8-4V7M4 7c0 2.21 3.58 4 8 4s8-1.79 8-4M4 7c0-2.21 3.58-4 8-4s8 1.79 8 4m0 5c0 2.21-3.58 4-8 4s-8-1.79-8-4"/></svg>
</div>
<span class="font-bold text-xl tracking-tight">PlainSQL</span>
</div>
<nav class="flex-1 px-4 space-y-2 overflow-y-auto scrollbar-custom">
<button onclick="window.location.reload()" class="w-full flex items-center gap-3 px-4 py-3 bg-brand-500/10 text-brand-400 rounded-xl border border-brand-500/20 hover:bg-brand-500/20 transition-all group">
<svg class="w-5 h-5 group-hover:rotate-90 transition-transform" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M12 6v6m0 0v6m0-6h6m-6 0H6"/></svg>
<span class="text-sm font-medium">New Analysis</span>
</button>
<div class="mt-8">
<p class="px-4 text-xs font-semibold text-gray-500 uppercase tracking-wider mb-2">Recent Queries</p>
<div id="history-list" class="space-y-1"></div>
</div>
</nav>
<div class="p-4 border-t border-dark-700">
<div class="flex items-center gap-3 px-3 py-2 bg-green-500/10 rounded-lg border border-green-500/20">
<div class="w-2 h-2 bg-green-500 rounded-full animate-pulse"></div>
<div class="flex flex-col">
<span class="text-[11px] font-bold text-green-400 uppercase">System Online</span>
<span class="text-[10px] text-gray-500">Read-Only Mode</span>
</div>
</div>
</div>
</aside>
<main class="flex-1 flex flex-col relative bg-dark-900">
<div id="chat-box" class="flex-1 overflow-y-auto p-4 md:p-8 space-y-6 pb-80 scroll-smooth scrollbar-custom">
<div class="flex gap-4 max-w-3xl mx-auto animate-fade">
<div class="w-8 h-8 rounded-full bg-brand-500/20 flex items-center justify-center flex-shrink-0 border border-brand-500/30 text-brand-400">
<svg class="w-5 h-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M13 10V3L4 14h7v7l9-11h-7z"/></svg>
</div>
<div class="bubble-ai p-5 shadow-sm">
<p class="text-sm leading-relaxed text-gray-200">
Hello. I am <strong>PlainSQL</strong>. I can access your database securely to fetch real-time insights.<br><br>
<em>Try asking: "Show me top 5 employees by salary" or "List all active users."</em>
</p>
</div>
</div>
</div>
<div class="absolute bottom-0 w-full p-4 md:p-6 bg-gradient-to-t from-dark-900 via-dark-900 to-transparent z-20">
<div class="max-w-3xl mx-auto">
<div id="suggestions" class="flex gap-2 mb-3 overflow-x-auto pb-1 scrollbar-hide"></div>
<form id="chat-form" class="relative group">
<div class="absolute inset-0 bg-brand-500/20 rounded-2xl blur-lg group-hover:bg-brand-500/30 transition-all opacity-0 group-hover:opacity-100"></div>
<input type="text" id="user-input"
class="relative w-full bg-dark-800 text-white border border-dark-700 rounded-2xl py-4 pl-5 pr-14 focus:outline-none focus:border-brand-500 focus:ring-1 focus:ring-brand-500 transition-all placeholder-gray-500 shadow-xl"
placeholder="Ask a question in plain English..." autocomplete="off">
<button type="submit" class="absolute right-2 top-2 p-2 bg-brand-500 hover:bg-brand-400 text-white rounded-xl transition-all shadow-lg active:scale-95">
<svg class="w-5 h-5" fill="none" stroke="currentColor" viewBox="0 0 24 24"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M14 5l7 7m0 0l-7 7m7-7H3"/></svg>
</button>
</form>
<div class="text-center mt-2">
<p class="text-[10px] text-gray-600">AI Generated SQL can be inaccurate. Always verify important data.</p>
</div>
</div>
</div>
</main>
<script>
// --- 🚀 KEY CHANGE: DYNAMIC URL FOR DEPLOYMENT ---
const API_URL = window.location.origin + "/chat";
const form = document.getElementById('chat-form');
const input = document.getElementById('user-input');
const chatBox = document.getElementById('chat-box');
const suggestionsBox = document.getElementById('suggestions');
const historyList = document.getElementById('history-list');
const soundWelcome = document.getElementById('sound-welcome');
const soundMessage = document.getElementById('sound-message');
const splashScreen = document.getElementById('splash-screen');
const modal = document.getElementById('chart-modal');
const modalContent = document.getElementById('chart-content');
let chartInstance = null;
let conversationHistory = [];
function unlockAudio() {
soundMessage.volume = 0;
soundMessage.play().then(() => {
soundMessage.pause();
soundMessage.currentTime = 0;
}).catch(() => {});
}
function playIncomingSound() {
soundMessage.volume = 0.4;
soundMessage.currentTime = 0;
soundMessage.play().catch(e => console.warn("Audio blocked:", e));
}
function enterSystem() {
soundWelcome.volume = 0.5;
soundWelcome.play().catch(e => console.log("Init Audio Error:", e));
splashScreen.style.opacity = '0';
setTimeout(() => { splashScreen.style.display = 'none'; }, 700);
setTimeout(() => input.focus(), 800);
}
function checkGreeting(text) {
const t = text.toLowerCase();
const greetings = ['hello', 'hi', 'hey', 'good morning', 'good afternoon', 'hola'];
if (greetings.some(g => t === g || t.startsWith(g + ' '))) {
return "Hello! 👋 I am **PlainSQL**, your data assistant. I'm here to help you query your database without writing code.<br><br>You can ask me things like: <em>'Show me top 5 employees by salary'</em> or <em>'List all active users.'</em>";
}
return null;
}
function scrollToBottom() {
setTimeout(() => {
chatBox.scrollTop = chatBox.scrollHeight;
}, 50);
}
async function typeText(element, text) {
element.classList.add('typing-cursor');
return new Promise(resolve => {
setTimeout(() => {
element.innerHTML = text;
element.classList.remove('typing-cursor');
resolve();
}, 300 + Math.random() * 500);
});
}
function showLoading() {
const id = 'loading-' + Date.now();
const html = `
<div id="${id}" class="flex gap-4 max-w-3xl mx-auto animate-fade">
<div class="w-8 h-8 rounded-full bg-brand-500/20 flex items-center justify-center text-brand-400">
<svg class="w-4 h-4 animate-spin" fill="none" viewBox="0 0 24 24"><circle class="opacity-25" cx="12" cy="12" r="10" stroke="currentColor" stroke-width="4"></circle><path class="opacity-75" fill="currentColor" d="M4 12a8 8 0 018-8V0C5.373 0 0 5.373 0 12h4zm2 5.291A7.962 7.962 0 014 12H0c0 3.042 1.135 5.824 3 7.938l3-2.647z"></path></svg>
</div>
<div class="bubble-ai p-4 flex gap-2 items-center">
<span class="text-xs text-gray-400">Analyzing database schema...</span>
</div>
</div>`;
chatBox.insertAdjacentHTML('beforeend', html);
scrollToBottom();
return id;
}
async function appendMessage(role, content, isHtml = false) {
const wrapper = document.createElement('div');
wrapper.className = `flex gap-4 max-w-3xl mx-auto animate-fade ${role === 'user' ? 'justify-end' : ''}`;
const aiIcon = `<div class="w-8 h-8 rounded-full bg-brand-500/20 flex items-center justify-center flex-shrink-0 border border-brand-500/30 text-brand-400"><svg class="w-5 h-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M13 10V3L4 14h7v7l9-11h-7z"/></svg></div>`;
const userIcon = `<div class="w-8 h-8 rounded-full bg-gray-700 flex items-center justify-center flex-shrink-0 text-gray-300"><svg class="w-5 h-5" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M16 7a4 4 0 11-8 0 4 4 0 018 0zM12 14a7 7 0 00-7 7h14a7 7 0 00-7-7z"/></svg></div>`;
const bubble = document.createElement('div');
bubble.className = role === 'user' ? 'bubble-user p-4 max-w-[85%] shadow-lg text-sm' : 'bubble-ai p-5 max-w-[90%] shadow-sm w-full text-sm leading-relaxed';
if (role === 'user') {
bubble.textContent = content;
wrapper.innerHTML = bubble.outerHTML + userIcon;
} else {
wrapper.innerHTML = aiIcon + bubble.outerHTML;
}
chatBox.appendChild(wrapper);
if (role === 'ai') {
playIncomingSound();
if (isHtml) {
wrapper.querySelector('.bubble-ai').innerHTML = content;
Prism.highlightAll();
} else {
await typeText(wrapper.querySelector('.bubble-ai'), content);
}
}
scrollToBottom();
}
function renderSuggestions(questions) {
suggestionsBox.innerHTML = '';
if (!questions) return;
questions.forEach((q, i) => {
const btn = document.createElement('button');
btn.className = "whitespace-nowrap px-4 py-1.5 bg-dark-800 border border-dark-700 hover:border-brand-500 hover:text-brand-400 text-gray-400 text-xs font-medium rounded-full transition-all animate-fade";
btn.style.animationDelay = `${i * 0.1}s`;
btn.textContent = q;
btn.onclick = () => { input.value = q; form.dispatchEvent(new Event('submit')); };
suggestionsBox.appendChild(btn);
});
scrollToBottom();
}
function addToHistory(question) {
const btn = document.createElement('button');
btn.className = "w-full text-left px-4 py-2 text-xs text-gray-400 hover:text-white hover:bg-dark-700 rounded-lg transition-colors truncate animate-fade";
btn.textContent = question;
btn.onclick = () => { input.value = question; form.dispatchEvent(new Event('submit')); };
historyList.prepend(btn);
}
function downloadCSV(dataString) {
try {
const data = JSON.parse(decodeURIComponent(dataString));
if (!data || !data.length) return;
const headers = Object.keys(data[0]);
const csvRows = [];
csvRows.push(headers.join(','));
for (const row of data) {
const values = headers.map(header => {
const escaped = ('' + row[header]).replace(/"/g, '\\"');
return `"${escaped}"`;
});
csvRows.push(values.join(','));
}
const blob = new Blob([csvRows.join('\n')], { type: 'text/csv' });
const url = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.setAttribute('hidden', '');
a.setAttribute('href', url);
a.setAttribute('download', 'data_export.csv');
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
} catch (e) {
console.error("Export failed", e);
}
}
form.addEventListener('submit', async (e) => {
e.preventDefault();
const question = input.value.trim();
if (!question) return;
unlockAudio();
suggestionsBox.innerHTML = '';
input.value = '';
await appendMessage('user', question);
addToHistory(question);
const greetingResponse = checkGreeting(question);
if (greetingResponse) {
setTimeout(() => appendMessage('ai', greetingResponse, true), 500);
return;
}
const loadingId = showLoading();
try {
const res = await fetch(`${API_URL}?ts=${Date.now()}`, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({ question, history: conversationHistory })
});
if (!res.ok) throw new Error("Backend connection failed.");
const data = await res.json();
document.getElementById(loadingId).remove();
if (data.sql && !data.sql.includes("Error")) {
conversationHistory.push({ user: question, sql: data.sql });
if(conversationHistory.length > 5) conversationHistory.shift();
}
let content = "";
if (data.message) content += `<div class="mb-4">${data.message}</div>`;
if (Array.isArray(data.answer) && data.answer.length > 0) {
const firstRow = data.answer[0];
if (typeof firstRow === 'string' && (firstRow.toLowerCase().includes("error"))) {
content += `<div class="p-4 bg-red-500/10 border border-red-500/20 rounded-lg text-red-400 font-mono text-xs mb-3">⚠️ ${firstRow}</div>`;
} else {
const headers = Object.keys(firstRow);
const dataStr = encodeURIComponent(JSON.stringify(data.answer));
content += `
<div class="overflow-hidden rounded-xl border border-dark-700 shadow-xl mb-3 bg-[#15171E]">
<div class="overflow-x-auto">
<table class="w-full text-left custom-table">
<thead><tr>${headers.map(h => `<th>${h}</th>`).join('')}</tr></thead>
<tbody>${data.answer.map(row => `<tr>${headers.map(h => `<td>${row[h]}</td>`).join('')}</tr>`).join('')}</tbody>
</table>
</div>
</div>
<div class="flex gap-2 mb-4">
<button onclick="openChart('${dataStr}')" class="flex items-center gap-2 px-3 py-2 bg-brand-500 text-white text-xs font-bold rounded-lg hover:bg-brand-400 transition-colors">
<svg class="w-4 h-4" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M11 3.055A9.001 9.001 0 1020.945 13H11V3.055z"/></svg>
Visualize
</button>
<button onclick="downloadCSV('${dataStr}')" class="flex items-center gap-2 px-3 py-2 bg-dark-700 border border-dark-600 text-gray-300 text-xs font-bold rounded-lg hover:bg-dark-600 transition-colors">
<svg class="w-4 h-4" fill="none" viewBox="0 0 24 24" stroke="currentColor"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 16v1a3 3 0 003 3h10a3 3 0 003-3v-1m-4-4l-4 4m0 0l-4-4m4 4V4"/></svg>
Export CSV
</button>
</div>
`;
}
} else if (Array.isArray(data.answer) && data.answer.length === 0) {
content += `<div class="p-4 bg-yellow-500/5 border border-yellow-500/10 rounded-lg text-yellow-500/80 text-xs mb-3">No records found matching query.</div>`;
}
if (data.sql) {
content += `
<div class="relative group mt-2">
<div class="absolute -top-3 left-3 bg-dark-700 px-2 text-[10px] text-gray-400 rounded border border-dark-600">Generated SQL</div>
<pre class="!m-0 !p-4 !bg-[#0d1117] !text-xs rounded-xl border border-dark-700/50"><code class="language-sql">${data.sql}</code></pre>
</div>`;
}
if (content) await appendMessage('ai', content, true);
if (data.follow_ups) renderSuggestions(data.follow_ups);
} catch (err) {
document.getElementById(loadingId)?.remove();
await appendMessage('ai', `<div class="p-4 bg-red-900/20 border border-red-500/30 rounded-lg text-red-400 text-sm">Error: ${err.message}</div>`, true);
}
});
function openChart(dataString) {
const data = JSON.parse(decodeURIComponent(dataString));
const headers = Object.keys(data[0]);
let labelKey = headers.find(h => isNaN(data[0][h])) || headers[0];
let valueKey = headers.find(h => !isNaN(data[0][h]) && h !== labelKey) || headers[1];
const labels = data.map(row => row[labelKey]);
const values = data.map(row => row[valueKey]);
modal.classList.remove('hidden');
setTimeout(() => { modal.classList.remove('opacity-0'); modalContent.classList.remove('scale-95'); modalContent.classList.add('scale-100'); }, 10);
if (chartInstance) chartInstance.destroy();
const ctx = document.getElementById('myChart').getContext('2d');
chartInstance = new Chart(ctx, {
type: labels.length > 8 ? 'bar' : 'doughnut',
data: {
labels: labels,
datasets: [{
label: valueKey.toUpperCase(),
data: values,
backgroundColor: ['#38bdf8', '#a855f7', '#ec4899', '#22c55e', '#eab308'],
borderColor: '#15171E', borderWidth: 2
}]
},
options: { responsive: true, maintainAspectRatio: false, plugins: { legend: { position: 'bottom', labels: { color: '#94A3B8' } } }, scales: { x: { display: false }, y: { display: false } } }
});
}
function closeChart() {
modal.classList.add('opacity-0'); modalContent.classList.remove('scale-100'); modalContent.classList.add('scale-95'); setTimeout(() => { modal.classList.add('hidden'); }, 300);
}
</script>
</body>
</html>