{% extends "base.html" %}

{% block title %}Query Builder - Analytics{% endblock %}

{% block head %}
<link rel="stylesheet" href="/static/plugins/bi/bi.css">
{% endblock %}

{% block content %}
<div class="studio-shell" style="display:flex;height:100%;overflow:hidden">
    {% include "plugins/bi/partials/bi_sidebar.html" %}

    <main style="flex:1;overflow-y:auto;padding:24px" x-data="queryBuilder()">
        <!-- Header -->
        <div class="flex items-center justify-between" style="margin-bottom:24px">
            <div class="flex items-center gap-3">
                <a href="/bi/queries" class="btn btn-sm btn-ghost" style="padding:4px">
                    <i data-lucide="arrow-left"></i>
                </a>
                <h1 class="flex items-center gap-2" style="font-family:var(--serif);font-size:28px;letter-spacing:-0.01em">
                    <i data-lucide="wand-2"></i>
                    Visual Query Builder
                </h1>
            </div>
            <div class="flex items-center gap-2">
                <button @click="generateSQL()" class="btn btn-brand">
                    <i data-lucide="code"></i> Generate SQL
                </button>
                <button @click="openInEditor()" x-show="generatedSQL" class="btn btn-ghost" style="color:var(--green)">
                    <i data-lucide="external-link"></i> Open in Editor
                </button>
            </div>
        </div>

        <div class="grid grid-cols-3 gap-6">
            <!-- Left: Builder Steps -->
            <div class="col-span-2" style="display:flex;flex-direction:column;gap:16px">
                <!-- Step 1: Source -->
                <div class="card" style="padding:16px">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:var(--brand-soft);color:var(--brand)">1</span>
                        Data Source
                    </h3>
                    <select x-model="sourceId" @change="loadTables()" style="width:100%">
                        <option value="">Select a source...</option>
                        {% for s in sources %}
                        <option value="{{ s.id }}">{{ s.name }} ({{ s.source_type }})</option>
                        {% endfor %}
                    </select>
                </div>

                <!-- Step 2: Table -->
                <div class="card" style="padding:16px" x-show="tables.length > 0">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:var(--brand-soft);color:var(--brand)">2</span>
                        Table
                    </h3>
                    <select x-model="table" @change="loadSchema()" style="width:100%">
                        <option value="">Select a table...</option>
                        <template x-for="t in tables" :key="t">
                            <option :value="t" x-text="t"></option>
                        </template>
                    </select>
                </div>

                <!-- Step 3: Columns -->
                <div class="card" style="padding:16px" x-show="schema.length > 0">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:var(--brand-soft);color:var(--brand)">3</span>
                        Columns
                        <span class="text-xs" style="font-weight:400;color:var(--fg-4)">(leave empty for all)</span>
                    </h3>
                    <div class="flex flex-wrap gap-2">
                        <template x-for="col in schema" :key="col.name">
                            <label class="flex items-center gap-1 text-xs"
                                   style="padding:4px 8px;border-radius:6px;cursor:pointer;background:var(--surface-2);border:1px solid var(--border)"
                                   :style="selectedColumns.includes(col.name) ? 'border-color:var(--brand);background:var(--brand-soft)' : ''">
                                <input type="checkbox" :value="col.name"
                                       x-model="selectedColumns"
                                       class="rounded w-3 h-3" style="accent-color:var(--brand)">
                                <span x-text="col.name"></span>
                                <span style="color:var(--fg-4)" x-text="'(' + col.type + ')'"></span>
                            </label>
                        </template>
                    </div>
                </div>

                <!-- Step 4: Aggregates -->
                <div class="card" style="padding:16px" x-show="schema.length > 0">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:rgba(111,90,214,0.15);color:var(--accent-violet)">4</span>
                        Aggregates
                        <span class="text-xs" style="font-weight:400;color:var(--fg-4)">(optional)</span>
                    </h3>
                    <template x-for="(agg, i) in aggregates" :key="i">
                        <div class="flex items-center gap-2" style="margin-bottom:8px">
                            <select x-model="agg.function">
                                <option value="COUNT">COUNT</option>
                                <option value="SUM">SUM</option>
                                <option value="AVG">AVG</option>
                                <option value="MIN">MIN</option>
                                <option value="MAX">MAX</option>
                            </select>
                            <select x-model="agg.column" class="flex-1">
                                <option value="*">*</option>
                                <template x-for="col in schema" :key="col.name">
                                    <option :value="col.name" x-text="col.name"></option>
                                </template>
                            </select>
                            <button @click="aggregates.splice(i, 1)" class="btn btn-sm btn-ghost" style="padding:2px">
                                <i data-lucide="x"></i>
                            </button>
                        </div>
                    </template>
                    <button @click="aggregates.push({function: 'COUNT', column: '*'})" class="text-xs" style="color:var(--brand);background:transparent;border:none;cursor:pointer">+ Add Aggregate</button>
                </div>

                <!-- Step 5: Filters -->
                <div class="card" style="padding:16px" x-show="schema.length > 0">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:rgba(176,119,0,0.15);color:var(--accent-amber)">5</span>
                        Filters
                        <span class="text-xs" style="font-weight:400;color:var(--fg-4)">(optional)</span>
                    </h3>
                    <template x-for="(f, i) in filters" :key="i">
                        <div class="flex items-center gap-2" style="margin-bottom:8px">
                            <select x-model="f.column">
                                <template x-for="col in schema" :key="col.name">
                                    <option :value="col.name" x-text="col.name"></option>
                                </template>
                            </select>
                            <select x-model="f.operator">
                                <option value="=">=</option>
                                <option value="!=">!=</option>
                                <option value=">">></option>
                                <option value="<"><</option>
                                <option value="LIKE">LIKE</option>
                                <option value="IS NULL">IS NULL</option>
                                <option value="IS NOT NULL">IS NOT NULL</option>
                            </select>
                            <input type="text" x-model="f.value" x-show="!['IS NULL','IS NOT NULL'].includes(f.operator)"
                                   class="flex-1" placeholder="value">
                            <button @click="filters.splice(i, 1)" class="btn btn-sm btn-ghost" style="padding:2px">
                                <i data-lucide="x"></i>
                            </button>
                        </div>
                    </template>
                    <button @click="filters.push({column: schema[0]?.name || '', operator: '=', value: ''})" class="text-xs" style="color:var(--brand);background:transparent;border:none;cursor:pointer">+ Add Filter</button>
                </div>

                <!-- Step 6: Group By -->
                <div class="card" style="padding:16px" x-show="aggregates.length > 0">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <span style="width:20px;height:20px;border-radius:50%;display:flex;align-items:center;justify-content:center;font-size:11px;font-weight:700;background:rgba(31,138,76,0.15);color:var(--green)">6</span>
                        Group By
                    </h3>
                    <div class="flex flex-wrap gap-2">
                        <template x-for="col in schema" :key="col.name">
                            <label class="flex items-center gap-1 text-xs"
                                   style="padding:4px 8px;border-radius:6px;cursor:pointer;background:var(--surface-2);border:1px solid var(--border)"
                                   :style="groupBy.includes(col.name) ? 'border-color:var(--green);background:rgba(31,138,76,0.08)' : ''">
                                <input type="checkbox" :value="col.name" x-model="groupBy"
                                       class="rounded w-3 h-3" style="accent-color:var(--green)">
                                <span x-text="col.name"></span>
                            </label>
                        </template>
                    </div>
                </div>
            </div>

            <!-- Right: SQL Preview -->
            <div style="display:flex;flex-direction:column;gap:16px">
                <div class="card" style="padding:16px;position:sticky;top:24px">
                    <h3 class="text-sm font-semibold flex items-center gap-2" style="margin-bottom:12px">
                        <i data-lucide="code" class="w-4 h-4"></i>
                        Generated SQL
                    </h3>
                    <pre x-show="generatedSQL" class="mono"
                         style="padding:12px;border-radius:6px;font-size:11.5px;overflow-x:auto;white-space:pre-wrap;max-height:16rem;background:var(--bg);color:var(--green);border:1px solid var(--border)" x-text="generatedSQL"></pre>
                    <p x-show="!generatedSQL" class="text-sm" style="color:var(--fg-3)">Select a source and table, then click "Generate SQL"</p>

                    <!-- Limit -->
                    <div style="margin-top:12px">
                        <label class="text-xs" style="color:var(--fg-3)">Row Limit</label>
                        <input type="number" x-model.number="limit" style="width:100%;margin-top:4px">
                    </div>
                </div>
            </div>
        </div>
    </main>
</div>
{% endblock %}

{% block scripts %}
<script src="/static/plugins/bi/bi.js"></script>
<script src="/static/plugins/bi/query-builder.js"></script>
{% endblock %}