{# Storage Billing recommendations - dataset-level LOGICAL→PHYSICAL
   switches surfaced from the ``storage_billing_optimization`` rule.
   Each card shows the savings, current vs. proposed cost, a per-table
   breakdown, and a copyable ALTER SCHEMA snippet. #}
{% extends "layout.html" %}

{% block content %}
<section class="ui-shell">
  <div class="ui-page-header">
    <div class="flex items-center gap-2">
      <h1 class="ui-page-title">Storage Billing</h1>
      {# Info-icon toggle. Same pattern as the Audit AI page: the
         description is one click away but doesn't take up space on
         every render. Aria wiring keeps it screen-reader-usable. #}
      <button type="button" id="storage-info-toggle"
              class="text-slate-400 hover:text-slate-600 dark:text-slate-500 dark:hover:text-slate-300"
              aria-label="Show page description"
              aria-expanded="false"
              aria-controls="storage-info-banner">
        <svg class="w-5 h-5" fill="none" stroke="currentColor" stroke-width="1.75" viewBox="0 0 24 24" aria-hidden="true">
          <circle cx="12" cy="12" r="9"/>
          <line x1="12" y1="8" x2="12" y2="8.01" stroke-linecap="round"/>
          <path stroke-linecap="round" stroke-linejoin="round" d="M11 12h1v4h1"/>
        </svg>
      </button>
    </div>
    <p id="storage-info-banner" class="hidden text-base text-slate-500 dark:text-slate-400 mt-2">
      Datasets currently on LOGICAL billing where switching to PHYSICAL would reduce monthly storage cost. Each card shows the dataset-level savings and a ready-to-run <code class="font-mono text-sm">ALTER SCHEMA</code> statement.
    </p>
  </div>

  {# Page-description toggle. Runs unconditionally (not gated by any
     state) so it works even on first paint. #}
  <script>
    (function () {
      var btn = document.getElementById('storage-info-toggle');
      var banner = document.getElementById('storage-info-banner');
      if (!btn || !banner) return;
      btn.addEventListener('click', function () {
        var nowHidden = banner.classList.toggle('hidden');
        btn.setAttribute('aria-expanded', String(!nowHidden));
        btn.setAttribute(
          'aria-label',
          nowHidden ? 'Show page description' : 'Hide page description'
        );
      });
    })();
  </script>

  {% if not recommendations %}
    <div class="mt-6 ui-pane text-center py-10">
      <svg class="w-12 h-12 mx-auto text-slate-300 dark:text-slate-600" fill="none" stroke="currentColor" stroke-width="1.5" viewBox="0 0 24 24" aria-hidden="true">
        <path stroke-linecap="round" stroke-linejoin="round" d="M9 12.75L11.25 15 15 9.75M21 12a9 9 0 11-18 0 9 9 0 0118 0z"/>
      </svg>
      <h2 class="mt-4 text-lg font-semibold text-slate-900 dark:text-slate-100">No material storage billing savings</h2>
      <p class="mt-2 max-w-md mx-auto text-sm text-slate-600 dark:text-slate-400">
        No dataset clears the ${{ "%.2f" | format(materiality_usd) }}/month switch-saving threshold - every dataset is already on PHYSICAL billing, compresses too poorly for PHYSICAL to help, or saves only a trivial amount. Every scanned dataset is still listed below so you can see the full picture.
      </p>
    </div>
  {% else %}
    {# Scan-period disclaimer. Unlike query-based findings, storage
       billing figures are a point-in-time snapshot of dataset size
       at the latest scan - the period selector at the top of the
       page (e.g. "30d") doesn't apply here. Subtle blue banner so
       it reads as info, not warning. #}
    <div class="mt-6 ui-pane border-blue-200 dark:border-blue-800 bg-blue-50/60 dark:bg-blue-950/30">
      <p class="text-sm text-blue-800 dark:text-blue-200">
        <strong>Heads up:</strong> The selected scan period doesn't apply to storage figures.
        These cards reflect the dataset's current size at the time of the latest scan
        and the resulting monthly cost projection - they would look the same regardless of
        whether you scanned over 7, 30, or 180 days.
      </p>
    </div>

    {# Roll-up strip - total opportunity across all flagged datasets.
       Uses the standard stat_card macro so the label / value / accent
       typography matches the dashboard, /opportunities, and
       /opportunities/{id} stat strips 1:1 (was inline ad-hoc before). #}
    {% from "components/_stat_card.html" import stat_card %}
    <div class="mt-4 grid grid-cols-1 sm:grid-cols-3 gap-4">
      {{ stat_card("Datasets flagged", recommendations | length) }}
      {{ stat_card("Total monthly savings", "$" ~ ("%.2f" | format(total_monthly_savings)), accent="emerald") }}
      {{ stat_card("Total annual savings", "$" ~ ("%.2f" | format(total_annual_savings)), accent="emerald") }}
    </div>
    {% if below_threshold_count %}
      <p class="mt-2 text-sm text-slate-500 dark:text-slate-400">
        {{ below_threshold_count }} more dataset{{ 's' if below_threshold_count != 1 else '' }} have a positive but immaterial saving (under ${{ "%.2f" | format(materiality_usd) }}/mo) - see the full list below.
      </p>
    {% endif %}

    {# Two cards per row at lg+; single column on narrow viewports so
       the ALTER SCHEMA snippet and per-table table still have room to
       render without horizontal scroll. The internal stat-strip is
       already `grid-cols-2 sm:grid-cols-4` so it adapts to the
       narrower half-width column automatically. #}
    <div class="mt-6 grid gap-4 lg:grid-cols-2">
      {% for rec in recommendations %}
        <article class="ui-pane">
          {# Header row: dataset (left) + headline saving (right).
             Intentionally NOT using ``ui-section-title`` / ``ui-card-value``
             here - those force-uppercase the dataset name and inflate
             the headline to the same scale as the page-level roll-up
             above, making each card feel as heavy as a top-level
             section. Compact typography keeps the card scannable in
             the 2-column grid. #}
          <div class="flex flex-wrap items-start justify-between gap-3">
            <div class="min-w-0">
              <h2 class="text-base font-semibold text-slate-900 dark:text-slate-100 font-mono break-all">{{ rec.affected_table }}</h2>
              <p class="mt-1 text-sm text-slate-500 dark:text-slate-400">
                {{ rec.table_count }} table{{ '' if rec.table_count == 1 else 's' }}
                · {{ '%.1f' | format(rec.compression_ratio) }}:1 compression
              </p>
            </div>
            <div class="text-right">
              <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">Monthly saving</p>
              <p class="text-2xl font-semibold text-emerald-600 dark:text-emerald-400">${{ '%.2f' | format(rec.monthly_savings) }}</p>
              <p class="text-xs text-amber-700 dark:text-amber-400 font-medium">{{ '%.1f' | format(rec.savings_percentage) }}% reduction</p>
            </div>
          </div>

          {# Cost comparison strip - compact inline typography (no
             nested card chrome). Smaller than ``ui-card-value`` so the
             four numbers fit comfortably in the half-width card. #}
          <div class="mt-4 grid grid-cols-2 sm:grid-cols-4 gap-3 text-sm">
            <div>
              <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">Current (logical)</p>
              <p class="mt-0.5 font-medium text-slate-900 dark:text-slate-100">${{ '%.2f' | format(rec.logical_cost_monthly) }}/mo</p>
            </div>
            <div>
              <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">After switch (physical)</p>
              <p class="mt-0.5 font-medium text-emerald-600 dark:text-emerald-400">${{ '%.2f' | format(rec.physical_cost_monthly) }}/mo</p>
            </div>
            <div>
              <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">Logical bytes</p>
              <p class="mt-0.5 font-medium text-slate-900 dark:text-slate-100">{{ '%.2f' | format(rec.total_logical_gb) }} GB</p>
            </div>
            <div>
              <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">Physical bytes</p>
              <p class="mt-0.5 font-medium text-slate-900 dark:text-slate-100">{{ '%.2f' | format(rec.total_physical_gb) }} GB</p>
            </div>
          </div>

          {# ALTER SCHEMA snippet - same visual treatment as the
             BigQuery query lookup on the opportunity detail page
             (bordered surface + per-snippet Copy button), but without
             the <details>/<summary> chrome since the "Apply" label
             above already names the block and a single ALTER SCHEMA
             line doesn't warrant a collapsible. ``whitespace-pre-wrap
             break-all`` lets the dataset name wrap onto a second line
             when the card is half-width instead of overflowing. #}
          <div class="mt-4">
            <p class="text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400 mb-1">Apply</p>
            <div class="relative border border-slate-200 dark:border-slate-800 bg-white dark:bg-slate-950 overflow-x-auto">
              <button type="button"
                      onclick="window.auditCopyById(this, 'apply-sql-{{ loop.index }}')"
                      class="absolute top-2 right-2 z-10 inline-flex items-center gap-1 border border-slate-300 dark:border-slate-700 bg-white dark:bg-slate-900 text-slate-600 dark:text-slate-200 px-2 py-1 text-sm hover:border-brand-500 hover:text-brand-500"
                      title="Copy SQL"
                      aria-label="Copy SQL">
                <svg class="h-4 w-4" fill="none" stroke="currentColor" stroke-width="1.8" viewBox="0 0 24 24" aria-hidden="true">
                  <path stroke-linecap="round" stroke-linejoin="round" d="M8 7.5A2.5 2.5 0 0110.5 5h6A2.5 2.5 0 0119 7.5v9A2.5 2.5 0 0116.5 19h-6A2.5 2.5 0 018 16.5v-9z" />
                  <path stroke-linecap="round" stroke-linejoin="round" d="M6 15.5H5.5A2.5 2.5 0 013 13V4.5A2.5 2.5 0 015.5 2h6A2.5 2.5 0 0114 4.5V5" />
                </svg>
                <span class="copy-label">Copy</span>
              </button>
              <pre id="apply-sql-{{ loop.index }}" class="text-sm font-mono leading-relaxed p-3 pr-20 m-0 whitespace-pre"><code class="text-slate-800 dark:text-slate-200">ALTER SCHEMA `{{ rec.affected_table }}` SET OPTIONS (storage_billing_model = 'PHYSICAL');</code></pre>
            </div>
          </div>

          {# Per-table breakdown - collapsed by default to keep the
             card scannable. Tables with no savings still show so the
             user can see the full dataset shape. #}
          {% if rec.tables %}
            <details class="mt-4">
              <summary class="cursor-pointer text-sm text-slate-600 dark:text-slate-300 hover:text-slate-900 dark:hover:text-slate-100">
                Show per-table breakdown ({{ rec.tables | length }} table{{ '' if rec.tables|length == 1 else 's' }})
              </summary>
              <div class="mt-3 border border-slate-200 dark:border-slate-800 overflow-x-auto">
                <table class="min-w-full text-sm">
                  <thead class="bg-slate-50 dark:bg-slate-900/40 text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">
                    <tr>
                      <th class="text-left px-3 py-2 font-medium">Table</th>
                      <th class="text-right px-3 py-2 font-medium">Logical GB</th>
                      <th class="text-right px-3 py-2 font-medium">Physical GB</th>
                      <th class="text-right px-3 py-2 font-medium">Compression</th>
                      <th class="text-right px-3 py-2 font-medium">Monthly saving</th>
                    </tr>
                  </thead>
                  <tbody class="divide-y divide-slate-200 dark:divide-slate-800">
                    {% for t in rec.tables %}
                      <tr>
                        <td class="px-3 py-2 font-mono text-slate-900 dark:text-slate-100">{{ t.table_name }}</td>
                        <td class="px-3 py-2 text-right text-slate-700 dark:text-slate-300">{{ '%.2f' | format(t.total_logical_gb) }}</td>
                        <td class="px-3 py-2 text-right text-slate-700 dark:text-slate-300">{{ '%.2f' | format(t.total_physical_gb) }}</td>
                        <td class="px-3 py-2 text-right text-slate-700 dark:text-slate-300">{{ '%.1f' | format(t.compression_ratio) }}:1</td>
                        <td class="px-3 py-2 text-right {% if t.monthly_savings > 0 %}text-emerald-700 dark:text-emerald-400 font-medium{% else %}text-slate-500 dark:text-slate-400{% endif %}">${{ '%.2f' | format(t.monthly_savings) }}</td>
                      </tr>
                    {% endfor %}
                  </tbody>
                </table>
              </div>
            </details>
          {% endif %}
        </article>
      {% endfor %}
    </div>

    {# Copy-by-id helper, idempotent and shared with sql_panel callers.
       This page doesn't use the sql_panel macro (it'd add a heavy
       <details>/<summary> chrome around a single ALTER SCHEMA line),
       so we register the helper locally. The macro's own definition
       guards on ``if (window.auditCopyById) return;`` so loading
       both is safe. #}
    <script>
      (function () {
        if (window.auditCopyById) return;
        window.auditCopyById = function (btn, id) {
          var pre = document.getElementById(id);
          if (!pre) return;
          var text = pre.textContent || '';
          var label = btn.querySelector('.copy-label');
          var done = function () {
            if (!label) return;
            var original = label.textContent;
            label.textContent = 'Copied!';
            setTimeout(function () { label.textContent = original; }, 1500);
          };
          if (navigator.clipboard && navigator.clipboard.writeText) {
            navigator.clipboard.writeText(text).then(done).catch(function () {});
          }
        };
      })();
    </script>
  {% endif %}

  {# ── All scanned datasets ──────────────────────────────────────────
     Every dataset the scan touched, so nothing looks silently dropped:
     material switches (cards above), immaterial savings, and datasets
     already on PHYSICAL billing or with no saving. #}
  {% if all_datasets %}
    <div class="mt-8">
      {% from "components/_section_title.html" import section_title %}
      {{ section_title("All scanned datasets", description="Every dataset in the latest scan - " ~ (all_datasets | length) ~ " total. The cards above highlight the material switches; this lists them all, including datasets already on PHYSICAL billing or with no material saving.") }}
      <div class="mt-3 ui-pane overflow-x-auto p-0">
        <table class="min-w-full text-sm">
          <thead class="bg-slate-50 dark:bg-slate-900/40 text-xs uppercase tracking-wider text-slate-500 dark:text-slate-400">
            <tr>
              <th class="text-left px-4 py-2.5 font-medium">Dataset</th>
              <th class="text-left px-4 py-2.5 font-medium">Billing</th>
              <th class="text-right px-4 py-2.5 font-medium">Tables</th>
              <th class="text-right px-4 py-2.5 font-medium">Logical GB</th>
              <th class="text-right px-4 py-2.5 font-medium">Physical GB</th>
              <th class="text-right px-4 py-2.5 font-medium">Compression</th>
              <th class="text-right px-4 py-2.5 font-medium">Monthly saving</th>
            </tr>
          </thead>
          <tbody class="divide-y divide-slate-200 dark:divide-slate-800">
            {% for d in all_datasets %}
              <tr class="hover:bg-slate-50 dark:hover:bg-slate-800/40">
                <td class="px-4 py-2.5 font-mono text-slate-900 dark:text-slate-100 break-all">{{ d.dataset }}</td>
                <td class="px-4 py-2.5">
                  <span class="inline-flex items-center px-2 py-0.5 text-xs rounded {{ 'bg-emerald-100 text-emerald-700 dark:bg-emerald-900/40 dark:text-emerald-300' if d.billing_model == 'PHYSICAL' else 'bg-slate-100 text-slate-600 dark:bg-slate-800 dark:text-slate-300' }}">{{ d.billing_model }}</span>
                </td>
                <td class="px-4 py-2.5 text-right text-slate-700 dark:text-slate-300">{{ d.table_count }}</td>
                <td class="px-4 py-2.5 text-right text-slate-700 dark:text-slate-300">{{ '%.2f' | format(d.logical_gb) }}</td>
                <td class="px-4 py-2.5 text-right text-slate-700 dark:text-slate-300">{{ '%.2f' | format(d.physical_gb) }}</td>
                <td class="px-4 py-2.5 text-right text-slate-700 dark:text-slate-300">{% if d.compression_ratio %}{{ '%.1f' | format(d.compression_ratio) }}:1{% else %}-{% endif %}</td>
                <td class="px-4 py-2.5 text-right">
                  {% if d.state == 'already_physical' %}
                    <span class="text-slate-400 dark:text-slate-500">already physical</span>
                  {% elif d.monthly_savings > 0 %}
                    <span class="{{ 'text-emerald-700 dark:text-emerald-400 font-medium' if d.state == 'material' else 'text-slate-500 dark:text-slate-400' }}">${{ '%.2f' | format(d.monthly_savings) }}/mo</span>
                  {% else %}
                    <span class="text-slate-400 dark:text-slate-500">-</span>
                  {% endif %}
                </td>
              </tr>
            {% endfor %}
          </tbody>
        </table>
      </div>
    </div>
  {% endif %}
</section>
{% endblock %}