{# Spec 151 - per-table layout recommendation detail.

   Renders:
     - Header (table name, region, size, window cost)
     - Current vs proposed layout (side-by-side)
     - Ranked candidate columns (table)
     - Apply SQL (copy panel)
     - Rollback SQL (copy panel)
     - Caveats list

   Empty state (no recommendation in latest scan) renders a single
   explanation card with a "Re-run scan" link instead of 404ing.
#}
{% extends "layout.html" %}

{% block content %}
{% from "components/_page_header.html" import back_link, header_card %}

{# Spec 151 US3 — small "Deep analyse" entry point per candidate
   column. Renders as either a cached-result chip (when the user
   has already run deep analysis on that column) or a tiny form
   button that POSTs to the /deep/preview endpoint to get a cost
   estimate before the chargeable run. The route accepts
   ``column_name`` as form data; the table_full_name is on the URL. #}
{%- macro _deep_button(table_full_name, column_name, deep_results_by_column) -%}
  {%- set _deep = (deep_results_by_column or {}).get(column_name) -%}
  {%- if _deep -%}
    <span class="inline-flex items-center gap-1 text-xs px-2 py-0.5 border border-slate-300 dark:border-slate-700 text-slate-600 dark:text-slate-300 bg-white dark:bg-slate-900"
          title="Deep analysis cached — approx distinct count from your actual data">
      ~{{ "{:,}".format(_deep.approx_distinct_count or 0) }} distinct
    </span>
  {%- else -%}
    <form method="post" action="/recommendations/layout/{{ table_full_name | urlencode }}/deep/preview" class="inline-flex">
      <input type="hidden" name="column_name" value="{{ column_name }}" />
      <button type="submit"
              class="inline-flex items-center gap-1 text-xs px-2 py-0.5 border border-brand-300 dark:border-brand-700 text-brand-600 dark:text-brand-300 hover:bg-brand-50 dark:hover:bg-brand-900/30 transition-colors"
              title="Run a billed BigQuery query against your data to measure distinct-count / cardinality on this column. Cost shown for confirmation before the run.">
        Deep analyse &rarr;
      </button>
    </form>
  {%- endif -%}
{%- endmacro %}

<section class="ui-shell">
<div class="space-y-6">
  {{ back_link("/recommendations/layout", "Partition &amp; Cluster") }}

  {% set _path_parts = (table_full_name or '').split('.') %}
  {% set _dataset = _path_parts[1] if _path_parts | length >= 2 else '' %}
  {% set _table = _path_parts[2] if _path_parts | length >= 3 else (table_full_name or '') %}
  {% set _title %}<code class="font-mono">{% if _dataset %}{{ _dataset }}.{% endif %}{{ _table }}</code>{% endset %}
  {{ header_card(_title, caption="Partition &amp; Cluster recommendation") }}

  {% if not recommendation %}
    <div class="ui-pane text-base text-slate-700 dark:text-slate-300 space-y-3">
      <p>
        No recommendation in the latest scan for this table. The most common
        reasons:
      </p>
      <ul class="list-disc pl-5 space-y-1">
        <li>The table is below 10&nbsp;GB - clustering's benefit doesn't outweigh metadata cost.</li>
        <li>Consumer queries don't filter / join / group on enough common columns to give a confident signal.</li>
        <li>The table is already optimally laid out for the workload we see.</li>
      </ul>
      <p>
        Recommendations are derived from the workload the latest scan
        observed. <a href="/admin/configurations" class="underline decoration-dotted text-brand-600 dark:text-brand-300">Run another scan</a> if you suspect this changed recently.
      </p>
    </div>
  {% else %}
    {# Three-card stat strip - reframed to answer "is this worth doing?":
       1. Table size           - magnitude check (is this a big table?)
       2. Cost in scan window  - what we'd potentially reduce
       3. Affected downstreams - count of consumer models with active
                                 issues (the actionable signal -
                                 "Contributing jobs" was just evidence
                                 density and not user-meaningful) #}
    {% set _impacted_count = (lineage_payload.nodes | rejectattr('kind', 'equalto', 'target') | list | length) if lineage_payload else 0 %}
    <style>
      @media (min-width: 1280px) {
        .audit-layout-stat-row {
          grid-template-columns: repeat(3, minmax(0, 1fr));
        }
      }
    </style>
    {% from "components/_stat_card.html" import stat_card %}
    <div class="audit-layout-stat-row grid gap-3 grid-cols-1 sm:grid-cols-2 lg:grid-cols-3">
      {{ stat_card("Table size", recommendation.table_total_bytes | format_bytes) }}
      {{ stat_card(
           "Cost in scan window",
           recommendation.table_total_cost_in_window_usd | format_usd,
           meta="across " ~ recommendation.contributing_job_count ~ " consumer job" ~ ("s" if recommendation.contributing_job_count != 1 else "")
      ) }}
      {% if _impacted_count > 0 %}
        {{ stat_card(
             "Affected downstreams",
             _impacted_count,
             meta="model" ~ ("s" if _impacted_count != 1 else "") ~ " with active issues",
             accent="rose"
        ) }}
      {% else %}
        {{ stat_card("Affected downstreams", 0, meta="no flagged downstreams - preventative", accent="emerald") }}
      {% endif %}
    </div>

    {# US2 - BigQuery second-opinion panel. Renders when a
       LayoutBqSecondOpinion row exists for this recommendation;
       otherwise renders a banner explaining the IAM grant. #}
    {% if bq_second_opinion %}
      {# "Audit has no pick" detection — empty audit fields tell us the
         row was created from Google's recommendation alone (BQ-only
         row, not analyzed by the audit's heuristic). Used to suppress
         the misleading "Different top pick" badge and to swap the
         Audit-picks panel for a clear "audit didn't analyze" note. #}
      {% set _audit_has_pick = recommendation.recommended_partition_column or cluster_candidates %}
      <div class="ui-pane mt-6">
        <div class="flex items-center justify-between gap-3 flex-wrap">
          <p class="text-sm uppercase tracking-wider text-slate-500 dark:text-slate-400">BigQuery's recommendation (second opinion)</p>
          {% if not _audit_has_pick %}
            <span class="inline-flex items-center gap-1.5 text-sm text-slate-600 dark:text-slate-300">
              <span class="w-1.5 h-1.5 rounded-full bg-slate-400"></span>
              BigQuery only — audit didn't analyze this table
            </span>
          {% elif bq_second_opinion.agrees_with_audit_top_pick %}
            <span class="inline-flex items-center gap-1.5 text-sm text-emerald-700 dark:text-emerald-300">
              <span class="w-1.5 h-1.5 rounded-full bg-emerald-500"></span>
              Agreement on top column
            </span>
          {% else %}
            <span class="inline-flex items-center gap-1.5 text-sm text-amber-700 dark:text-amber-300">
              <span class="w-1.5 h-1.5 rounded-full bg-amber-500"></span>
              Different top pick
            </span>
          {% endif %}
        </div>

        <div class="grid grid-cols-1 md:grid-cols-2 gap-4 mt-4">
          <div>
            <p class="text-sm uppercase tracking-wider text-slate-500 dark:text-slate-400 mb-1.5">BigQuery picks</p>
            {% if bq_second_opinion.bq_recommended_columns %}
              <div class="flex flex-wrap gap-1.5">
                {% for col in bq_second_opinion.bq_recommended_columns %}
                  <span class="font-mono text-sm px-2 py-0.5 border border-slate-200 dark:border-slate-700 text-slate-700 dark:text-slate-300 bg-slate-50 dark:bg-slate-800/40">{{ col }}</span>
                {% endfor %}
              </div>
            {% else %}
              <p class="text-sm text-slate-400">No specific columns surfaced</p>
            {% endif %}
            <p class="text-sm text-slate-400 mt-2">Methodology: BigQuery's ML-based recommender - slot-time + scan-pattern weighting on the recent workload.</p>
          </div>
          <div>
            <p class="text-sm uppercase tracking-wider text-slate-500 dark:text-slate-400 mb-1.5">Audit picks</p>
            {% if _audit_has_pick %}
              <div class="flex flex-wrap gap-1.5">
                {% if recommendation.recommended_partition_column %}
                  <span class="font-mono text-sm px-2 py-0.5 border border-emerald-200 dark:border-emerald-800 text-emerald-700 dark:text-emerald-300 bg-emerald-50 dark:bg-emerald-900/30">{{ recommendation.recommended_partition_column }}</span>
                {% endif %}
                {% for c in cluster_candidates %}
                  <span class="font-mono text-sm px-2 py-0.5 border border-slate-200 dark:border-slate-700 text-slate-700 dark:text-slate-300 bg-slate-50 dark:bg-slate-800/40">{{ c.column_name }}</span>
                {% endfor %}
              </div>
              <p class="text-sm text-slate-400 mt-2">Methodology: cost-weighted filter / join / group-by frequency across {{ recommendation.contributing_job_count }} cached queries.</p>
            {% else %}
              <p class="text-sm text-slate-500 dark:text-slate-400">No audit pick — the table didn't meet the audit's size gates (≥&nbsp;10&nbsp;GB cluster / ≥&nbsp;100&nbsp;GB partition) or had too few consumer queries in the scan window to converge on a column. The audit's recommendations require both.</p>
              <p class="text-sm text-slate-400 mt-2">Methodology: cost-weighted filter / join / group-by frequency across cached consumer queries — needs a populated window to fire.</p>
            {% endif %}
          </div>
        </div>

        {% if bq_second_opinion.bq_description %}
          <p class="text-sm text-slate-600 dark:text-slate-400 mt-4 italic">{{ bq_second_opinion.bq_description }}</p>
        {% endif %}
      </div>
    {% else %}
      <div class="ui-pane mt-6 border-blue-200 dark:border-blue-800 bg-blue-50 dark:bg-blue-950/30">
        <p class="text-sm font-semibold uppercase tracking-wider text-blue-700 dark:text-blue-300">BigQuery second opinion</p>
        <p class="text-sm text-blue-800 dark:text-blue-200 mt-1.5">
          Add <code class="font-mono text-sm bg-blue-100 dark:bg-blue-900/60 px-1.5 py-0.5">roles/recommender.bigqueryPartitionClusterViewer</code> to the principal that runs the scan to surface BigQuery's own recommendation alongside the audit's. The page works without it &mdash; this enables a second-opinion panel that increases confidence when both methodologies agree.
        </p>
      </div>
    {% endif %}

    {# Why this recommendation - rationale panel. Explains the reasoning
       behind the picked partition / cluster columns and how a layout
       change ripples into the downstream issues shown in the lineage
       below. Replaces the deleted "Ranked candidates" table - same
       evidence, surfaced in plain English. #}
    {% set _part_pick = partition_candidate %}
    {% set _cluster_pick = (cluster_candidates | first) if cluster_candidates else None %}
    {% set _consumers = (lineage_payload.nodes | rejectattr('kind', 'equalto', 'target') | list) if lineage_payload else [] %}
    <div class="ui-pane">
      <p class="text-sm uppercase tracking-wider text-slate-500 dark:text-slate-400">Why this recommendation</p>
      <div class="mt-3 grid grid-cols-1 lg:grid-cols-2 gap-4">
        <div class="bg-slate-100 dark:bg-slate-800 p-4">
          <h3 class="text-sm font-semibold text-slate-700 dark:text-slate-200">Picked columns</h3>
          <ul class="mt-2 space-y-3 text-sm text-slate-700 dark:text-slate-300">
            {% if _part_pick %}
              <li class="flex flex-wrap items-center gap-2">
                <span class="inline-block px-2 py-0.5 bg-emerald-100 dark:bg-emerald-900/50 text-emerald-800 dark:text-emerald-200 text-sm font-mono">PARTITION BY {{ _part_pick.column_name }}</span>
                <span class="text-sm text-slate-500 dark:text-slate-400">filter frequency {{ "%.0f%%" | format(_part_pick.cost_share * 100) }} - score {{ "%.2f" | format(_part_pick.score) }}</span>
                {{ _deep_button(recommendation.table_full_name, _part_pick.column_name, deep_results_by_column) }}
              </li>
            {% endif %}
            {% for c in cluster_candidates %}
              <li class="flex flex-wrap items-center gap-2">
                <span class="inline-block px-2 py-0.5 bg-brand-100 dark:bg-brand-900/50 text-brand-800 dark:text-brand-200 text-sm font-mono">CLUSTER #{{ c.cluster_rank }} {{ c.column_name }}</span>
                <span class="text-sm text-slate-500 dark:text-slate-400">{{ c.usage_type | replace('_', ' ') }} frequency {{ "%.0f%%" | format(c.cost_share * 100) }} - score {{ "%.2f" | format(c.score) }}</span>
                {{ _deep_button(recommendation.table_full_name, c.column_name, deep_results_by_column) }}
              </li>
            {% endfor %}
            {% if not _part_pick and not cluster_candidates %}
              <li class="text-slate-500 dark:text-slate-400">No picks - workload signal was below the partition / cluster gates.</li>
            {% endif %}
          </ul>
          {# Spec 151 US3 entry point. Each candidate column above gets
             a small "Deep analyse" button that POSTs to the preview
             endpoint — backend does a dry-run and returns the cost
             estimate the user has to acknowledge before the
             chargeable run. Cached results show inline so the user
             sees the approx distinct count without re-running. #}
        </div>
        <div class="bg-slate-100 dark:bg-slate-800 p-4">
          <h3 class="text-sm font-semibold text-slate-700 dark:text-slate-200">How it helps the downstream issues</h3>
          <p class="mt-2 text-sm text-slate-700 dark:text-slate-300 leading-relaxed">
            {% if _consumers | length > 0 %}
              {{ _consumers | length }} downstream model{{ 's' if _consumers | length != 1 else '' }} reading from this table carries active issues today.
              {% if _cluster_pick %}
                Clustering by <code class="font-mono">{{ _cluster_pick.column_name }}</code> reorders the table's micro-files so reads filtered on it scan fewer bytes - the same scans that drive slot-contention and shuffle-spill on those downstreams. The lineage below names each impacted model.
              {% elif _part_pick %}
                Partitioning by <code class="font-mono">{{ _part_pick.column_name }}</code> lets BigQuery prune partitions at read time, reducing the byte volume that drives slot-contention and shuffle-spill on those downstreams.
              {% endif %}
            {% else %}
              No downstream models with active issues today. The recommendation is preventative - it locks in efficient layout before issues appear at scale.
            {% endif %}
          </p>
          <p class="mt-3 text-sm text-slate-500 dark:text-slate-400 leading-relaxed">
            Pick methodology: cost-weighted filter / join / group-by frequency across {{ recommendation.contributing_job_count }} cached consumer job{{ 's' if recommendation.contributing_job_count != 1 else '' }}, intersected with the table's schema. Columns must clear the materiality (&geq; 1&nbsp;GB), partition (DATE / TIMESTAMP, 70&nbsp;% filter), and cluster (50&nbsp;% combined) gates.
          </p>
        </div>
      </div>
    </div>

    {# Downstream lineage - pure HTML/SVG dbt-style chip layout. Target
       chip on the left, every consumer model with active issues stacked
       on the right; curved SVG connectors drawn between them. Pure CSS
       + tiny inline SVG so it always renders (the previous ECharts
       attempt failed silently on the user's data). #}
    <div class="ui-pane">
      <div class="flex items-start justify-between gap-3 mb-4 flex-wrap">
        <div>
          <p class="text-sm uppercase tracking-wider text-slate-500 dark:text-slate-400">Downstream lineage (impacted models)</p>
          <p class="text-sm text-slate-500 dark:text-slate-400 mt-1">Consumer models with active issues that read from this table. Click a chip to open its opportunity detail.</p>
        </div>
        <div class="flex flex-wrap items-center gap-3 text-sm text-slate-500 dark:text-slate-400">
          <span class="inline-flex items-center gap-1.5"><span class="inline-block w-4 h-2.5 rounded bg-brand-500"></span>this table</span>
          <span class="inline-flex items-center gap-1.5"><span class="inline-block w-4 h-2.5 rounded bg-rose-500"></span>issue</span>
        </div>
      </div>
      {% if _consumers | length == 0 %}
        <div class="py-10 text-center text-sm text-slate-500 dark:text-slate-400">
          No downstream models with active issues today. Models reading this table are either clean or only carry SQL-rewrite suggestions.
        </div>
      {% else %}
        {# Cap visible at 12 so the lineage stays a sensible height
           even when a base table fans out to 30+ downstreams. Top
           rows are picked by issue_count, then cost. #}
        {% set _max_visible = 12 %}
        {% set _sorted_consumers = _consumers | sort(attribute='total_cost_usd', reverse=true) | sort(attribute='issue_count', reverse=true) %}
        {% set _visible = _sorted_consumers[:_max_visible] %}
        {% set _hidden_count = (_consumers | length) - (_visible | length) %}
        {# ECharts ``tree`` series renders a clean dbt-style left-to-
           right hierarchical layout for free: chip-shaped nodes,
           smooth bezier edges, automatic spacing. We pre-compute the
           tree payload server-side and pass it via ``data-payload``;
           the script below feeds it straight into ECharts. #}
        <div id="layout-lineage-tree"
             class="h-[{{ (_visible | length * 40 + 80) }}px] w-full"
             style="min-height: 360px;"
             data-target='{{ table_full_name }}'
             data-consumers='{{ _visible | tojson }}'></div>
        {% if _hidden_count > 0 %}
          <p class="mt-3 text-sm text-slate-500 dark:text-slate-400 text-right">
            + {{ _hidden_count }} more impacted model{{ 's' if _hidden_count != 1 else '' }} -
            <a class="text-brand-600 dark:text-brand-300 hover:underline"
               href="/opportunities?search={{ table_full_name.split('.')[-1] | urlencode }}">view all</a>
          </p>
        {% endif %}
        <script>
          (function() {
            var el = document.getElementById('layout-lineage-tree');
            if (!el || typeof echarts === 'undefined') return;
            var target = el.getAttribute('data-target') || '';
            var consumers;
            try {
              consumers = JSON.parse(el.getAttribute('data-consumers') || '[]');
            } catch (e) {
              return;
            }
            var isDark = document.documentElement.classList.contains('dark');
            var brand = isDark ? '#60a5fa' : '#2563eb';
            var rose = isDark ? '#fb7185' : '#f43f5e';
            var muted = isDark ? '#64748b' : '#94a3b8';
            var ink = isDark ? '#e2e8f0' : '#0f172a';

            function truncate(s, n) {
              return s.length > n ? s.slice(0, n - 1) + '…' : s;
            }

            var children = consumers.map(function(c) {
              var label = truncate(c.label, 36);
              return {
                name: label,
                _meta: c,
                value: c.total_cost_usd || 0,
                itemStyle: { color: rose, borderColor: rose, borderWidth: 0 },
                label: {
                  color: '#ffffff',
                  fontFamily: 'ui-monospace, SFMono-Regular, Menlo, monospace',
                  fontSize: 11,
                  padding: [4, 8, 4, 8],
                  backgroundColor: rose,
                  borderRadius: 4,
                  align: 'left',
                  formatter: function() {
                    return label + (c.issue_count ? '  · ' + c.issue_count : '');
                  }
                }
              };
            });

            var tree = [{
              name: target.split('.').pop(),
              itemStyle: { color: brand, borderColor: brand, borderWidth: 0 },
              label: {
                color: isDark ? '#0f172a' : '#ffffff',
                fontFamily: 'ui-monospace, SFMono-Regular, Menlo, monospace',
                fontSize: 12,
                fontWeight: 600,
                padding: [5, 10, 5, 10],
                backgroundColor: brand,
                borderRadius: 4,
                align: 'left'
              },
              children: children
            }];

            var chart = echarts.init(el, isDark ? 'dark' : null);
            chart.setOption({
              backgroundColor: 'transparent',
              tooltip: {
                trigger: 'item',
                formatter: function(p) {
                  var m = (p.data || {})._meta;
                  if (!m) return '<strong>' + p.name + '</strong><br/>Target table';
                  var lines = ['<strong>' + m.label + '</strong>'];
                  lines.push('Executions: ' + (m.execution_count || 0));
                  lines.push('Cost: $' + (m.total_cost_usd || 0).toFixed(2));
                  if (m.issue_count) lines.push('Issues: ' + m.issue_count);
                  return lines.join('<br/>');
                }
              },
              series: [{
                type: 'tree',
                orient: 'LR',
                data: tree,
                top: '4%',
                bottom: '4%',
                left: '14%',
                right: '24%',
                symbol: 'emptyCircle',
                symbolSize: 0,
                edgeShape: 'curve',
                edgeForkPosition: '63%',
                lineStyle: { color: muted, width: 1.25, curveness: 0.5 },
                label: { show: true, position: 'inside' },
                leaves: { label: { show: true, position: 'inside' } },
                expandAndCollapse: false,
                animationDuration: 300,
                emphasis: { focus: 'descendant' }
              }]
            });

            chart.on('click', function(p) {
              var m = (p.data || {})._meta;
              if (!m) return;
              window.open(
                '/opportunities?search=' + encodeURIComponent(m.label),
                '_blank'
              );
            });

            window.addEventListener('resize', function() { chart.resize(); });
          })();
        </script>
      {% endif %}
    </div>

    {# Current / Proposed layout panel removed - the picks are already
       surfaced in the "Picked columns" sub-panel of the rationale,
       and the exact ALTER SQL is in the Apply panel below. Two
       restatements were redundant. #}


    {# Apply / Rollback SQL panels. #}
    {% from "components/_copy_pre.html" import copy_pre %}
    {% if recommendation.apply_sql %}
      {{ copy_pre("apply-sql", "Apply", recommendation.apply_sql) }}
    {% endif %}
    {% if recommendation.rollback_sql %}
      {{ copy_pre("rollback-sql", "Rollback", recommendation.rollback_sql) }}
    {% endif %}

    {# Caveats - use render_caveats (route-computed) so per-request
       warnings like PARTITION_CARDINALITY_EXCEEDED show alongside the
       persisted caveats. #}
    {% set _caveats = render_caveats if render_caveats is defined else recommendation.caveats %}
    {% if _caveats %}
      <div class="ui-pane mt-6 border-blue-200 dark:border-blue-800 bg-blue-50 dark:bg-blue-950/30">
        <p class="text-sm font-semibold uppercase tracking-wider text-blue-700 dark:text-blue-300 mb-2">Caveats</p>
        <ul class="space-y-2 text-sm text-blue-800 dark:text-blue-200">
          {% for caveat in _caveats %}
            <li>
              <span class="text-sm font-mono uppercase tracking-wider mr-2 text-blue-600 dark:text-blue-400">{{ caveat.code }}</span>
              {{ caveat.message }}
            </li>
          {% endfor %}
        </ul>
      </div>
    {% endif %}
  {% endif %}
</section>
{% endblock %}