// SQL query box — accepts SELECT statements against the live atlas tables.
// Tables: features (compliance features), per_head, prompts, layers, components, behaviors

const { useState: useStateSQL, useMemo: useMemoSQL } = React;

function buildTables() {
  const raw = (window.AtlasData && window.AtlasData._RAW) || {};
  const phRaw = raw.perHead || [];
  const bRaw  = raw.bouncer || [];
  const pRaw  = raw.prompts || [];

  // features ← bouncer_analysis features (already flattened + normalized)
  const features = bRaw.map(f => {
    const sp = (f.split || '').split('_').map(w => w[0] || '').join('').slice(0, 3) || 's';
    const tail = f.split === 'per_head'
      ? `.h${f.head}.d${f.top_dim}`
      : `.f${f.feature}.r${f.rank}`;
    return {
      feature_id: `${sp}.L${f.layer}.${f.component}${tail}`,
      split: f.split,
      layer: f.layer, component: f.component,
      feature: f.feature, head: f.head, rank: f.rank,
      fstat: round(f.fstat, 3), delta: round(f.delta, 4),
      leaning: f.leaning,
    };
  });

  const ov_circuits = (raw.ovCircuits || []).map(r => ({
    layer: r.layer, head: r.head, kv_head: r.kv_head,
    top_singular_val: round(r.top_singular_val, 3),
    spectral_conc:    round(r.spectral_conc, 4),
    eff_rank:         round(r.eff_rank, 2),
    compliance_score: round(r.compliance_score, 4),
    layer_comp_strength: round(r.layer_comp_strength, 4),
  }));

  const per_head = phRaw.map(r => ({
    layer: r.layer, component: r.component, head: r.head,
    dims: r.dims, non_zero_var: r.non_zero_var,
    top_sep_score: round(r.top_sep_score, 3),
    mean_sep_score: round(r.mean_sep_score, 3),
    top_code_dim: r.top_code_dim,
    top_code_spec: round(parseFloat(r.top_code_spec), 3),
  }));

  const prompts = pRaw.map((p, i) => ({
    idx: i, category: p.category, subcategory: p.subcategory, prompt: p.prompt,
  }));

  // layers: derived
  const layerMap = {};
  for (let l = 0; l < (window.AtlasData?.N_LAYERS || 0); l++) layerMap[l] = { layer: l, mean_fstat: 0, max_fstat: 0, n_features: 0 };
  for (const f of features) {
    const x = layerMap[f.layer]; if (!x) continue;
    if (!Number.isFinite(f.fstat)) continue;
    if (f.fstat > x.max_fstat) x.max_fstat = f.fstat;
    x.mean_fstat += f.fstat; x.n_features++;
  }
  const layers = Object.values(layerMap).map(x => ({
    layer: x.layer,
    n_features: x.n_features,
    mean_fstat: round(x.n_features ? x.mean_fstat / x.n_features : 0, 3),
    max_fstat: round(x.max_fstat, 3),
    position: round(x.layer / Math.max(1, (window.AtlasData?.N_LAYERS || 1) - 1), 3),
  }));

  // components table: prefer component_summary (has tax/fstat data) over COMPONENTS list
  const csRaw = raw.componentSummary || [];
  const components = csRaw.length > 0
    ? csRaw.map(r => ({ ...r }))
    : (window.AtlasData?.COMPONENTS || []).map(c => ({ component: c.id, kind: c.kind, label: c.label }));

  const behaviors = (window.AtlasData?.BEHAVIORS || []).map(b => ({
    behavior: b.id, label: b.label, prompt_count: b.count,
  }));

  return { features, per_head, prompts, layers, components, behaviors, ov_circuits };
}

function round(v, p) {
  if (v == null || Number.isNaN(v)) return v;
  const f = Math.pow(10, p);
  return Math.round(v * f) / f;
}

function parseAndRun(sql, tables) {
  const s = sql.trim().replace(/;$/, '');
  if (!/^select\s/i.test(s)) throw new Error('only SELECT is supported here.');

  const m = s.match(
    /^select\s+(.+?)\s+from\s+(\w+)(?:\s+where\s+(.+?))?(?:\s+order\s+by\s+(\w+)(?:\s+(asc|desc))?)?(?:\s+limit\s+(\d+))?$/i
  );
  if (!m) throw new Error("couldn't parse — try: SELECT * FROM features WHERE leaning = 'auth' ORDER BY fstat DESC LIMIT 20");

  const [, cols, table, whereStr, orderCol, orderDirRaw, limitRaw] = m;
  const data = tables[table.toLowerCase()];
  if (!data) throw new Error(`unknown table: ${table}. try one of: ${Object.keys(tables).join(', ')}`);

  let rows = data;

  if (whereStr) {
    const clauses = whereStr.split(/\s+and\s+/i).map(c => {
      const cm = c.trim().match(/^(\w+)\s*(>=|<=|!=|<>|>|<|=|like)\s*(.+)$/i);
      if (!cm) throw new Error('bad WHERE clause: ' + c);
      let [, col, op, valRaw] = cm;
      let val = valRaw.trim();
      if (/^['"].*['"]$/.test(val)) val = val.slice(1, -1);
      else if (!isNaN(parseFloat(val))) val = parseFloat(val);
      return { col, op: op.toLowerCase(), val };
    });
    rows = rows.filter(r => clauses.every(({ col, op, val }) => {
      const x = r[col];
      switch (op) {
        case '=':  return x === val || String(x) === String(val);
        case '!=': case '<>': return x !== val && String(x) !== String(val);
        case '>':  return x > val;
        case '<':  return x < val;
        case '>=': return x >= val;
        case '<=': return x <= val;
        case 'like': {
          const re = new RegExp('^' + String(val).replace(/%/g, '.*').replace(/_/g, '.') + '$', 'i');
          return re.test(String(x));
        }
        default: return false;
      }
    }));
  }

  if (orderCol) {
    const dir = (orderDirRaw || 'asc').toLowerCase() === 'desc' ? -1 : 1;
    rows = [...rows].sort((a, b) => {
      const av = a[orderCol], bv = b[orderCol];
      if (typeof av === 'string') return av.localeCompare(bv) * dir;
      return ((av ?? 0) - (bv ?? 0)) * dir;
    });
  }

  if (limitRaw) rows = rows.slice(0, parseInt(limitRaw, 10));

  let colList;
  if (cols.trim() === '*') {
    colList = rows[0] ? Object.keys(rows[0]) : [];
  } else {
    colList = cols.split(',').map(c => c.trim());
  }
  const projected = rows.map(r => Object.fromEntries(colList.map(c => [c, r[c]])));
  return { columns: colList, rows: projected, total: rows.length };
}

const EXAMPLES = [
  { label: 'sharpest compliance features',
    sql: "SELECT layer, component, feature, fstat, delta\nFROM features\nWHERE leaning = 'corp'\nORDER BY fstat DESC\nLIMIT 20" },
  { label: 'gate dormancy by layer',
    sql: "SELECT layer, component, tax_non_activated, n_features,\n  ROUND(100.0 * tax_non_activated / n_features, 1) AS dead_pct\nFROM components\nWHERE component = 'gate'\nORDER BY dead_pct DESC\nLIMIT 20" },
  { label: 'sharpest separator heads',
    sql: "SELECT layer, component, head, top_sep_score, top_code_spec\nFROM per_head\nORDER BY top_sep_score DESC\nLIMIT 25" },
  { label: 'L0 entanglement vs selectivity',
    sql: "SELECT layer, component, code_entangled, code_selective,\n  ROUND(1.0 * code_selective / MAX(code_entangled, 1), 2) AS sel_ratio\nFROM components\nORDER BY layer ASC, sel_ratio DESC\nLIMIT 30" },
  { label: 'richest layers',
    sql: "SELECT layer, mean_fstat, max_fstat, n_features FROM layers\nORDER BY mean_fstat DESC\nLIMIT 12" },
  { label: 'creative-writing prompts',
    sql: "SELECT idx, subcategory, prompt FROM prompts\nWHERE category = 'Creative Writing'\nLIMIT 15" },
  { label: 'sharpest OV circuits',
    sql: "SELECT layer, head, spectral_conc, eff_rank, compliance_score\nFROM ov_circuits\nORDER BY spectral_conc DESC\nLIMIT 20" },
  { label: 'compliance-aligned heads',
    sql: "SELECT layer, head, compliance_score, spectral_conc, eff_rank\nFROM ov_circuits\nORDER BY compliance_score DESC\nLIMIT 20" },
];

function SQLBox() {
  const tables = useMemoSQL(buildTables, [window.AtlasData?.META?.source, window.AtlasData?.N_LAYERS]);
  const [sql, setSql] = useStateSQL(EXAMPLES[0].sql);
  const [result, setResult] = useStateSQL(null);
  const [error, setError] = useStateSQL(null);

  const run = () => {
    try {
      setError(null);
      setResult(parseAndRun(sql, tables));
    } catch (e) {
      setError(String(e.message || e));
      setResult(null);
    }
  };

  const tableNames = Object.keys(tables);

  return (
    <div className="sql">
      <div className="sql-head">
        <div>
          <div className="bf-eyebrow">Raw query</div>
          <div className="bf-sub">SELECT against the live atlas — tables: <span className="mono">{tableNames.join(', ')}</span>.</div>
        </div>
        <div className="sql-examples">
          {EXAMPLES.map((ex, i) => (
            <button key={i} className="sql-ex" onClick={() => setSql(ex.sql)}>{ex.label}</button>
          ))}
        </div>
      </div>
      <div className="sql-editor-wrap">
        <textarea
          className="sql-editor"
          value={sql}
          onChange={e => setSql(e.target.value)}
          spellCheck={false}
          onKeyDown={(e) => { if ((e.metaKey || e.ctrlKey) && e.key === 'Enter') run(); }}
        />
        <div className="sql-actions">
          <span className="muted mono">⌘+↵ to run</span>
          <button className="sql-run" onClick={run}>run query</button>
        </div>
      </div>
      {error && <div className="sql-error">⚠ {error}</div>}
      {result && (
        <div className="sql-result">
          <div className="sql-stats muted">
            <span className="mono">{result.rows.length}</span> rows
            {result.total > result.rows.length && <span> · of {result.total} matching</span>}
          </div>
          <div className="sql-table-wrap">
            <table className="sql-table">
              <thead>
                <tr>{result.columns.map(c => <th key={c} className="mono">{c}</th>)}</tr>
              </thead>
              <tbody>
                {result.rows.slice(0, 200).map((r, i) => (
                  <tr key={i}>
                    {result.columns.map(c => (
                      <td key={c} className={typeof r[c] === 'number' ? 'mono a-right' : ''}>
                        {typeof r[c] === 'number' ? formatNum(r[c]) : String(r[c] ?? '')}
                      </td>
                    ))}
                  </tr>
                ))}
              </tbody>
            </table>
          </div>
        </div>
      )}
    </div>
  );
}

function formatNum(v) {
  if (Number.isInteger(v)) return v.toString();
  return v.toFixed(3);
}

window.SQLBox = SQLBox;