const flash = document.getElementById('flash');
let currentSchema = null;
let selectedName = null;
let objectFilter = '';

function showFlash(message, ok) {
  flash.hidden = false;
  flash.className = 'flash ' + (ok ? 'ok' : 'error');
  flash.textContent = message;
}

function cellValue(v) {
  if (v === null || v === undefined) return '<span class="muted">NULL</span>';
  if (typeof v === 'object') return escapeHtml(JSON.stringify(v));
  return escapeHtml(String(v));
}

function quoteIdent(name) {
  return /^[A-Za-z_][A-Za-z0-9_]*$/.test(name) ? name : '"' + String(name).replaceAll('"', '""') + '"';
}

function matchesFilter(name) {
  if (!objectFilter) return true;
  return String(name || '').toLowerCase().includes(objectFilter);
}

function renderObjectList(listEl, items, emptyText) {
  const filtered = (items || []).filter(t => matchesFilter(t.name));
  if (!filtered.length) {
    listEl.innerHTML = `<li class="muted">${escapeHtml(emptyText)}</li>`;
    return;
  }
  listEl.innerHTML = filtered.map(t => {
    const active = selectedName === t.name ? ' active' : '';
    return `<li><button type="button" class="sql-object-item${active}" data-name="${escapeHtml(t.name)}">` +
      `<span class="sql-object-name">${escapeHtml(t.name)}</span>` +
      `<span class="sql-object-rows muted">${Number(t.rowCount).toLocaleString('de-DE')}</span>` +
      `</button></li>`;
  }).join('');

  listEl.querySelectorAll('button[data-name]').forEach(btn => {
    btn.addEventListener('click', () => selectTable(btn.getAttribute('data-name')));
  });
}

function setTab(tabId) {
  document.querySelectorAll('.sql-tab').forEach(btn => {
    const on = btn.getAttribute('data-tab') === tabId;
    btn.classList.toggle('active', on);
    btn.setAttribute('aria-selected', on ? 'true' : 'false');
  });
  document.querySelectorAll('.sql-tab-panel').forEach(panel => {
    const on = panel.id === 'tab-' + tabId;
    panel.hidden = !on;
    panel.classList.toggle('active', on);
  });
}

async function loadSchema() {
  const res = await api('/api/sqladmin/schema');
  const data = await res.json().catch(() => ({}));
  if (!res.ok) {
    document.getElementById('dbProvider').textContent = 'Fehler';
    document.getElementById('dbPath').textContent = data.error || 'Schema nicht ladbar';
    document.getElementById('tablesList').innerHTML = `<li class="crit">${escapeHtml(data.error || 'Fehler')}</li>`;
    document.getElementById('viewsList').innerHTML = '';
    return;
  }

  currentSchema = data;
  const provider = data.provider || '—';
  const path = data.databasePath || '(Remote-DB – siehe Einstellungen)';
  document.getElementById('dbProvider').textContent = provider;
  document.getElementById('dbPath').textContent = path;
  document.getElementById('dbPath').title = path;
  document.getElementById('dbMeta').textContent =
    `${provider} · ${(data.tables || []).length} Tabellen · ${(data.views || []).length} Views`;
  document.getElementById('tablesCount').textContent = `(${(data.tables || []).length})`;
  document.getElementById('viewsCount').textContent = `(${(data.views || []).length})`;

  renderObjectList(document.getElementById('tablesList'), data.tables, 'Keine Tabellen');
  renderObjectList(document.getElementById('viewsList'), data.views, 'Keine Views');

  if (selectedName) {
    const stillThere = [...(data.tables || []), ...(data.views || [])].some(t => t.name === selectedName);
    if (!stillThere) selectedName = null;
  }
}

async function selectTable(name) {
  selectedName = name;
  renderObjectList(document.getElementById('tablesList'), currentSchema?.tables, 'Keine Tabellen');
  renderObjectList(document.getElementById('viewsList'), currentSchema?.views, 'Keine Views');

  const res = await api('/api/sqladmin/tables/' + encodeURIComponent(name));
  const data = await res.json().catch(() => ({}));
  if (!res.ok) {
    showFlash(data.error || 'Objekt konnte nicht geladen werden.', false);
    return;
  }

  const rowsLabel = `${Number(data.rowCount).toLocaleString('de-DE')} Zeilen`;
  document.getElementById('dataTitle').textContent = data.name;
  document.getElementById('dataMeta').textContent = `${data.type} · ${rowsLabel}`;
  document.getElementById('structureTitle').textContent = `Struktur · ${data.name}`;

  document.getElementById('columnsBody').innerHTML = (data.columns || []).map(c =>
    `<tr><td>${escapeHtml(c.name)}</td><td class="mono">${escapeHtml(c.dataType)}</td>` +
    `<td>${c.notNull ? 'ja' : 'nein'}</td><td>${c.primaryKey ? 'ja' : ''}</td>` +
    `<td class="mono">${c.defaultValue != null ? escapeHtml(c.defaultValue) : ''}</td></tr>`
  ).join('') || '<tr><td colspan="5" class="muted">—</td></tr>';

  document.getElementById('indexesBody').innerHTML = (data.indexes || []).map(i =>
    `<tr><td>${escapeHtml(i.name)}</td><td>${i.unique ? 'ja' : 'nein'}</td><td class="mono">${escapeHtml((i.columns || []).join(', '))}</td></tr>`
  ).join('') || '<tr><td colspan="3" class="muted">Keine Indizes</td></tr>';

  const sample = data.sampleRows || [];
  const cols = sample.length ? Object.keys(sample[0]) : [];
  document.getElementById('dataHead').innerHTML = cols.length
    ? '<tr>' + cols.map(c => `<th>${escapeHtml(c)}</th>`).join('') + '</tr>'
    : '';
  document.getElementById('dataBody').innerHTML = sample.length
    ? sample.map(row => '<tr>' + cols.map(c => `<td>${cellValue(row[c])}</td>`).join('') + '</tr>').join('')
    : `<tr><td class="muted">Keine Daten</td></tr>`;

  document.getElementById('sqlInput').value = `SELECT * FROM ${quoteIdent(name)} LIMIT 100;`;
  setTab('data');
}

async function runQuery() {
  const sql = document.getElementById('sqlInput').value;
  const btn = document.getElementById('btnRunQuery');
  const meta = document.getElementById('queryMeta');
  btn.disabled = true;
  meta.textContent = 'Läuft…';
  try {
    const res = await api('/api/sqladmin/query', { method: 'POST', body: JSON.stringify({ sql }) });
    const data = await res.json().catch(() => ({}));
    if (!res.ok || !data.ok) {
      showFlash(data.error || 'Abfrage fehlgeschlagen.', false);
      meta.textContent = '';
      return;
    }

    setTab('sql');
    if (data.isReadQuery) {
      const cols = data.columns || [];
      document.getElementById('resultHead').innerHTML = '<tr>' + cols.map(c => `<th>${escapeHtml(c)}</th>`).join('') + '</tr>';
      document.getElementById('resultBody').innerHTML = (data.rows || []).map(row =>
        '<tr>' + row.map(v => `<td>${cellValue(v)}</td>`).join('') + '</tr>'
      ).join('') || `<tr><td colspan="${cols.length || 1}" class="muted">Keine Zeilen</td></tr>`;
      meta.textContent = `${data.rowCount} Zeile(n)${data.truncated ? ' (auf 3000 begrenzt)' : ''} · ${data.elapsedMs} ms`;
    } else {
      document.getElementById('resultHead').innerHTML = '';
      document.getElementById('resultBody').innerHTML = `<tr><td class="muted">${data.affectedRows ?? 0} Zeile(n) betroffen</td></tr>`;
      meta.textContent = `${data.affectedRows ?? 0} betroffen · ${data.elapsedMs} ms`;
      showFlash('Anweisung ausgeführt.', true);
      await loadSchema();
    }
  } catch (e) {
    showFlash(e.message, false);
    meta.textContent = '';
  } finally {
    btn.disabled = false;
  }
}

async function backup() {
  const btn = document.getElementById('btnBackup');
  btn.disabled = true;
  try {
    const res = await api('/api/sqladmin/backup', { method: 'POST' });
    const data = await res.json().catch(() => ({}));
    showFlash(data.message || (res.ok ? 'Backup erstellt.' : 'Backup fehlgeschlagen.'), res.ok);
  } catch (e) {
    showFlash(e.message, false);
  } finally {
    btn.disabled = false;
  }
}

async function optimize() {
  const btn = document.getElementById('btnOptimize');
  btn.disabled = true;
  try {
    const res = await api('/api/sqladmin/optimize', { method: 'POST' });
    const data = await res.json().catch(() => ({}));
    showFlash(data.message || (res.ok ? 'Optimierung abgeschlossen.' : 'Optimierung fehlgeschlagen.'), res.ok);
  } catch (e) {
    showFlash(e.message, false);
  } finally {
    btn.disabled = false;
  }
}

async function init() {
  await initLocale();
  document.getElementById('mainNav').innerHTML = chromeNav('sqladmin');
  setActiveNav('sqladmin');
  document.getElementById('btnLogout').addEventListener('click', logout);
  document.getElementById('btnRefreshSchema').addEventListener('click', loadSchema);
  document.getElementById('btnBackup').addEventListener('click', backup);
  document.getElementById('btnOptimize').addEventListener('click', optimize);
  document.getElementById('btnRunQuery').addEventListener('click', runQuery);
  document.getElementById('objectFilter').addEventListener('input', (e) => {
    objectFilter = String(e.target.value || '').trim().toLowerCase();
    renderObjectList(document.getElementById('tablesList'), currentSchema?.tables, 'Keine Tabellen');
    renderObjectList(document.getElementById('viewsList'), currentSchema?.views, 'Keine Views');
  });
  document.querySelectorAll('.sql-tab').forEach(btn => {
    btn.addEventListener('click', () => setTab(btn.getAttribute('data-tab')));
  });

  try {
    const me = await loadMe();
    if (me?.organizationName)
      document.getElementById('orgName').textContent = me.organizationName;
    if (me?.license) {
      renderLicenseBanner(me.license);
      if (!me.license.canUseSqlAdmin) {
        showFlash('SQL-Admin ist nicht freigeschaltet (Testversion abgelaufen oder Lizenz fehlt).', false);
      }
    }
    if (me?.role) applyRoleNav(me.role);
  } catch (_) {}

  await loadSchema();
}

init();
