{% extends "base.html" %}

{% block title %}SQL Лаборатория - DataAudit Platform{% endblock %}

{% block content %}
<link rel="stylesheet" href="{{ url_for('static', path='/css/bi.css') }}">


<div class="page-content">

    <!-- BI Tabs -->
    <nav class="bi-tabs">
        <a href="/analytics" class="bi-tab"><i data-lucide="home"></i> Обзор</a>
        <a href="/analytics/sql" class="bi-tab active"><i data-lucide="terminal"></i> SQL Лаборатория</a>
        <a href="/analytics/charts" class="bi-tab"><i data-lucide="bar-chart-2"></i> Графики</a>
        <a href="/analytics/dashboards" class="bi-tab"><i data-lucide="layout-dashboard"></i> Дашборды</a>
        {% if user and user.role.value in ('admin', 'manager') %}
        <a href="/analytics/datasources" class="bi-tab"><i data-lucide="database"></i> Источники</a>
        {% endif %}
    </nav>

    <!-- SQL Editor -->
    <div class="sql-lab">

        <!-- Editor Panel -->
        <div class="sql-editor-panel">
            <div class="sql-editor-header">
                <select id="ds-select"><option value="">Загрузка...</option></select>
                <button class="btn btn-primary btn-small" onclick="runSQL()" id="run-btn">
                    <i data-lucide="play" style="width:14px;height:14px;"></i> Выполнить
                </button>
                <span style="font-size:12px;color:var(--color-text-secondary);">Ctrl+Enter</span>
                <div style="flex:1"></div>
                <button class="btn btn-secondary btn-small" onclick="openSavedQueries()">
                    <i data-lucide="folder-open" style="width:14px;height:14px;"></i> Запросы
                </button>
            </div>
            <textarea id="sql-textarea" class="sql-textarea" 
                placeholder="SELECT * FROM config.platform_info LIMIT 100;"
                spellcheck="false"></textarea>
        </div>

        <!-- Results Panel -->
        <div class="sql-results-panel">
            <div class="sql-results-header">
                <div class="sql-results-stats" id="results-stats">
                    <span>Выполните SQL-запрос</span>
                </div>
                <div class="sql-results-actions" id="results-actions" style="display:none;">
                    <button class="btn btn-secondary btn-small" onclick="togglePreview()">
                        <i data-lucide="bar-chart-2" style="width:14px;height:14px;"></i> Визуализация
                    </button>
                    <button class="btn btn-secondary btn-small" onclick="exportCSV()">
                        <i data-lucide="download" style="width:14px;height:14px;"></i> CSV
                    </button>
                    <button class="btn btn-secondary btn-small" onclick="openSaveQueryModal()">
                        <i data-lucide="save" style="width:14px;height:14px;"></i> Сохранить
                    </button>
                    <button class="btn btn-primary btn-small" onclick="openSaveChartModal()">
                        <i data-lucide="plus" style="width:14px;height:14px;"></i> График
                    </button>
                </div>
            </div>
            <div class="sql-results-body" id="results-body">
                <div class="bi-empty-state">
                    <i data-lucide="terminal"></i>
                    <p>Результаты</p>
                    <span>Выберите источник данных и выполните SQL-запрос</span>
                </div>
            </div>
        </div>

        <!-- Chart Preview (hidden by default) -->
        <div id="chart-preview-wrapper" style="display:none; margin-top:16px;">
            <div class="chart-preview-panel">
                <div class="chart-preview-header">
                    <h3>Визуализация</h3>
                    <div style="display:flex;gap:4px;">
                        <button class="chart-type-btn active" data-type="bar" onclick="setPreviewType(this,'bar')">Bar</button>
                        <button class="chart-type-btn" data-type="line" onclick="setPreviewType(this,'line')">Line</button>
                        <button class="chart-type-btn" data-type="pie" onclick="setPreviewType(this,'pie')">Pie</button>
                        <button class="chart-type-btn" data-type="area" onclick="setPreviewType(this,'area')">Area</button>
                    </div>
                </div>
                <div class="chart-preview-body">
                    <div id="chart-preview-container" class="echarts-container"></div>
                </div>
            </div>
        </div>
    </div>

</div>

<!-- Save Query Modal -->
<div id="save-query-modal" class="modal" style="display:none;">
    <div class="modal-overlay" onclick="closeSaveQueryModal()"></div>
    <div class="modal-content modal-small">
        <div class="modal-header">
            <h2>Сохранить запрос</h2>
            <button class="modal-close" onclick="closeSaveQueryModal()">
                <i data-lucide="x" style="width:20px;height:20px;"></i>
            </button>
        </div>
        <div class="modal-body">
            <div class="bi-form-group">
                <label>Название</label>
                <input type="text" id="sq-title" placeholder="Мой запрос">
            </div>
            <div class="bi-form-group">
                <label>Описание</label>
                <textarea id="sq-description" rows="2" placeholder="Описание (необязательно)"></textarea>
            </div>
            <div class="bi-form-group">
                <div class="checkbox-row">
                    <input type="checkbox" id="sq-public"> <label for="sq-public">Публичный</label>
                </div>
            </div>
        </div>
        <div class="modal-actions">
            <button class="btn btn-secondary" onclick="closeSaveQueryModal()">Отмена</button>
            <button class="btn btn-primary" onclick="doSaveQuery()">Сохранить</button>
        </div>
    </div>
</div>

<!-- Save as Chart Modal -->
<div id="save-chart-modal" class="modal" style="display:none;">
    <div class="modal-overlay" onclick="closeSaveChartModal()"></div>
    <div class="modal-content modal-small">
        <div class="modal-header">
            <h2>Сохранить как график</h2>
            <button class="modal-close" onclick="closeSaveChartModal()">
                <i data-lucide="x" style="width:20px;height:20px;"></i>
            </button>
        </div>
        <div class="modal-body">
            <div class="bi-form-group">
                <label>Название</label>
                <input type="text" id="sc-title" placeholder="Мой график">
            </div>
            <div class="bi-form-group">
                <label>Тип графика</label>
                <select id="sc-type">
                    <option value="bar">Столбчатый</option>
                    <option value="line">Линейный</option>
                    <option value="pie">Круговой</option>
                    <option value="area">С заливкой</option>
                    <option value="scatter">Точечный</option>
                    <option value="table">Таблица</option>
                    <option value="metric">Метрика</option>
                </select>
            </div>
            <div class="bi-form-group">
                <label>Описание</label>
                <textarea id="sc-description" rows="2" placeholder="Описание (необязательно)"></textarea>
            </div>
            <div class="bi-form-group">
                <div class="checkbox-row">
                    <input type="checkbox" id="sc-public"> <label for="sc-public">Публичный</label>
                </div>
            </div>
        </div>
        <div class="modal-actions">
            <button class="btn btn-secondary" onclick="closeSaveChartModal()">Отмена</button>
            <button class="btn btn-primary" onclick="doSaveChart()">Создать график</button>
        </div>
    </div>
</div>

<!-- Saved Queries Drawer -->
<div id="queries-drawer-overlay" class="bi-drawer-overlay" onclick="closeSavedQueries()"></div>
<div id="queries-drawer" class="bi-drawer">
    <div class="bi-drawer-header">
        <h3>Сохранённые запросы</h3>
        <button class="modal-close" onclick="closeSavedQueries()">
            <i data-lucide="x" style="width:20px;height:20px;"></i>
        </button>
    </div>
    <div class="bi-drawer-body" id="queries-drawer-body">
        <div class="bi-spinner"></div>
    </div>
</div>

{% endblock %}

{% block scripts %}
<script src="https://cdn.jsdelivr.net/npm/echarts@5/dist/echarts.min.js"></script>
<script src="{{ url_for('static', path='/js/bi.js') }}"></script>
<script>
let _previewType = 'bar';
let _savedQueries = [];

document.addEventListener('DOMContentLoaded', async function() {
    lucide.createIcons();

    // Load datasources
    try {
        await DAP_BI.loadDatasources();
        DAP_BI.populateDatasourceSelect(document.getElementById('ds-select'));
    } catch (e) {
        console.error('Failed to load datasources:', e);
    }

    // Ctrl+Enter shortcut
    const ta = document.getElementById('sql-textarea');
    ta.addEventListener('keydown', function(e) {
        if (e.ctrlKey && e.key === 'Enter') {
            e.preventDefault();
            runSQL();
        }
        // Tab → 4 spaces
        if (e.key === 'Tab') {
            e.preventDefault();
            const s = this.selectionStart, end = this.selectionEnd;
            this.value = this.value.substring(0, s) + '    ' + this.value.substring(end);
            this.selectionStart = this.selectionEnd = s + 4;
        }
    });
});

async function runSQL() {
    const dsId = document.getElementById('ds-select').value;
    const sql = document.getElementById('sql-textarea').value.trim();
    if (!dsId) { alert('Выберите источник данных'); return; }
    if (!sql) { alert('Введите SQL-запрос'); return; }

    const btn = document.getElementById('run-btn');
    const stats = document.getElementById('results-stats');
    const body = document.getElementById('results-body');
    const actions = document.getElementById('results-actions');

    btn.disabled = true;
    stats.innerHTML = '<span class="bi-spinner"></span> Выполнение...';
    body.innerHTML = '';

    const t0 = performance.now();
    try {
        const data = await DAP_BI.runQuery(dsId, sql);
        const ms = Math.round(performance.now() - t0);

        if (data.error) {
            stats.innerHTML = `<span style="color:#991b1b;">Ошибка</span>`;
            body.innerHTML = `<div class="bi-error-msg">${DAP_BI._esc(data.message)}</div>`;
            actions.style.display = 'none';
        } else {
            stats.innerHTML = `
                <span><strong>${data.row_count}</strong> строк</span>
                <span>${ms} мс</span>
                ${data.truncated ? '<span style="color:#d97706;">⚠ обрезано</span>' : ''}
            `;
            DAP_BI.renderResultsTable(body, data);
            actions.style.display = 'flex';
        }
    } catch (err) {
        stats.innerHTML = '<span style="color:#991b1b;">Ошибка</span>';
        body.innerHTML = `<div class="bi-error-msg">${DAP_BI._esc(err.message)}</div>`;
        actions.style.display = 'none';
    }
    btn.disabled = false;
    lucide.createIcons();
}

function togglePreview() {
    const w = document.getElementById('chart-preview-wrapper');
    if (w.style.display === 'none') {
        w.style.display = 'block';
        const data = DAP_BI.getLastResult();
        if (data && !data.error) {
            const el = document.getElementById('chart-preview-container');
            DAP_BI.renderChart(el, _previewType, data, {});
        }
    } else {
        w.style.display = 'none';
    }
}

function setPreviewType(btn, type) {
    _previewType = type;
    document.querySelectorAll('#chart-preview-wrapper .chart-type-btn').forEach(b => b.classList.remove('active'));
    btn.classList.add('active');
    const data = DAP_BI.getLastResult();
    if (data && !data.error) {
        const el = document.getElementById('chart-preview-container');
        DAP_BI.renderChart(el, type, data, {});
    }
}

function exportCSV() {
    const data = DAP_BI.getLastResult();
    if (data) DAP_BI.exportCSV(data, 'dap-export.csv');
}

/* Save Query */
function openSaveQueryModal() { document.getElementById('save-query-modal').style.display = 'flex'; lucide.createIcons(); }
function closeSaveQueryModal() { document.getElementById('save-query-modal').style.display = 'none'; }

async function doSaveQuery() {
    const dsId = document.getElementById('ds-select').value;
    const sql = document.getElementById('sql-textarea').value.trim();
    const title = document.getElementById('sq-title').value.trim();
    if (!title) { alert('Введите название'); return; }

    try {
        await DAP_BI.saveQuery({
            title: title,
            description: document.getElementById('sq-description').value,
            datasource_id: parseInt(dsId) || null,
            sql_text: sql,
            is_public: document.getElementById('sq-public').checked,
        });
        closeSaveQueryModal();
        alert('Запрос сохранён');
    } catch (err) { alert('Ошибка: ' + err.message); }
}

/* Save as Chart */
function openSaveChartModal() { document.getElementById('save-chart-modal').style.display = 'flex'; lucide.createIcons(); }
function closeSaveChartModal() { document.getElementById('save-chart-modal').style.display = 'none'; }

async function doSaveChart() {
    const dsId = document.getElementById('ds-select').value;
    const sql = document.getElementById('sql-textarea').value.trim();
    const title = document.getElementById('sc-title').value.trim();
    if (!title) { alert('Введите название'); return; }
    if (!dsId) { alert('Выберите источник данных'); return; }

    try {
        const chart = await DAP_BI.saveChart({
            title: title,
            description: document.getElementById('sc-description').value,
            datasource_id: parseInt(dsId),
            sql_text: sql,
            chart_type: document.getElementById('sc-type').value,
            chart_config: {},
            is_public: document.getElementById('sc-public').checked,
        });
        closeSaveChartModal();
        window.location.href = `/analytics/charts/${chart.id}/edit`;
    } catch (err) { alert('Ошибка: ' + err.message); }
}

/* Saved Queries Drawer */
function openSavedQueries() {
    document.getElementById('queries-drawer').classList.add('open');
    document.getElementById('queries-drawer-overlay').classList.add('open');
    loadDrawerQueries();
}
function closeSavedQueries() {
    document.getElementById('queries-drawer').classList.remove('open');
    document.getElementById('queries-drawer-overlay').classList.remove('open');
}

async function loadDrawerQueries() {
    const container = document.getElementById('queries-drawer-body');
    container.innerHTML = '<div class="bi-spinner"></div>';
    try {
        _savedQueries = await DAP_BI.loadQueries();
        if (_savedQueries.length === 0) {
            container.innerHTML = '<div class="bi-empty-state"><p>Нет сохранённых запросов</p></div>';
            return;
        }
        container.innerHTML = _savedQueries.map((q, idx) => `
            <div class="bi-query-item" onclick="loadQueryByIndex(${idx})">
                <div class="bi-query-item-info">
                    <h4>${DAP_BI._esc(q.title)}</h4>
                    <span>${DAP_BI._esc(q.datasource_name || '—')} · ${DAP_BI.formatDate(q.updated_at)}</span>
                </div>
                <button class="btn btn-small btn-danger" onclick="event.stopPropagation(); deleteQueryItem(${q.id})" style="flex-shrink:0;">
                    <i data-lucide="trash-2" style="width:14px;height:14px;"></i>
                </button>
            </div>
        `).join('');
        lucide.createIcons();
    } catch (err) {
        container.innerHTML = `<div class="bi-error-msg">${DAP_BI._esc(err.message)}</div>`;
    }
}

function loadQueryByIndex(idx) {
    const q = _savedQueries[idx];
    if (!q) return;
    if (q.datasource_id) document.getElementById('ds-select').value = q.datasource_id;
    document.getElementById('sql-textarea').value = q.sql_text || '';
    closeSavedQueries();
}

async function deleteQueryItem(id) {
    if (!confirm('Удалить запрос?')) return;
    try {
        await DAP_BI.deleteQuery(id);
        loadDrawerQueries();
    } catch (err) { alert('Ошибка: ' + err.message); }
}
</script>
{% endblock %}