{# app/modules/dev_tools/templates/dev_tools/admin/sql-query.html #}
{% extends "admin/base.html" %}
{% from 'shared/macros/headers.html' import page_header %}
{% from 'shared/macros/modals.html' import modal %}

{% block title %}SQL Query Tool{% endblock %}

{% block alpine_data %}sqlQueryTool(){% endblock %}

{% block content %}
{{ page_header('SQL Query Tool', back_url='/admin/dashboard', back_label='Back to Dashboard') }}

<div class="flex gap-6">
    <!-- Left sidebar -->
    <div class="w-72 flex-shrink-0 space-y-4">
        <!-- Schema Explorer (preset queries) -->
        <div class="bg-white dark:bg-gray-800 rounded-lg shadow p-4">
            <button @click="showPresets = !showPresets"
                    class="flex items-center justify-between w-full text-sm font-semibold text-gray-700 dark:text-gray-300 uppercase tracking-wider">
                <span class="flex items-center gap-1.5">
                    <span x-html="$icon('database', 'w-4 h-4')"></span>
                    Schema Explorer
                </span>
                <span x-html="$icon(showPresets ? 'chevron-up' : 'chevron-down', 'w-4 h-4')"></span>
            </button>
            <div x-show="showPresets" x-collapse class="mt-3">
                <!-- Search filter -->
                <div class="mb-2">
                    <input type="text" x-model="presetSearch" placeholder="Filter presets..."
                           class="w-full text-xs rounded-md border-gray-300 dark:border-gray-600 dark:bg-gray-700 dark:text-gray-100 px-2 py-1.5 focus:ring-indigo-500 focus:border-indigo-500">
                </div>
                <template x-for="section in filteredPresetSections" :key="section.label">
                    <div class="mb-2">
                        <div class="text-[10px] font-bold text-indigo-500 dark:text-indigo-400 uppercase tracking-widest px-2 py-1"
                             x-text="section.label"></div>
                        <template x-for="group in section.groups" :key="group.category">
                            <div class="mb-1">
                                <button @click="toggleCategory(group.category)"
                                        class="flex items-center justify-between w-full text-xs font-semibold text-gray-400 dark:text-gray-500 uppercase px-2 py-1 rounded hover:text-gray-600 dark:hover:text-gray-300 transition-colors">
                                    <span x-text="group.category"></span>
                                    <span class="text-[10px] font-mono leading-none" x-text="isCategoryExpanded(group.category) ? '−' : '+'"></span>
                                </button>
                                <ul x-show="isCategoryExpanded(group.category)" x-collapse class="space-y-0.5 mt-0.5">
                                    <template x-for="preset in group.items" :key="preset.name">
                                        <li @click="loadPreset(preset)"
                                            class="flex items-center gap-1.5 rounded-md px-2 py-1 text-sm cursor-pointer text-gray-600 dark:text-gray-400 hover:bg-gray-100 dark:hover:bg-gray-700 hover:text-gray-900 dark:hover:text-gray-200 transition-colors">
                                            <span x-html="$icon('document-text', 'w-3.5 h-3.5 flex-shrink-0')"></span>
                                            <span class="truncate" x-text="preset.name"></span>
                                        </li>
                                    </template>
                                </ul>
                            </div>
                        </template>
                    </div>
                </template>
                <div x-show="presetSearch && filteredPresetSections.length === 0"
                     class="text-xs text-gray-400 px-2 py-2">No matching presets.</div>
            </div>
        </div>

        <!-- Saved Queries -->
        <div class="bg-white dark:bg-gray-800 rounded-lg shadow p-4">
            <h3 class="text-sm font-semibold text-gray-700 dark:text-gray-300 uppercase tracking-wider mb-3 flex items-center gap-1.5">
                <span x-html="$icon('collection', 'w-4 h-4')"></span>
                Saved Queries
            </h3>
            <div x-show="loadingSaved" class="text-sm text-gray-500">Loading...</div>
            <div x-show="!loadingSaved && savedQueries.length === 0" class="text-sm text-gray-400">
                No saved queries yet.
            </div>
            <ul class="space-y-1">
                <template x-for="q in savedQueries" :key="q.id">
                    <li class="group flex items-center justify-between rounded-md px-2 py-1.5 text-sm cursor-pointer hover:bg-gray-100 dark:hover:bg-gray-700 transition-colors"
                        :class="activeSavedId === q.id ? 'bg-indigo-50 dark:bg-indigo-900/30 text-indigo-700 dark:text-indigo-300' : 'text-gray-700 dark:text-gray-300'"
                        @click="loadQuery(q)">
                        <div class="flex-1 min-w-0">
                            <div class="truncate font-medium" x-text="q.name"></div>
                            <div class="text-xs text-gray-400" x-show="q.run_count > 0">
                                Run <span x-text="q.run_count"></span> time<span x-show="q.run_count !== 1">s</span>
                            </div>
                        </div>
                        <button @click.stop="deleteSavedQuery(q.id)"
                                class="opacity-0 group-hover:opacity-100 p-1 text-gray-400 hover:text-red-500 transition-opacity"
                                title="Delete">
                            <span x-html="$icon('trash', 'w-4 h-4')"></span>
                        </button>
                    </li>
                </template>
            </ul>
        </div>
    </div>

    <!-- Main area -->
    <div class="flex-1 min-w-0">
        <!-- SQL Editor -->
        <div class="bg-white dark:bg-gray-800 rounded-lg shadow mb-4">
            <div class="p-4">
                <textarea
                    x-model="sql"
                    rows="8"
                    class="w-full bg-gray-900 text-green-400 font-mono text-sm rounded-lg p-4 border border-gray-700 focus:border-indigo-500 focus:ring-1 focus:ring-indigo-500 resize-y"
                    placeholder="Enter your SQL query here... (SELECT only)"
                    spellcheck="false"
                ></textarea>
            </div>

            <!-- Action bar -->
            <div class="flex items-center gap-3 px-4 pb-4">
                <button @click="executeQuery()"
                        :disabled="running || !sql.trim()"
                        class="inline-flex items-center px-4 py-2 bg-indigo-600 text-white text-sm font-medium rounded-lg hover:bg-indigo-700 disabled:opacity-50 disabled:cursor-not-allowed transition-colors">
                    <span x-show="!running" x-html="$icon('play', 'w-4 h-4 mr-1.5')"></span>
                    <span x-show="running" x-html="$icon('spinner', 'w-4 h-4 mr-1.5')"></span>
                    <span x-text="running ? 'Running...' : 'Run Query'"></span>
                    <span class="ml-1.5 text-xs opacity-70">(Ctrl+Enter)</span>
                </button>

                <button @click="openSaveModal()"
                        :disabled="!sql.trim()"
                        class="inline-flex items-center px-4 py-2 bg-gray-600 text-white text-sm font-medium rounded-lg hover:bg-gray-700 disabled:opacity-50 disabled:cursor-not-allowed transition-colors">
                    <span x-html="$icon('save', 'w-4 h-4 mr-1.5')"></span>
                    Save Query
                </button>

                <button @click="exportCsv()"
                        x-show="rows.length > 0"
                        class="inline-flex items-center px-4 py-2 bg-green-600 text-white text-sm font-medium rounded-lg hover:bg-green-700 transition-colors">
                    <span x-html="$icon('download', 'w-4 h-4 mr-1.5')"></span>
                    Export CSV
                </button>
            </div>
        </div>

        <!-- Execution info -->
        <div x-show="executionTimeMs !== null" class="mb-4 text-sm text-gray-600 dark:text-gray-400 flex items-center gap-4">
            <span>
                <span x-text="rowCount"></span> row<span x-show="rowCount !== 1">s</span> returned
            </span>
            <span x-show="truncated" class="text-amber-600 dark:text-amber-400 font-medium">
                (results truncated to 1000 rows)
            </span>
            <span>
                <span x-text="executionTimeMs"></span> ms
            </span>
        </div>

        <!-- Error display -->
        <div x-show="error" class="mb-4 bg-red-50 dark:bg-red-900/30 border border-red-200 dark:border-red-800 rounded-lg p-4">
            <div class="flex items-start">
                <span x-html="$icon('exclamation-circle', 'w-5 h-5 text-red-500 mr-2 flex-shrink-0')"></span>
                <pre class="text-sm text-red-700 dark:text-red-300 font-mono whitespace-pre-wrap break-words" x-text="error"></pre>
            </div>
        </div>

        <!-- Results table -->
        <div x-show="columns.length > 0" class="bg-white dark:bg-gray-800 rounded-lg shadow overflow-hidden">
            <div class="overflow-x-auto">
                <table class="min-w-full divide-y divide-gray-200 dark:divide-gray-700">
                    <thead class="bg-gray-50 dark:bg-gray-900">
                        <tr>
                            <template x-for="col in columns" :key="col">
                                <th class="px-4 py-2 text-left text-xs font-semibold text-gray-600 dark:text-gray-400 uppercase tracking-wider whitespace-nowrap"
                                    x-text="col"></th>
                            </template>
                        </tr>
                    </thead>
                    <tbody class="divide-y divide-gray-100 dark:divide-gray-700">
                        <template x-for="(row, rowIdx) in rows" :key="rowIdx">
                            <tr class="hover:bg-gray-50 dark:hover:bg-gray-750">
                                <template x-for="(cell, cellIdx) in row" :key="cellIdx">
                                    <td class="px-4 py-2 text-sm font-mono whitespace-nowrap max-w-xs truncate"
                                        :class="isNull(cell) ? 'text-gray-400 italic' : 'text-gray-900 dark:text-gray-100'"
                                        :title="formatCell(cell)"
                                        x-text="formatCell(cell)"></td>
                                </template>
                            </tr>
                        </template>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Save Query Modal -->
{% call modal('saveQueryModal', 'Save Query', show_var='showSaveModal', size='sm', show_footer=false) %}
<div class="space-y-4">
    <div>
        <label class="block text-sm font-medium text-gray-700 dark:text-gray-300 mb-1">Name</label>
        <input type="text" x-model="saveName"
               class="w-full rounded-lg border-gray-300 dark:border-gray-600 dark:bg-gray-700 dark:text-gray-100 text-sm focus:ring-indigo-500 focus:border-indigo-500"
               placeholder="e.g. Active users count"
               @keydown.enter="saveQuery()">
    </div>
    <div>
        <label class="block text-sm font-medium text-gray-700 dark:text-gray-300 mb-1">Description (optional)</label>
        <input type="text" x-model="saveDescription"
               class="w-full rounded-lg border-gray-300 dark:border-gray-600 dark:bg-gray-700 dark:text-gray-100 text-sm focus:ring-indigo-500 focus:border-indigo-500"
               placeholder="Brief description of what this query does">
    </div>
    <div class="flex justify-end gap-3 pt-2">
        <button @click="showSaveModal = false"
                class="px-4 py-2 text-sm font-medium text-gray-700 dark:text-gray-300 bg-gray-100 dark:bg-gray-700 rounded-lg hover:bg-gray-200 dark:hover:bg-gray-600 transition-colors">
            Cancel
        </button>
        <button @click="saveQuery()"
                :disabled="!saveName.trim() || saving"
                class="px-4 py-2 text-sm font-medium text-white bg-indigo-600 rounded-lg hover:bg-indigo-700 disabled:opacity-50 transition-colors">
            <span x-text="saving ? 'Saving...' : 'Save'"></span>
        </button>
    </div>
</div>
{% endcall %}
{% endblock %}

{% block extra_scripts %}
<script defer src="{{ url_for('dev_tools_static', path='admin/js/sql-query.js') }}"></script>
{% endblock %}