avl_console/
query_builder.rs

1use axum::{
2    extract::State,
3    response::{Html, IntoResponse, Json},
4    routing::{get, post},
5    Router,
6};
7use serde::{Deserialize, Serialize};
8use std::sync::Arc;
9
10use crate::{error::ConsoleError, state::ConsoleState};
11
12/// Query Builder UI HTML with drag-and-drop interface
13const QUERY_BUILDER_HTML: &str = r#"<!DOCTYPE html>
14<html lang="pt-BR">
15<head>
16    <meta charset="UTF-8">
17    <meta name="viewport" content="width=device-width, initial-scale=1.0">
18    <title>Query Builder Visual - AVL Console</title>
19    <style>
20        * { margin: 0; padding: 0; box-sizing: border-box; }
21        body {
22            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
23            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
24            min-height: 100vh;
25            padding: 20px;
26        }
27        .container {
28            max-width: 1800px;
29            margin: 0 auto;
30            background: white;
31            border-radius: 20px;
32            padding: 30px;
33            box-shadow: 0 20px 60px rgba(0,0,0,0.3);
34        }
35        h1 {
36            color: #667eea;
37            margin-bottom: 10px;
38            font-size: 32px;
39        }
40        .subtitle {
41            color: #666;
42            margin-bottom: 30px;
43            font-size: 16px;
44        }
45        .builder-grid {
46            display: grid;
47            grid-template-columns: 250px 1fr;
48            gap: 20px;
49            margin-bottom: 20px;
50        }
51        .sidebar {
52            background: #f8f9fa;
53            border-radius: 12px;
54            padding: 20px;
55            border: 2px solid #e9ecef;
56        }
57        .sidebar h3 {
58            color: #495057;
59            margin-bottom: 15px;
60            font-size: 14px;
61            text-transform: uppercase;
62            letter-spacing: 0.5px;
63        }
64        .component-list {
65            display: flex;
66            flex-direction: column;
67            gap: 8px;
68        }
69        .component {
70            background: white;
71            border: 2px solid #dee2e6;
72            border-radius: 8px;
73            padding: 12px;
74            cursor: grab;
75            transition: all 0.2s;
76            font-size: 14px;
77            font-weight: 500;
78            color: #495057;
79        }
80        .component:hover {
81            border-color: #667eea;
82            background: #f0f3ff;
83            transform: translateY(-2px);
84        }
85        .component:active {
86            cursor: grabbing;
87        }
88        .component-icon {
89            margin-right: 8px;
90            font-size: 16px;
91        }
92        .canvas {
93            background: white;
94            border: 2px dashed #dee2e6;
95            border-radius: 12px;
96            min-height: 400px;
97            padding: 20px;
98            position: relative;
99        }
100        .canvas.drag-over {
101            background: #f0f3ff;
102            border-color: #667eea;
103        }
104        .canvas-placeholder {
105            text-align: center;
106            color: #adb5bd;
107            padding: 60px 20px;
108            pointer-events: none;
109        }
110        .query-component {
111            background: white;
112            border: 2px solid #667eea;
113            border-radius: 8px;
114            padding: 15px;
115            margin-bottom: 10px;
116            position: relative;
117            transition: all 0.2s;
118        }
119        .query-component:hover {
120            box-shadow: 0 4px 12px rgba(102, 126, 234, 0.2);
121        }
122        .component-header {
123            display: flex;
124            justify-content: space-between;
125            align-items: center;
126            margin-bottom: 10px;
127        }
128        .component-title {
129            font-weight: 600;
130            color: #667eea;
131            font-size: 14px;
132        }
133        .component-remove {
134            background: #dc3545;
135            color: white;
136            border: none;
137            border-radius: 4px;
138            padding: 4px 10px;
139            cursor: pointer;
140            font-size: 12px;
141        }
142        .component-remove:hover {
143            background: #c82333;
144        }
145        .component-content {
146            display: flex;
147            flex-direction: column;
148            gap: 10px;
149        }
150        .field-group {
151            display: flex;
152            gap: 10px;
153            align-items: center;
154        }
155        .field-group label {
156            font-size: 13px;
157            color: #6c757d;
158            min-width: 80px;
159        }
160        .field-group input,
161        .field-group select {
162            flex: 1;
163            padding: 8px 12px;
164            border: 1px solid #dee2e6;
165            border-radius: 6px;
166            font-size: 13px;
167        }
168        .field-group input:focus,
169        .field-group select:focus {
170            outline: none;
171            border-color: #667eea;
172        }
173        .action-bar {
174            display: flex;
175            gap: 15px;
176            padding: 20px;
177            background: #f8f9fa;
178            border-radius: 12px;
179            align-items: center;
180        }
181        .btn {
182            padding: 12px 24px;
183            border: none;
184            border-radius: 8px;
185            font-weight: 600;
186            cursor: pointer;
187            transition: all 0.2s;
188            font-size: 14px;
189        }
190        .btn-primary {
191            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
192            color: white;
193        }
194        .btn-primary:hover {
195            transform: translateY(-2px);
196            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.4);
197        }
198        .btn-secondary {
199            background: white;
200            color: #667eea;
201            border: 2px solid #667eea;
202        }
203        .btn-secondary:hover {
204            background: #f0f3ff;
205        }
206        .generated-query {
207            flex: 1;
208            background: #282c34;
209            color: #abb2bf;
210            padding: 15px;
211            border-radius: 8px;
212            font-family: 'Courier New', monospace;
213            font-size: 13px;
214            line-height: 1.6;
215            overflow-x: auto;
216        }
217        .results-section {
218            margin-top: 20px;
219            background: #f8f9fa;
220            border-radius: 12px;
221            padding: 20px;
222            display: none;
223        }
224        .results-section.active {
225            display: block;
226        }
227        .results-table {
228            width: 100%;
229            border-collapse: collapse;
230            background: white;
231            border-radius: 8px;
232            overflow: hidden;
233        }
234        .results-table th {
235            background: #667eea;
236            color: white;
237            padding: 12px;
238            text-align: left;
239            font-size: 13px;
240        }
241        .results-table td {
242            padding: 12px;
243            border-bottom: 1px solid #dee2e6;
244            font-size: 13px;
245        }
246        .results-table tbody tr:hover {
247            background: #f8f9fa;
248        }
249        .loading {
250            text-align: center;
251            padding: 40px;
252            color: #667eea;
253        }
254        .error-message {
255            background: #f8d7da;
256            color: #721c24;
257            padding: 15px;
258            border-radius: 8px;
259            border: 1px solid #f5c6cb;
260        }
261    </style>
262</head>
263<body>
264    <div class="container">
265        <h1>🎨 Query Builder Visual</h1>
266        <p class="subtitle">Construa queries SQL complexas com drag-and-drop - sem escrever código!</p>
267
268        <div class="builder-grid">
269            <div class="sidebar">
270                <h3>📦 Componentes</h3>
271                <div class="component-list">
272                    <div class="component" draggable="true" data-type="select">
273                        <span class="component-icon">🔍</span>SELECT
274                    </div>
275                    <div class="component" draggable="true" data-type="from">
276                        <span class="component-icon">📋</span>FROM
277                    </div>
278                    <div class="component" draggable="true" data-type="where">
279                        <span class="component-icon">🔎</span>WHERE
280                    </div>
281                    <div class="component" draggable="true" data-type="join">
282                        <span class="component-icon">🔗</span>JOIN
283                    </div>
284                    <div class="component" draggable="true" data-type="groupby">
285                        <span class="component-icon">📊</span>GROUP BY
286                    </div>
287                    <div class="component" draggable="true" data-type="orderby">
288                        <span class="component-icon">⬆️</span>ORDER BY
289                    </div>
290                    <div class="component" draggable="true" data-type="limit">
291                        <span class="component-icon">🔢</span>LIMIT
292                    </div>
293                </div>
294            </div>
295
296            <div class="canvas" id="canvas">
297                <div class="canvas-placeholder">
298                    <h3>👋 Arraste componentes aqui para começar</h3>
299                    <p>Construa sua query visual arrastando blocos da esquerda</p>
300                </div>
301            </div>
302        </div>
303
304        <div class="action-bar">
305            <button class="btn btn-primary" onclick="executeQuery()">▶️ Executar Query</button>
306            <button class="btn btn-secondary" onclick="clearCanvas()">🗑️ Limpar</button>
307            <div class="generated-query" id="generatedQuery">-- Sua query aparecerá aqui</div>
308        </div>
309
310        <div class="results-section" id="resultsSection">
311            <h3 style="margin-bottom: 15px; color: #495057;">📊 Resultados</h3>
312            <div id="resultsContent"></div>
313        </div>
314    </div>
315
316    <script>
317        let components = [];
318
319        // Drag and Drop handlers
320        document.querySelectorAll('.component').forEach(comp => {
321            comp.addEventListener('dragstart', (e) => {
322                e.dataTransfer.setData('componentType', e.target.dataset.type);
323            });
324        });
325
326        const canvas = document.getElementById('canvas');
327        canvas.addEventListener('dragover', (e) => {
328            e.preventDefault();
329            canvas.classList.add('drag-over');
330        });
331
332        canvas.addEventListener('dragleave', () => {
333            canvas.classList.remove('drag-over');
334        });
335
336        canvas.addEventListener('drop', (e) => {
337            e.preventDefault();
338            canvas.classList.remove('drag-over');
339            const type = e.dataTransfer.getData('componentType');
340            addComponent(type);
341        });
342
343        function addComponent(type) {
344            const id = Date.now();
345            const component = { id, type, config: {} };
346            components.push(component);
347            renderCanvas();
348            updateQuery();
349        }
350
351        function removeComponent(id) {
352            components = components.filter(c => c.id !== id);
353            renderCanvas();
354            updateQuery();
355        }
356
357        function updateComponentConfig(id, key, value) {
358            const comp = components.find(c => c.id === id);
359            if (comp) {
360                comp.config[key] = value;
361                updateQuery();
362            }
363        }
364
365        function renderCanvas() {
366            const placeholder = canvas.querySelector('.canvas-placeholder');
367            if (placeholder) placeholder.remove();
368
369            canvas.innerHTML = components.map(comp => {
370                const templates = {
371                    select: `
372                        <div class="field-group">
373                            <label>Colunas:</label>
374                            <input type="text" placeholder="*, id, nome, email"
375                                   value="${comp.config.columns || '*'}"
376                                   onchange="updateComponentConfig(${comp.id}, 'columns', this.value)">
377                        </div>
378                    `,
379                    from: `
380                        <div class="field-group">
381                            <label>Tabela:</label>
382                            <input type="text" placeholder="users, products, orders"
383                                   value="${comp.config.table || ''}"
384                                   onchange="updateComponentConfig(${comp.id}, 'table', this.value)">
385                        </div>
386                    `,
387                    where: `
388                        <div class="field-group">
389                            <label>Campo:</label>
390                            <input type="text" placeholder="age, status, category"
391                                   value="${comp.config.field || ''}"
392                                   onchange="updateComponentConfig(${comp.id}, 'field', this.value)">
393                        </div>
394                        <div class="field-group">
395                            <label>Operador:</label>
396                            <select onchange="updateComponentConfig(${comp.id}, 'operator', this.value)">
397                                <option value="=" ${comp.config.operator === '=' ? 'selected' : ''}>=</option>
398                                <option value="!=" ${comp.config.operator === '!=' ? 'selected' : ''}>!=</option>
399                                <option value=">" ${comp.config.operator === '>' ? 'selected' : ''}>&gt;</option>
400                                <option value="<" ${comp.config.operator === '<' ? 'selected' : ''}>&lt;</option>
401                                <option value=">=" ${comp.config.operator === '>=' ? 'selected' : ''}>&gt;=</option>
402                                <option value="<=" ${comp.config.operator === '<=' ? 'selected' : ''}>&lt;=</option>
403                                <option value="LIKE" ${comp.config.operator === 'LIKE' ? 'selected' : ''}>LIKE</option>
404                            </select>
405                        </div>
406                        <div class="field-group">
407                            <label>Valor:</label>
408                            <input type="text" placeholder="'value', 100, true"
409                                   value="${comp.config.value || ''}"
410                                   onchange="updateComponentConfig(${comp.id}, 'value', this.value)">
411                        </div>
412                    `,
413                    join: `
414                        <div class="field-group">
415                            <label>Tipo:</label>
416                            <select onchange="updateComponentConfig(${comp.id}, 'joinType', this.value)">
417                                <option value="INNER" ${comp.config.joinType === 'INNER' ? 'selected' : ''}>INNER</option>
418                                <option value="LEFT" ${comp.config.joinType === 'LEFT' ? 'selected' : ''}>LEFT</option>
419                                <option value="RIGHT" ${comp.config.joinType === 'RIGHT' ? 'selected' : ''}>RIGHT</option>
420                                <option value="FULL" ${comp.config.joinType === 'FULL' ? 'selected' : ''}>FULL</option>
421                            </select>
422                        </div>
423                        <div class="field-group">
424                            <label>Tabela:</label>
425                            <input type="text" placeholder="other_table"
426                                   value="${comp.config.table || ''}"
427                                   onchange="updateComponentConfig(${comp.id}, 'table', this.value)">
428                        </div>
429                        <div class="field-group">
430                            <label>Condição:</label>
431                            <input type="text" placeholder="table1.id = table2.user_id"
432                                   value="${comp.config.condition || ''}"
433                                   onchange="updateComponentConfig(${comp.id}, 'condition', this.value)">
434                        </div>
435                    `,
436                    groupby: `
437                        <div class="field-group">
438                            <label>Colunas:</label>
439                            <input type="text" placeholder="category, status"
440                                   value="${comp.config.columns || ''}"
441                                   onchange="updateComponentConfig(${comp.id}, 'columns', this.value)">
442                        </div>
443                    `,
444                    orderby: `
445                        <div class="field-group">
446                            <label>Coluna:</label>
447                            <input type="text" placeholder="created_at, name"
448                                   value="${comp.config.column || ''}"
449                                   onchange="updateComponentConfig(${comp.id}, 'column', this.value)">
450                        </div>
451                        <div class="field-group">
452                            <label>Ordem:</label>
453                            <select onchange="updateComponentConfig(${comp.id}, 'order', this.value)">
454                                <option value="ASC" ${comp.config.order === 'ASC' ? 'selected' : ''}>ASC</option>
455                                <option value="DESC" ${comp.config.order === 'DESC' ? 'selected' : ''}>DESC</option>
456                            </select>
457                        </div>
458                    `,
459                    limit: `
460                        <div class="field-group">
461                            <label>Limite:</label>
462                            <input type="number" placeholder="10, 100, 1000"
463                                   value="${comp.config.limit || '100'}"
464                                   onchange="updateComponentConfig(${comp.id}, 'limit', this.value)">
465                        </div>
466                    `
467                };
468
469                return `
470                    <div class="query-component">
471                        <div class="component-header">
472                            <div class="component-title">${comp.type.toUpperCase()}</div>
473                            <button class="component-remove" onclick="removeComponent(${comp.id})">✕</button>
474                        </div>
475                        <div class="component-content">
476                            ${templates[comp.type] || ''}
477                        </div>
478                    </div>
479                `;
480            }).join('');
481
482            if (components.length === 0) {
483                canvas.innerHTML = `
484                    <div class="canvas-placeholder">
485                        <h3>👋 Arraste componentes aqui para começar</h3>
486                        <p>Construa sua query visual arrastando blocos da esquerda</p>
487                    </div>
488                `;
489            }
490        }
491
492        function updateQuery() {
493            let query = '';
494
495            components.forEach(comp => {
496                switch(comp.type) {
497                    case 'select':
498                        query += `SELECT ${comp.config.columns || '*'}\n`;
499                        break;
500                    case 'from':
501                        query += `FROM ${comp.config.table || 'table_name'}\n`;
502                        break;
503                    case 'where':
504                        const op = comp.config.operator || '=';
505                        query += `WHERE ${comp.config.field || 'field'} ${op} ${comp.config.value || 'value'}\n`;
506                        break;
507                    case 'join':
508                        const joinType = comp.config.joinType || 'INNER';
509                        query += `${joinType} JOIN ${comp.config.table || 'table_name'} ON ${comp.config.condition || 'condition'}\n`;
510                        break;
511                    case 'groupby':
512                        query += `GROUP BY ${comp.config.columns || 'column'}\n`;
513                        break;
514                    case 'orderby':
515                        const order = comp.config.order || 'ASC';
516                        query += `ORDER BY ${comp.config.column || 'column'} ${order}\n`;
517                        break;
518                    case 'limit':
519                        query += `LIMIT ${comp.config.limit || '100'}\n`;
520                        break;
521                }
522            });
523
524            document.getElementById('generatedQuery').textContent = query || '-- Sua query aparecerá aqui';
525        }
526
527        function clearCanvas() {
528            components = [];
529            renderCanvas();
530            updateQuery();
531            document.getElementById('resultsSection').classList.remove('active');
532        }
533
534        async function executeQuery() {
535            const query = document.getElementById('generatedQuery').textContent;
536            if (query === '-- Sua query aparecerá aqui') {
537                alert('Adicione componentes para construir uma query primeiro!');
538                return;
539            }
540
541            const resultsSection = document.getElementById('resultsSection');
542            const resultsContent = document.getElementById('resultsContent');
543
544            resultsSection.classList.add('active');
545            resultsContent.innerHTML = '<div class="loading">⏳ Executando query...</div>';
546
547            try {
548                const response = await fetch('/query-builder/execute', {
549                    method: 'POST',
550                    headers: { 'Content-Type': 'application/json' },
551                    body: JSON.stringify({ query })
552                });
553
554                const data = await response.json();
555
556                if (data.error) {
557                    resultsContent.innerHTML = `<div class="error-message">❌ ${data.error}</div>`;
558                } else {
559                    renderResults(data);
560                }
561            } catch (error) {
562                resultsContent.innerHTML = `<div class="error-message">❌ Erro: ${error.message}</div>`;
563            }
564        }
565
566        function renderResults(data) {
567            const resultsContent = document.getElementById('resultsContent');
568
569            if (!data.rows || data.rows.length === 0) {
570                resultsContent.innerHTML = '<p style="text-align: center; color: #6c757d;">Nenhum resultado encontrado</p>';
571                return;
572            }
573
574            const columns = data.columns || Object.keys(data.rows[0]);
575
576            let html = `
577                <table class="results-table">
578                    <thead>
579                        <tr>${columns.map(col => `<th>${col}</th>`).join('')}</tr>
580                    </thead>
581                    <tbody>
582                        ${data.rows.map(row => `
583                            <tr>${columns.map(col => `<td>${row[col] !== null ? row[col] : '<em>null</em>'}</td>`).join('')}</tr>
584                        `).join('')}
585                    </tbody>
586                </table>
587                <p style="margin-top: 15px; color: #6c757d; font-size: 13px;">
588                    ✅ ${data.rows.length} resultado(s) em ${data.execution_time_ms || 0}ms
589                </p>
590            `;
591
592            resultsContent.innerHTML = html;
593        }
594    </script>
595</body>
596</html>"#;
597
598/// Query execution request
599#[derive(Debug, Deserialize)]
600struct ExecuteQueryRequest {
601    query: String,
602}
603
604/// Query execution response
605#[derive(Debug, Serialize)]
606struct ExecuteQueryResponse {
607    columns: Vec<String>,
608    rows: Vec<serde_json::Value>,
609    execution_time_ms: u64,
610    #[serde(skip_serializing_if = "Option::is_none")]
611    error: Option<String>,
612}
613
614/// Saved query template
615#[derive(Debug, Serialize, Deserialize, Clone)]
616pub struct QueryTemplate {
617    pub id: String,
618    pub name: String,
619    pub description: String,
620    pub query: String,
621    pub components: Vec<serde_json::Value>,
622    pub created_at: String,
623}
624
625/// Query Builder UI endpoint
626async fn query_builder_ui() -> impl IntoResponse {
627    Html(QUERY_BUILDER_HTML)
628}
629
630/// Execute a visual query
631async fn execute_query(
632    State(state): State<Arc<ConsoleState>>,
633    Json(payload): Json<ExecuteQueryRequest>,
634) -> Result<Json<ExecuteQueryResponse>, ConsoleError> {
635    let start = std::time::Instant::now();
636
637    // In production, this would execute against AvilaDB
638    // For now, simulate with mock data
639    let (columns, rows) = simulate_query_execution(&payload.query)?;
640
641    let execution_time_ms = start.elapsed().as_millis() as u64;
642
643    Ok(Json(ExecuteQueryResponse {
644        columns,
645        rows,
646        execution_time_ms,
647        error: None,
648    }))
649}
650
651/// Simulate query execution (replace with real AvilaDB calls)
652pub fn simulate_query_execution(
653    query: &str,
654) -> Result<(Vec<String>, Vec<serde_json::Value>), ConsoleError> {
655    // Mock data based on query content
656    if query.to_lowercase().contains("from users") {
657        Ok((
658            vec!["id".to_string(), "name".to_string(), "email".to_string()],
659            vec![
660                serde_json::json!({"id": 1, "name": "Alice", "email": "alice@example.com"}),
661                serde_json::json!({"id": 2, "name": "Bob", "email": "bob@example.com"}),
662                serde_json::json!({"id": 3, "name": "Charlie", "email": "charlie@example.com"}),
663            ],
664        ))
665    } else if query.to_lowercase().contains("from products") {
666        Ok((
667            vec![
668                "id".to_string(),
669                "name".to_string(),
670                "price".to_string(),
671                "stock".to_string(),
672            ],
673            vec![
674                serde_json::json!({"id": 1, "name": "Laptop", "price": 4999.99, "stock": 15}),
675                serde_json::json!({"id": 2, "name": "Mouse", "price": 79.99, "stock": 150}),
676                serde_json::json!({"id": 3, "name": "Keyboard", "price": 299.99, "stock": 75}),
677            ],
678        ))
679    } else {
680        Ok((
681            vec!["result".to_string()],
682            vec![serde_json::json!({"result": "Query executed successfully"})],
683        ))
684    }
685}
686
687/// List saved query templates
688async fn list_templates(
689    State(state): State<Arc<ConsoleState>>,
690) -> Result<Json<Vec<QueryTemplate>>, ConsoleError> {
691    // In production, load from database
692    let templates = vec![
693        QueryTemplate {
694            id: "tpl_1".to_string(),
695            name: "Active Users".to_string(),
696            description: "Get all active users from the last 30 days".to_string(),
697            query: "SELECT * FROM users WHERE active = true AND last_login > @date".to_string(),
698            components: vec![],
699            created_at: "2024-11-23T10:00:00Z".to_string(),
700        },
701        QueryTemplate {
702            id: "tpl_2".to_string(),
703            name: "Top Products".to_string(),
704            description: "Get top 10 products by sales".to_string(),
705            query: "SELECT * FROM products ORDER BY sales DESC LIMIT 10".to_string(),
706            components: vec![],
707            created_at: "2024-11-23T11:00:00Z".to_string(),
708        },
709    ];
710
711    Ok(Json(templates))
712}
713
714/// Save a query template
715async fn save_template(
716    State(state): State<Arc<ConsoleState>>,
717    Json(template): Json<QueryTemplate>,
718) -> Result<Json<QueryTemplate>, ConsoleError> {
719    // In production, save to database
720    Ok(Json(template))
721}
722
723/// Create router for query builder
724pub fn router(state: Arc<ConsoleState>) -> Router {
725    Router::new()
726        .route("/", get(query_builder_ui))
727        .route("/execute", post(execute_query))
728        .route("/templates", get(list_templates))
729        .route("/templates", post(save_template))
730        .with_state(state)
731}
732
733#[cfg(test)]
734mod tests {
735    use super::*;
736
737    #[test]
738    fn test_simulate_users_query() {
739        let (columns, rows) = simulate_query_execution("SELECT * FROM users").unwrap();
740        assert_eq!(columns.len(), 3);
741        assert_eq!(rows.len(), 3);
742    }
743
744    #[test]
745    fn test_simulate_products_query() {
746        let (columns, rows) = simulate_query_execution("SELECT * FROM products").unwrap();
747        assert_eq!(columns.len(), 4);
748        assert_eq!(rows.len(), 3);
749    }
750}
avl_console/query_builder.rs

avl_console/
query_builder.rs
