<?php

declare(strict_types=1);

$moduleGuide = [
    'id' => 'queryGuideModal',
    'eyebrow' => 'Visual SQL handbook',
    'title' => 'Query guide',
    'subtitle' => 'Design queries visually, edit clean SQL with autocomplete, test safely, turn a field into live list-page tabs, and save reusable definitions.',
    'icon' => 'database-gear',
    'default' => 'start',
    'topics' => [
        [
            'id' => 'start',
            'label' => 'Start here',
            'icon' => 'compass',
            'keywords' => 'beginner overview designer sql results additional where saved query workflow tabs pipeline diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">All levels</span><h3>From point-and-click to tested SQL</h3><p>The Query module has four coordinated tabs. Pick a table on the left (or drag several onto the canvas), tick the fields you want, and the SQL writes itself — you can then refine it by hand with autocomplete, preview the rows, and add filter tabs for the generated list page.</p></div>
                <figure class="pp-guide-diagram">
                    <div class="pp-guide-flow">
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-grid-3x3"></i></span><span class="pp-fn-title">1 · Designer</span><span class="pp-fn-sub">Tick fields, set sort, grouping &amp; criteria.</span></div>
                        <div class="pp-guide-flow-arrow"><i class="bi bi-arrow-right"></i></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-code-slash"></i></span><span class="pp-fn-title">2 · SQL</span><span class="pp-fn-sub">Auto-written; edit with autocomplete.</span></div>
                        <div class="pp-guide-flow-arrow"><i class="bi bi-arrow-right"></i></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-table"></i></span><span class="pp-fn-title">3 · Results</span><span class="pp-fn-sub">Preview rows (capped), page &amp; export.</span></div>
                        <div class="pp-guide-flow-arrow"><i class="bi bi-arrow-right"></i></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-funnel"></i></span><span class="pp-fn-title">4 · Filter tabs</span><span class="pp-fn-sub">Turn the query into list-page tabs.</span></div>
                    </div>
                    <figcaption>The four tabs stay in sync: what you build in the Designer becomes SQL, the SQL drives the Results preview, and filter tabs ship to your generated app.</figcaption>
                </figure>
                <div class="pp-guide-callout"><i class="bi bi-stars"></i><div><strong>New &amp; notable:</strong> clean single-table SQL, column-aware autocomplete, aggregate functions with <code>HAVING</code>, foreign-key auto-join, CSV/JSON/Excel export, EXPLAIN plans, save-as-view, and dynamic list-page tabs generated from a field's distinct values — all portable across every supported database engine.</div></div>
                <div class="pp-guide-callout warning"><i class="bi bi-database-lock"></i><div>The Results preview runs <strong>read-only</strong> queries only (SELECT / WITH / SHOW / EXPLAIN). Attempts to <code>UPDATE</code>/<code>DELETE</code>/<code>DROP</code> — or to sneak a second statement in after a semicolon — are refused, so nothing here can change your data.</div></div>
            HTML,
        ],
        [
            'id' => 'designer',
            'label' => 'Designer grid',
            'icon' => 'grid-3x3',
            'keywords' => 'field alias sort type order group by criteria where add row remove select short name qualified distinct limit expression diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Beginner → intermediate</span><h3>Each row is one output column</h3><p>Tick a field in a table card (left) and it becomes a row here. Every row contributes a column to the <code>SELECT</code> plus optional sort, grouping, and filter behaviour. Here's how each grid column maps to a piece of SQL:</p></div>
                <figure class="pp-guide-diagram">
                    <div class="pp-guide-map">
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-check2-square"></i> Field ticked</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">std_id, name</span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-clause">SELECT</span><span></span><span></span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-tag"></i> Alias</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">COUNT(id) AS total</span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-funnel"></i> Criteria (plain field)</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">WHERE age &gt; 18</span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-collection"></i> Group By (aggregate)</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">GROUP BY gender</span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-funnel-fill"></i> Criteria (on aggregate)</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">HAVING COUNT(id) &gt; 5</span></div>
                        <div class="pp-guide-map-row"><span class="pp-guide-map-src"><i class="bi bi-sort-down"></i> Sort Type / Order</span><span class="pp-guide-map-arrow"><i class="bi bi-arrow-right"></i></span><span class="pp-guide-map-dst">ORDER BY total DESC</span></div>
                    </div>
                    <figcaption>One grid row → one clause. The Designer decides <code>WHERE</code> vs <code>HAVING</code> for you based on whether the field is aggregated.</figcaption>
                </figure>
                <div class="pp-guide-table-wrap"><table class="pp-guide-table"><thead><tr><th>Column</th><th>Effect</th><th>Tip</th></tr></thead><tbody>
                    <tr><td>Field</td><td>The column returned. Shown by its <strong>short name</strong> (e.g. <code>std_id</code>) for readability; the full <code>table.field</code> is kept internally and shown on hover.</td><td>With joins the name is auto-qualified to avoid ambiguity.</td></tr>
                    <tr><td>Alias</td><td>A friendlier output name (<code>… AS order_count</code>).</td><td>Use short, unique names — downstream pages use them as headers.</td></tr>
                    <tr><td>Sort Type / Order</td><td>Builds <code>ORDER BY</code> — direction and precedence.</td><td>End on a unique key (e.g. the PK) for stable paging.</td></tr>
                    <tr><td>Group By</td><td>Choose <em>Group By</em> or an aggregate — <em>Count, Count Distinct, Sum, Average, Min, Max</em>.</td><td>See the <strong>Aggregates</strong> topic for the full pattern.</td></tr>
                    <tr><td>Criteria (WHERE)</td><td>Filters rows. Type just the operator + value (<code>&gt; 100</code>, <code>LIKE '%rai%'</code>, <code>IN (1,2)</code>, <code>IS NULL</code>) — it binds to this field automatically.</td><td>Type a full expression to use it verbatim.</td></tr>
                </tbody></table></div>
                <div class="pp-guide-callout"><i class="bi bi-magic"></i><div><strong>Single-table SQL is clean.</strong> When there are no joins the redundant <code>table.</code> prefix is dropped, so you get <code>SELECT std_id, name FROM db_aus</code> — not <code>db_aus.std_id, …</code>. Editing it and clicking <em>Sync to Designer</em> round-trips correctly.</div></div>
                <ul class="pp-guide-list">
                    <li><strong>Add field / expression</strong> lets you type a custom SQL expression with its own alias, e.g. <code>CONCAT(first,' ',last) AS full_name</code> or <code>YEAR(created_at) AS yr</code>.</li>
                    <li><strong>DISTINCT</strong> (toolbar switch) returns only unique rows — <code>SELECT DISTINCT …</code>.</li>
                    <li><strong>Limit</strong> caps how many rows the query itself returns (part of the saved query; 0 = no limit).</li>
                </ul>
            HTML,
        ],
        [
            'id' => 'aggregates',
            'label' => 'Aggregates & grouping',
            'icon' => 'calculator',
            'keywords' => 'group by aggregate count sum avg average min max having distinct totals summary fold rows diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Intermediate</span><h3>Summaries, totals, and counts</h3><p>An aggregate <em>folds</em> many rows into one row per group. Mark the field you group on as <em>Group By</em>, and the measure as <em>Count / Sum / Average / …</em></p></div>
                <figure class="pp-guide-diagram">
                    <svg viewBox="0 0 640 210" role="img" aria-label="Five rows folding into two groups by gender"><defs><marker id="agg-ah" viewBox="0 0 10 10" refX="8" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0 0 L10 5 L0 10 z" fill="currentColor"/></marker></defs><text x="105" y="22" text-anchor="middle" style="fill:var(--guide-muted)" font-size="12" font-weight="700">5 rows</text><g font-size="12"><rect x="30" y="34" width="150" height="24" rx="6" style="fill:var(--guide-bg)" stroke="currentColor"/><text x="42" y="50" style="fill:var(--guide-text)">Male</text><rect x="30" y="66" width="150" height="24" rx="6" style="fill:var(--guide-bg)" stroke="currentColor"/><text x="42" y="82" style="fill:var(--guide-text)">Male</text><rect x="30" y="98" width="150" height="24" rx="6" style="fill:var(--guide-bg)" stroke="currentColor"/><text x="42" y="114" style="fill:var(--guide-text)">Female</text><rect x="30" y="130" width="150" height="24" rx="6" style="fill:var(--guide-bg)" stroke="currentColor"/><text x="42" y="146" style="fill:var(--guide-text)">Female</text><rect x="30" y="162" width="150" height="24" rx="6" style="fill:var(--guide-bg)" stroke="currentColor"/><text x="42" y="178" style="fill:var(--guide-text)">Female</text></g><g><line x1="200" y1="110" x2="425" y2="110" stroke="currentColor" stroke-width="2" marker-end="url(#agg-ah)"/><text x="312" y="98" text-anchor="middle" style="fill:var(--guide-accent)" font-size="12" font-weight="700">GROUP BY gender</text><text x="312" y="128" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">COUNT(std_id)</text></g><text x="535" y="22" text-anchor="middle" style="fill:var(--guide-muted)" font-size="12" font-weight="700">2 groups</text><g font-size="13"><rect x="440" y="48" width="180" height="44" rx="8" style="fill:var(--guide-accent-soft)" stroke="currentColor"/><text x="456" y="75" style="fill:var(--guide-text)" font-weight="600">Male — 2</text><rect x="440" y="118" width="180" height="44" rx="8" style="fill:var(--guide-accent-soft)" stroke="currentColor"/><text x="456" y="145" style="fill:var(--guide-text)" font-weight="600">Female — 3</text></g></svg>
                    <figcaption>Each distinct <code>gender</code> becomes one output row; <code>COUNT</code> reports how many source rows folded into it.</figcaption>
                </figure>
                <p><strong>Grid setup</strong> — how many students of each gender, only groups above 5:</p>
                <div class="pp-guide-table-wrap"><table class="pp-guide-table"><thead><tr><th>Field</th><th>Alias</th><th>Group By</th><th>Criteria</th></tr></thead><tbody>
                    <tr><td>gender</td><td></td><td>Group By</td><td></td></tr>
                    <tr><td>std_id</td><td>total</td><td>Count</td><td>&gt; 5</td></tr>
                </tbody></table></div>
                <pre class="pp-guide-code"><code>SELECT gender, COUNT(std_id) AS total
                FROM db_aus
                GROUP BY gender
                HAVING COUNT(std_id) &gt; 5
                ORDER BY total DESC;</code></pre>
                <ul class="pp-guide-list"><li>Criteria on an <strong>aggregate</strong> field goes to <code>HAVING</code> (filters groups); criteria on a plain field goes to <code>WHERE</code> (filters rows) — automatically.</li><li><em>Count Distinct</em> emits <code>COUNT(DISTINCT field)</code>.</li><li>Every non-aggregated output column normally needs its own <em>Group By</em> row.</li></ul>
            HTML,
        ],
        [
            'id' => 'joins',
            'label' => 'Tables & joins',
            'icon' => 'share',
            'keywords' => 'diagram joins inner left relation foreign key auto join multiple tables cartesian duplicate rows drag venn',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Intermediate</span><h3>Connect tables deliberately</h3><p>Drag a table from the left onto the canvas to add it. To join, drag a field from one card onto a field in another — the join dialog opens with the two sides pre-filled; pick the join type. The join type decides which rows survive:</p></div>
                <figure class="pp-guide-diagram">
                    <svg viewBox="0 0 640 200" role="img" aria-label="INNER join keeps only the overlap; LEFT join keeps all of the left table"><defs><clipPath id="jn-inner"><circle cx="150" cy="105" r="58"/></clipPath><clipPath id="jn-left"><circle cx="470" cy="105" r="58"/></clipPath></defs><text x="182" y="24" text-anchor="middle" style="fill:var(--guide-text)" font-size="14" font-weight="700">INNER JOIN</text><circle cx="150" cy="105" r="58" style="fill:var(--guide-bg)" stroke="currentColor" stroke-width="1.5"/><circle cx="214" cy="105" r="58" style="fill:var(--guide-bg)" stroke="currentColor" stroke-width="1.5"/><g clip-path="url(#jn-inner)"><circle cx="214" cy="105" r="58" fill="currentColor" opacity="0.55"/></g><circle cx="150" cy="105" r="58" fill="none" stroke="currentColor" stroke-width="1.5"/><circle cx="214" cy="105" r="58" fill="none" stroke="currentColor" stroke-width="1.5"/><text x="120" y="110" style="fill:var(--guide-muted)" font-size="13" font-weight="700">A</text><text x="238" y="110" style="fill:var(--guide-muted)" font-size="13" font-weight="700">B</text><text x="182" y="188" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">only rows matching in both</text><text x="462" y="24" text-anchor="middle" style="fill:var(--guide-text)" font-size="14" font-weight="700">LEFT JOIN</text><circle cx="470" cy="105" r="58" fill="currentColor" opacity="0.55"/><g clip-path="url(#jn-left)"><circle cx="534" cy="105" r="58" fill="currentColor" opacity="0.8"/></g><circle cx="470" cy="105" r="58" fill="none" stroke="currentColor" stroke-width="1.5"/><circle cx="534" cy="105" r="58" fill="none" stroke="currentColor" stroke-width="1.5"/><text x="440" y="110" style="fill:var(--guide-on-accent,#fff)" font-size="13" font-weight="700">A</text><text x="558" y="110" style="fill:var(--guide-muted)" font-size="13" font-weight="700">B</text><text x="462" y="188" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">every A row; unmatched B → NULL</text></svg>
                    <figcaption>INNER keeps the overlap; LEFT keeps all of table A and fills missing B columns with <code>NULL</code>.</figcaption>
                </figure>
                <div class="pp-guide-callout"><i class="bi bi-magic"></i><div><strong>Foreign-key auto-join.</strong> When you add a table that has a foreign key to (or from) a table already on the canvas, the join is created for you automatically (you'll see an "Auto-joined on foreign key" toast). Drag fields manually only when there's no FK.</div></div>
                <ul class="pp-guide-list"><li><strong>INNER JOIN</strong> keeps rows that match on both sides.</li><li><strong>LEFT JOIN</strong> keeps every left row, filling unmatched right columns with <code>NULL</code>.</li><li>Double-click a join arrow to <strong>edit</strong> it (change the join type) or delete it.</li><li>One-to-many joins repeat the "one" side — aggregate (see Aggregates) or expect duplicates.</li></ul>
                <div class="pp-guide-callout warning"><i class="bi bi-exclamation-triangle"></i><div>A missing/incorrect join condition creates a Cartesian product that multiplies rows. Check the Results row count before saving.</div></div>
            HTML,
        ],
        [
            'id' => 'sql',
            'label' => 'SQL editor & autocomplete',
            'icon' => 'code-slash',
            'keywords' => 'sql editor codemirror autocomplete syntax highlight sync designer clause order having read only execution order diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Intermediate → advanced</span><h3>A real editor over the generated SQL</h3><p>The SQL tab is a full code editor: syntax highlighting, and <strong>column-aware autocomplete</strong> drawn from the tables in your query.</p></div>
                <ul class="pp-guide-list">
                    <li>Type <code>table.</code> or an alias (from <code>FROM orders o</code> → <code>o.</code>) and it lists <strong>that table's columns</strong>.</li>
                    <li>Start typing anywhere for tables, columns, and SQL keywords; press <code>Ctrl/⌘ + Space</code> to force suggestions.</li>
                    <li><strong>Sync to Designer</strong> parses your SQL back into the visual grid (bare column names are re-qualified so it round-trips).</li>
                </ul>
                <p>You write the clauses in one order, but the database <em>runs</em> them in another. Knowing the execution order explains why <code>WHERE</code> can't see an alias and why <code>HAVING</code> filters groups:</p>
                <figure class="pp-guide-diagram">
                    <svg viewBox="0 0 640 330" role="img" aria-label="Logical execution order of a SELECT statement"><defs><marker id="sql-ah" viewBox="0 0 10 10" refX="6" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0 0 L10 5 L0 10 z" fill="currentColor"/></marker></defs><g font-size="13"><rect x="70" y="12" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="32" r="13" fill="currentColor"/><text x="96" y="37" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">1</text><text x="122" y="30" style="fill:var(--guide-accent)" font-weight="700">FROM · JOIN</text><text x="122" y="45" style="fill:var(--guide-muted)" font-size="11">assemble &amp; match the tables</text><rect x="70" y="60" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="80" r="13" fill="currentColor"/><text x="96" y="85" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">2</text><text x="122" y="78" style="fill:var(--guide-accent)" font-weight="700">WHERE</text><text x="122" y="93" style="fill:var(--guide-muted)" font-size="11">keep rows that pass</text><rect x="70" y="108" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="128" r="13" fill="currentColor"/><text x="96" y="133" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">3</text><text x="122" y="126" style="fill:var(--guide-accent)" font-weight="700">GROUP BY</text><text x="122" y="141" style="fill:var(--guide-muted)" font-size="11">fold rows into groups</text><rect x="70" y="156" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="176" r="13" fill="currentColor"/><text x="96" y="181" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">4</text><text x="122" y="174" style="fill:var(--guide-accent)" font-weight="700">HAVING</text><text x="122" y="189" style="fill:var(--guide-muted)" font-size="11">keep groups that pass</text><rect x="70" y="204" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="224" r="13" fill="currentColor"/><text x="96" y="229" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">5</text><text x="122" y="222" style="fill:var(--guide-accent)" font-weight="700">SELECT</text><text x="122" y="237" style="fill:var(--guide-muted)" font-size="11">compute output columns &amp; aliases</text><rect x="70" y="252" width="500" height="40" rx="9" style="fill:var(--guide-bg)" stroke="currentColor"/><circle cx="96" cy="272" r="13" fill="currentColor"/><text x="96" y="277" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">6</text><text x="122" y="270" style="fill:var(--guide-accent)" font-weight="700">ORDER BY</text><text x="122" y="285" style="fill:var(--guide-muted)" font-size="11">sort for presentation</text></g><g stroke="currentColor" stroke-width="1.5" opacity="0.7"><line x1="320" y1="52" x2="320" y2="60" marker-end="url(#sql-ah)"/><line x1="320" y1="100" x2="320" y2="108" marker-end="url(#sql-ah)"/><line x1="320" y1="148" x2="320" y2="156" marker-end="url(#sql-ah)"/><line x1="320" y1="196" x2="320" y2="204" marker-end="url(#sql-ah)"/><line x1="320" y1="244" x2="320" y2="252" marker-end="url(#sql-ah)"/></g><text x="320" y="312" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">Because SELECT runs at step 5, aliases aren't visible to WHERE (step 2).</text></svg>
                    <figcaption>Logical execution order — not the order you type. It's why an aggregate is filtered by <code>HAVING</code>, not <code>WHERE</code>.</figcaption>
                </figure>
                <pre class="pp-guide-code"><code>SELECT c.id, c.name, COUNT(o.id) AS order_count
                FROM customers AS c
                LEFT JOIN orders AS o ON o.customer_id = c.id
                WHERE c.status = 'active'      -- filters rows
                GROUP BY c.id, c.name          -- forms groups
                HAVING COUNT(o.id) &gt; 0         -- filters groups
                ORDER BY order_count DESC;     -- presentation order</code></pre>
                <div class="pp-guide-callout warning"><i class="bi bi-shield-check"></i><div>The preview is <strong>read-only</strong>: only a single <code>SELECT</code>/<code>WITH</code>/<code>SHOW</code>/<code>EXPLAIN</code> runs. <code>INSERT/UPDATE/DELETE/DROP</code>, <code>INTO OUTFILE</code>, and <strong>stacked statements</strong> (a second query after <code>;</code>) are refused — even though the editor auto-runs as you design.</div></div>
            HTML,
        ],
        [
            'id' => 'results',
            'label' => 'Results & export',
            'icon' => 'table',
            'keywords' => 'results run test validate null duplicate types performance sample data export csv json excel cap limit paging explain plan',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">All levels</span><h3>Validate meaning, not just syntax</h3><p>A query can run and still return the wrong records. The Results tab shows the row count, timing, and the rows, with a toolbar for paging, exporting, and inspecting the plan.</p></div>
                <figure class="pp-guide-diagram">
                    <div class="pp-guide-flow">
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-chevron-bar-contract"></i></span><span class="pp-fn-title">Page</span><span class="pp-fn-sub">25–250 / page or All; ◀ ▶ to step. Hard cap 1,000 rows.</span></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-diagram-2"></i></span><span class="pp-fn-title">Explain</span><span class="pp-fn-sub">See the DB query plan — spot scans &amp; missing indexes.</span></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-download"></i></span><span class="pp-fn-title">Export</span><span class="pp-fn-sub">CSV · JSON · Excel (formula-injection-safe).</span></div>
                    </div>
                    <figcaption>The toolbar above every result grid. Paging keeps huge tables from hanging the editor.</figcaption>
                </figure>
                <ul class="pp-guide-list">
                    <li><strong>Paging</strong> — choose 25–250 rows per page (or <em>All</em>) and step with ◀ ▶. The preview is hard-capped at 1,000 rows so a huge table can't hang the editor.</li>
                    <li><strong>Export</strong> — <em>CSV</em>, <em>JSON</em>, or <em>Excel</em> (formula-injection-safe CSV).</li>
                    <li><strong>Explain</strong> — shows the database's query plan so you can spot table scans / missing indexes; click <em>Back to results</em> to return.</li>
                    <li>Results refresh when you open the tab; every cell is HTML-escaped, so data that looks like markup won't render.</li>
                </ul>
                <div class="pp-guide-checklist">
                    <label><input type="checkbox"> Columns/aliases match the intended output.</label>
                    <label><input type="checkbox"> Row count is plausible — joins didn't duplicate rows unexpectedly.</label>
                    <label><input type="checkbox"> NULL / empty values behave as intended.</label>
                    <label><input type="checkbox"> Filters cover boundary, missing, and special-character cases.</label>
                    <label><input type="checkbox"> Sorting is deterministic when the list is paged.</label>
                    <label><input type="checkbox"> No unauthorized column is exposed.</label>
                </div>
            HTML,
        ],
        [
            'id' => 'where',
            'label' => 'Filter tabs (Additional WHERE)',
            'icon' => 'funnel',
            'keywords' => 'additional where filter tabs dynamic field distinct all tab show count hide empty drag reorder list page session variable mockup',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Intermediate</span><h3>Turn the query into tabs on the generated list</h3><p>Each tab you add here becomes a one-click filter on the generated <strong>list page</strong>. Pick a field like <code>gender</code> and the app builds one tab per distinct value — live from the data:</p></div>
                <figure class="pp-guide-diagram">
                    <svg viewBox="0 0 640 210" role="img" aria-label="Generated list page with auto-generated filter tabs"><g font-size="12.5"><rect x="24" y="18" width="104" height="34" rx="8" fill="currentColor"/><text x="76" y="40" text-anchor="middle" style="fill:var(--guide-on-accent,#fff)" font-weight="700">All · 1591</text><rect x="138" y="18" width="118" height="34" rx="8" style="fill:var(--guide-accent-soft)" stroke="currentColor"/><text x="197" y="40" text-anchor="middle" style="fill:var(--guide-accent)" font-weight="700">Female · 653</text><rect x="266" y="18" width="104" height="34" rx="8" style="fill:var(--guide-accent-soft)" stroke="currentColor"/><text x="318" y="40" text-anchor="middle" style="fill:var(--guide-accent)" font-weight="700">Male · 929</text><rect x="380" y="18" width="96" height="34" rx="8" style="fill:var(--guide-accent-soft)" stroke="currentColor"/><text x="428" y="40" text-anchor="middle" style="fill:var(--guide-accent)" font-weight="700">Other · 6</text><rect x="486" y="18" width="40" height="34" rx="8" style="fill:var(--guide-bg)" stroke="currentColor" stroke-dasharray="3 3"/><text x="506" y="41" text-anchor="middle" style="fill:var(--guide-muted)" font-size="16">+</text></g><rect x="24" y="70" width="592" height="34" rx="6" style="fill:var(--guide-bg-soft)" stroke="currentColor"/><g style="fill:var(--guide-muted)" font-size="12" font-weight="700"><text x="40" y="92">Name</text><text x="300" y="92">Gender</text><text x="460" y="92">Level</text></g><g style="fill:var(--guide-text)" font-size="12"><rect x="24" y="108" width="592" height="30" rx="6" style="fill:var(--guide-bg)" stroke="currentColor" stroke-opacity="0.4"/><text x="40" y="127">Aroha R.</text><text x="300" y="127">Female</text><text x="460" y="127">Masters</text><rect x="24" y="142" width="592" height="30" rx="6" style="fill:var(--guide-bg)" stroke="currentColor" stroke-opacity="0.4"/><text x="40" y="161">Meera K.</text><text x="300" y="161">Female</text><text x="460" y="161">Bachelors</text><rect x="24" y="176" width="592" height="30" rx="6" style="fill:var(--guide-bg)" stroke="currentColor" stroke-opacity="0.4"/><text x="40" y="195">Sita T.</text><text x="300" y="195">Female</text><text x="460" y="195">Diploma</text></g></svg>
                    <figcaption>Auto tabs from <code>gender</code>: one tab per distinct value with live counts. The active tab (Female) filters the rows below; new values appear on their own.</figcaption>
                </figure>
                <ul class="pp-guide-list">
                    <li><strong>Single tab — one fixed WHERE</strong>: give it a name and a clause, e.g. <code>status = 'active'</code>.</li>
                    <li><strong>Auto tabs — one per distinct value of a field</strong>: pick a field and the list page builds one tab per distinct value, live from the data. <em>Preview</em> shows exactly which tabs will be generated.</li>
                </ul>
                <div class="pp-guide-grid">
                    <article class="pp-guide-card"><span class="pp-guide-step"><i class="bi bi-list-ol"></i></span><h4>Show an “All” tab</h4><p>Toggle whether the list also shows an <strong>All</strong> tab (every record). Off = the list defaults to your first tab.</p></article>
                    <article class="pp-guide-card"><span class="pp-guide-step"><i class="bi bi-123"></i></span><h4>Show record count</h4><p>Adds a live <code>(n)</code> badge to each tab.</p></article>
                    <article class="pp-guide-card"><span class="pp-guide-step"><i class="bi bi-eye-slash"></i></span><h4>Hide when empty</h4><p>A tab with zero rows is hidden (unless it's the active view).</p></article>
                    <article class="pp-guide-card"><span class="pp-guide-step"><i class="bi bi-arrows-move"></i></span><h4>Drag to reorder</h4><p>On the list page, drag tabs to reorder them — the order is remembered per table in that browser.</p></article>
                </div>
                <div class="pp-guide-callout"><i class="bi bi-person-badge"></i><div><strong>Session variables</strong> work in a fixed clause: <code>:userid</code>, <code>:session.key</code>, <code>:user.field</code>. For example a <em>My records</em> tab is just <code>assigned_to = :userid</code> — each user sees their own rows, and the count badge reflects it.</div></div>
                <div class="pp-guide-callout warning"><i class="bi bi-shield-exclamation"></i><div>Auto tabs are generated with the same row-level security as the list, so they never reveal values a user can't see. Fixed-clause tabs are raw developer SQL — keep them simple and safe.</div></div>
            HTML,
        ],
        [
            'id' => 'saved',
            'label' => 'Saved queries',
            'icon' => 'bookmark',
            'keywords' => 'save as saved queries expand collapse load delete naming reuse per table autosave view database view diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Beginner → advanced</span><h3>Preserve verified definitions</h3><p>Your work per table is auto-saved as you go. Beyond that, you have two ways to keep a finished query — and they do different things:</p></div>
                <figure class="pp-guide-diagram">
                    <div class="pp-guide-flow">
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-hdd"></i></span><span class="pp-fn-title">Auto-save</span><span class="pp-fn-sub">Every change to this table's query is kept automatically.</span></div>
                        <div class="pp-guide-flow-arrow"><i class="bi bi-arrow-right"></i></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-bookmark-star"></i></span><span class="pp-fn-title">Save As…</span><span class="pp-fn-sub">A named, reusable definition in the sidebar. Load or delete anytime.</span></div>
                        <div class="pp-guide-flow-arrow"><i class="bi bi-arrow-right"></i></div>
                        <div class="pp-guide-flow-node"><span class="pp-fn-ico"><i class="bi bi-eye"></i></span><span class="pp-fn-title">Save as View</span><span class="pp-fn-sub">A real DB view — reuse it as a table in the Tables module.</span></div>
                    </div>
                    <figcaption><strong>Save As</strong> stores the <em>definition</em> to reopen here; <strong>Save as View</strong> creates a live database object other pages can build on.</figcaption>
                </figure>
                <div class="pp-guide-callout"><i class="bi bi-eye"></i><div><strong>Save as View</strong> turns the current SQL into a real database <em>view</em> (created or replaced across any supported engine). It then behaves like a read-only table — pick it up in the <strong>Tables</strong> module to build list/report pages on top of it.</div></div>
                <h4>Naming pattern</h4><pre class="pp-guide-code"><code>domain_purpose_variant
                customers_active_summary
                orders_monthly_totals</code></pre>
                <ul class="pp-guide-list"><li>Use descriptive names — avoid "test" or "query1".</li><li>After a schema change, reload the saved query and re-run Results.</li><li>Delete obsolete definitions so nobody builds on stale logic.</li></ul>
            HTML,
        ],
        [
            'id' => 'developers',
            'label' => 'For developers',
            'icon' => 'braces',
            'keywords' => 'developer architecture endpoints controller build whereTabs runtime rls dialect security portable pagination stacked statement diagram',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Developer</span><h3>How it fits together</h3><p>The module state lives in <code>config/query.json</code> under <code>byTable.&lt;table&gt;</code> (fields, joins, sql, whereTabs, whereTabsShowAll, positions). Named queries live in <code>config/queries/*.json</code>.</p></div>
                <figure class="pp-guide-diagram">
                    <svg viewBox="0 0 640 250" role="img" aria-label="Query module architecture: browser, controller endpoints, project database, and the build path"><defs><marker id="dev-ah" viewBox="0 0 10 10" refX="8" refY="5" markerWidth="7" markerHeight="7" orient="auto"><path d="M0 0 L10 5 L0 10 z" fill="currentColor"/></marker></defs><rect x="20" y="26" width="150" height="70" rx="10" style="fill:var(--guide-bg)" stroke="currentColor" stroke-width="1.5"/><text x="95" y="52" text-anchor="middle" style="fill:var(--guide-text)" font-size="13" font-weight="700">Designer</text><text x="95" y="70" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">browser SPA</text><text x="95" y="85" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">4 tabs</text><rect x="245" y="14" width="170" height="94" rx="10" style="fill:var(--guide-accent-soft)" stroke="currentColor" stroke-width="1.5"/><text x="330" y="34" text-anchor="middle" style="fill:var(--guide-accent)" font-size="12.5" font-weight="700">QueryController</text><g style="fill:var(--guide-text)" font-size="10.5" font-family="var(--bs-font-monospace, monospace)"><text x="258" y="52">generate-sql</text><text x="258" y="66">execute</text><text x="258" y="80">save-view</text><text x="258" y="94">parse-sql · field-values</text></g><rect x="480" y="26" width="150" height="70" rx="10" style="fill:var(--guide-bg)" stroke="currentColor" stroke-width="1.5"/><text x="555" y="52" text-anchor="middle" style="fill:var(--guide-text)" font-size="13" font-weight="700">Project DB</text><text x="555" y="70" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">via dialect layer</text><text x="555" y="85" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">8 engines</text><g stroke="currentColor" stroke-width="1.5"><line x1="170" y1="61" x2="243" y2="61" marker-end="url(#dev-ah)"/><line x1="415" y1="61" x2="478" y2="61" marker-end="url(#dev-ah)"/></g><text x="206" y="52" text-anchor="middle" style="fill:var(--guide-muted)" font-size="10">JSON</text><text x="447" y="52" text-anchor="middle" style="fill:var(--guide-muted)" font-size="10">PDO</text><rect x="20" y="150" width="230" height="66" rx="10" style="fill:var(--guide-bg)" stroke="currentColor" stroke-width="1.5"/><text x="135" y="176" text-anchor="middle" style="fill:var(--guide-text)" font-size="12.5" font-weight="700">config/query.json</text><text x="135" y="196" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">byTable · queries/*.json</text><rect x="360" y="150" width="270" height="66" rx="10" style="fill:var(--guide-accent-soft)" stroke="currentColor" stroke-width="1.5"/><text x="495" y="176" text-anchor="middle" style="fill:var(--guide-accent)" font-size="12.5" font-weight="700">Generated list page</text><text x="495" y="196" text-anchor="middle" style="fill:var(--guide-muted)" font-size="11">filter tabs · ?wtab=N · RLS</text><g stroke="currentColor" stroke-width="1.5"><line x1="250" y1="183" x2="358" y2="183" marker-end="url(#dev-ah)"/></g><text x="304" y="174" text-anchor="middle" style="fill:var(--guide-muted)" font-size="10">Build</text><line x1="135" y1="108" x2="135" y2="148" stroke="currentColor" stroke-width="1.5" stroke-dasharray="4 3" marker-end="url(#dev-ah)"/></svg>
                    <figcaption>The browser talks JSON to the controller, which runs SQL through the shared 8-engine dialect layer. State persists to JSON; the Build step bakes filter tabs into the generated app.</figcaption>
                </figure>
                <div class="pp-guide-table-wrap"><table class="pp-guide-table"><thead><tr><th>Endpoint</th><th>Role</th></tr></thead><tbody>
                    <tr><td><code>POST query/generate-sql</code></td><td>Designer state → SQL. Identifiers are quoted with the project <strong>dialect</strong> (not hard-coded backticks); <code>LIMIT</code> is emitted only on LIMIT-family engines.</td></tr>
                    <tr><td><code>POST query/execute</code></td><td>Runs the preview — read-only guard (rejects non-SELECT <em>and stacked statements</em>), 1,000-row cap, optional <code>page</code>/<code>perPage</code> paging (appended <code>LIMIT … OFFSET …</code>), and EXPLAIN.</td></tr>
                    <tr><td><code>POST query/save-view</code></td><td>Creates/replaces a database VIEW from the SQL via the project dialect.</td></tr>
                    <tr><td><code>POST query/parse-sql</code></td><td>SQL → structured query (re-qualifies bare columns) for <em>Sync to Designer</em>.</td></tr>
                    <tr><td><code>POST query/field-values</code></td><td>Distinct values of a field (preview for dynamic tabs); dialect-quoted.</td></tr>
                    <tr><td><code>POST query/save-state</code> / <code>save</code></td><td>Auto-save per table / named Save As (files under <code>config/queries</code>).</td></tr>
                </tbody></table></div>
                <h4>Filter tabs on the generated app</h4>
                <ul class="pp-guide-list">
                    <li>The tabs you define are baked into the generated list page as a runtime that applies the active tab's <code>WHERE</code> clause, switched by <code>?wtab=N</code>.</li>
                    <li><strong>Dynamic</strong> tabs bake a marker and expand at request time: <code>SELECT DISTINCT field … &lt;RowSecurity&gt; ORDER BY field LIMIT 40</code>, one tab per value (values are PDO-quoted; the field identifier is re-validated).</li>
                    <li>Counts use <code>RowSecurity::mergeWhere</code>, so tabs and badges respect row-level security. The clause of a fixed tab is trusted developer SQL — same trust model as an Events <code>sqlWhere</code>.</li>
                    <li>Tab reordering is display-only (localStorage) — each tab keeps its own <code>wtab</code> index, so the clause mapping is never affected.</li>
                </ul>
                <div class="pp-guide-callout"><i class="bi bi-book"></i><div>SQL reference: <a href="https://dev.mysql.com/doc/refman/8.4/en/select.html" target="_blank" rel="noopener">MySQL SELECT</a> · <a href="https://www.postgresql.org/docs/current/sql-select.html" target="_blank" rel="noopener">PostgreSQL SELECT</a>.</div></div>
            HTML,
        ],
        [
            'id' => 'checklist',
            'label' => 'Production checklist',
            'icon' => 'check2-square',
            'keywords' => 'production checklist security performance portability test release',
            'body' => <<<'HTML'
                <div class="pp-guide-hero"><span class="pp-guide-level">Release readiness</span><h3>Before a query becomes application logic</h3></div>
                <div class="pp-guide-checklist">
                    <label><input type="checkbox"> SQL is valid for the project's database dialect.</label>
                    <label><input type="checkbox"> Joins, NULL behaviour, grouping, and totals are verified in Results.</label>
                    <label><input type="checkbox"> Fixed WHERE-tab clauses are simple, safe, and dialect-portable.</label>
                    <label><input type="checkbox"> Tenant / role / ownership restrictions are enforced server-side (RLS), not just via tabs.</label>
                    <label><input type="checkbox"> Ordering is deterministic; returned volume is bounded.</label>
                    <label><input type="checkbox"> Indexes / execution plan reviewed for large data.</label>
                    <label><input type="checkbox"> Saved query retested after schema changes.</label>
                </div>
            HTML,
        ],
    ],
];

include constant('BASE_PATH') . '/core/views/module-guide.php';
