<?php
/**
 * sql_viewer.php — Single-file, read-only SQL viewer (mini-phpMyAdmin, Light UI)
 *
 * Setup (Empfehlung):
 *   CREATE USER 'viewer'@'%' IDENTIFIED BY 'secret';
 *   GRANT SELECT, SHOW VIEW ON `your_db`.* TO 'viewer'@'%';
 *   FLUSH PRIVILEGES;
 */

// ─────────────────────────────────────────────────────────────────────────────
// CONFIG
// ─────────────────────────────────────────────────────────────────────────────
$DB_HOST   = 'localhost';
$DB_NAME   = 'kd54698_cloud';       // ← anpassen
$DB_USER   = 'kd54698_cloud';       // ← anpassen (idealerweise Read-Only-User)
$DB_PASS   = 'w3r24asd!!!';         // ← anpassen
$DB_CHARSET= 'utf8mb4';

$dsn = "mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_CHARSET";
$options = [
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES   => false,
];

try { $pdo = new PDO($dsn, $DB_USER, $DB_PASS, $options); }
catch (Throwable $e) {
  http_response_code(500);
  echo '<pre>DB connection failed: '.htmlspecialchars($e->getMessage()).'</pre>';
  exit;
}

function h($v){ return htmlspecialchars((string)$v, ENT_QUOTES|ENT_SUBSTITUTE, 'UTF-8'); }

// ─────────────────────────────────────────────────────────────────────────────
// Helpers
// ─────────────────────────────────────────────────────────────────────────────
function all_tables(PDO $pdo){
  $list = [];
  foreach ($pdo->query("SHOW FULL TABLES WHERE Table_type='BASE TABLE'") as $r){ $list[] = array_values($r)[0]; }
  return $list;
}

function table_size(PDO $pdo, $db, $table){
  $q = $pdo->prepare("SELECT DATA_LENGTH+INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA=? AND TABLE_NAME=?");
  $q->execute([$db,$table]);
  $sz = $q->fetchColumn();
  return $sz===false? null : (int)$sz;
}

function columns(PDO $pdo, $table){
  $cols = [];
  foreach ($pdo->query("SHOW FULL COLUMNS FROM `{$table}`") as $c){ $cols[] = $c['Field']; }
  return $cols ?: ['*'];
}

function is_valid_table(PDO $pdo, $name){
  static $cache = null; if ($cache===null){ $cache = all_tables($pdo); }
  return in_array($name, $cache, true);
}

// ─────────────────────────────────────────────────────────────────────────────
// Router-like params
// ─────────────────────────────────────────────────────────────────────────────
$table  = $_GET['table'] ?? '';
$schema = isset($_GET['schema']);
$export = $_GET['export'] ?? '';

// ─────────────────────────────────────────────────────────────────────────────
// CSV export
// ─────────────────────────────────────────────────────────────────────────────
if ($export==='csv' && $table && is_valid_table($pdo,$table)){
  $cols  = columns($pdo,$table);
  $q     = trim((string)($_GET['q'] ?? ''));
  $order = $_GET['order'] ?? $cols[0];
  $dir   = strtolower($_GET['dir'] ?? 'desc'); $dir = $dir==='asc'?'ASC':'DESC';
  if (!in_array($order,$cols,true)) $order=$cols[0];

  $whereSql=''; $params=[];
  if ($q!==''){
    $concat='`'.implode('`,`',$cols).'`';
    $whereSql = "WHERE CONCAT_WS(' ', $concat) LIKE :q";
    $params[':q']="%$q%";
  }
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="'. $table .'-export.csv"');
  $out=fopen('php://output','w');
  fputcsv($out,$cols);
  $sql="SELECT * FROM `{$table}` $whereSql ORDER BY `$order` $dir";
  $stm=$pdo->prepare($sql);
  foreach($params as $k=>$v){ $stm->bindValue($k,$v,PDO::PARAM_STR);} $stm->execute();
  while($row=$stm->fetch(PDO::FETCH_ASSOC)){ fputcsv($out,$row); }
  fclose($out); exit;
}

// ─────────────────────────────────────────────────────────────────────────────
// UI HEAD (Light Design)
// ─────────────────────────────────────────────────────────────────────────────
?><!doctype html>
<html lang="de">
<head>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <title>SQL Viewer (Read-Only)</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <style>
    :root{
      --bg: #f7f8fa;
      --fg: #111111;
      --muted: #6b7280;
      --card: #ffffff;
      --card-border: #e5e7eb;
      --link: #0d6efd;
      --accent: #0d6efd;
      --soft: #eef2ff;
      --sticky: #ffffffcc; /* leicht transparent */
      --mono: ui-monospace, SFMono-Regular, Menlo, Monaco, Consolas, "Liberation Mono", monospace;
    }
    html, body{background:var(--bg); color:var(--fg);}
    a{color:var(--link);}
    .navbar{
      background: #ffffff;
      border-bottom: 1px solid var(--card-border);
    }
    .navbar-brand{color:var(--fg) !important;}
    .card{
      background:var(--card);
      border:1px solid var(--card-border);
      box-shadow: 0 1px 2px rgba(0,0,0,.03);
    }
    .form-control, .form-select{
      background:#fff; color:var(--fg);
      border-color: var(--card-border);
    }
    .form-control::placeholder{ color:#9aa3b2; }
    .badge-soft{
      background: var(--soft);
      color: #1f2937;
      border: 1px solid #c7d2fe;
      font-weight: 500;
    }
    .table-wrap{overflow:auto;max-width:100%}
    .code{font-family: var(--mono); font-variant-ligatures: none;}
    .sticky-actions{
      position:sticky; top:0; z-index:10; padding-top:.5rem;
      background: var(--sticky); backdrop-filter: blur(6px);
    }
    table.table{
      --bs-table-bg:#fff;
      --bs-table-striped-bg:#f9fafb;
      --bs-table-striped-color:var(--fg);
      --bs-table-hover-bg:#f3f4f6;
      --bs-table-hover-color:var(--fg);
      border-color: var(--card-border);
    }
    .text-secondary{ color: var(--muted) !important; }
    .btn-outline-light{ /* umgefärbt auf helles Design -> dunkel */
      --bs-btn-color:#111;
      --bs-btn-border-color:#d1d5db;
      --bs-btn-hover-bg:#f3f4f6;
      --bs-btn-hover-border-color:#9ca3af;
      --bs-btn-active-bg:#e5e7eb;
      --bs-btn-active-border-color:#9ca3af;
      --bs-btn-disabled-color:#6b7280;
      --bs-btn-disabled-border-color:#e5e7eb;
    }
    .page-link{ color:#111; }
    .page-item.active .page-link{
      background-color: var(--accent);
      border-color: var(--accent);
      color: #fff;
    }
  </style>
</head>
<body>
<nav class="navbar mb-4">
  <div class="container-fluid">
    <a class="navbar-brand fw-semibold" href="?">🔎 SQL Viewer</a>
    <span class="navbar-text small">DB: <span class="code"><?=h($DB_NAME)?></span></span>
  </div>
</nav>
<div class="container mb-5">
<?php

// ─────────────────────────────────────────────────────────────────────────────
// Views
// ─────────────────────────────────────────────────────────────────────────────
if (!$table){
  // Dashboard: Table list
  $tables = all_tables($pdo);
  echo '<div class="d-flex align-items-center justify-content-between mb-3">';
  echo '<h1 class="h4 mb-0">Tabellen</h1>';
  echo '<a class="btn btn-outline-dark btn-sm" href="?">Aktualisieren</a>';
  echo '</div>';
  if (!$tables){ echo '<div class="alert alert-warning">Keine Tabellen gefunden.</div>'; }
  else{
    echo '<div class="row g-3">';
    foreach ($tables as $t){
      // counts can be heavy on very big tables; try-catch
      try { $cnt=(int)$pdo->query("SELECT COUNT(*) FROM `".$t."`")->fetchColumn(); } catch (Throwable $e){ $cnt=0; }
      $sz = null; try { $sz=table_size($pdo,$DB_NAME,$t);} catch(Throwable $e){}
      $sizeLabel = $sz!==null? number_format($sz/1024,0,',','.').' KB' : '–';
      echo '<div class="col-12 col-md-6 col-lg-4">';
      echo '  <div class="card h-100">';
      echo '    <div class="card-body d-flex flex-column">';
      echo '      <div class="d-flex justify-content-between align-items-start mb-2">';
      echo '        <h2 class="h5 mb-0 code">'.h($t).'</h2>';
      echo '        <span class="badge badge-soft">'.$sizeLabel.'</span>';
      echo '      </div>';
      echo '      <div class="text-secondary mb-3">Einträge: <span class="code">'.number_format($cnt,0,',','.').'</span></div>';
      echo '      <div class="mt-auto d-flex gap-2">';
      echo '        <a class="btn btn-sm btn-primary" href="?table='.urlencode($t).'">Öffnen</a>';
      echo '        <a class="btn btn-sm btn-outline-dark" href="?table='.urlencode($t).'&export=csv">CSV</a>';
      echo '        <a class="btn btn-sm btn-outline-dark" href="?table='.urlencode($t).'&schema=1">Schema</a>';
      echo '      </div>';
      echo '    </div>';
      echo '  </div>';
      echo '</div>';
    }
    echo '</div>';
  }
}
elseif ($table && $schema){
  // Schema view
  if (!is_valid_table($pdo,$table)){
    echo '<div class="alert alert-danger">Ungültige Tabelle.</div>';
  } else {
    $desc = $pdo->query("SHOW FULL COLUMNS FROM `{$table}`")->fetchAll();
    echo '<div class="d-flex align-items-center justify-content-between mb-3">';
    echo '  <h1 class="h4 mb-0">Schema von <span class="code">'.h($table).'</span></h1>';
    echo '  <a class="btn btn-outline-dark btn-sm" href="?table='.urlencode($table).'">Zur Tabelle</a>';
    echo '</div>';
    echo '<div class="table-wrap">';
    echo '<table class="table table-striped table-hover align-middle">';
    echo '<thead><tr><th>Spalte</th><th>Typ</th><th>Null</th><th>Key</th><th>Default</th><th>Extras</th><th>Kommentar</th></tr></thead><tbody>';
    foreach($desc as $c){
      echo '<tr>'
        .'<td class="code">'.h($c['Field']).'</td>'
        .'<td class="code">'.h($c['Type']).'</td>'
        .'<td>'.h($c['Null']).'</td>'
        .'<td>'.h($c['Key']).'</td>'
        .'<td class="code">'.h($c['Default']).'</td>'
        .'<td class="code">'.h($c['Extra']).'</td>'
        .'<td>'.h($c['Comment'] ?? '').'</td>'
        .'</tr>';
    }
    echo '</tbody></table></div>';
  }
}
else {
  // Table view
  if (!is_valid_table($pdo,$table)){
    echo '<div class="alert alert-danger">Ungültige Tabelle.</div>';
  } else {
    $cols = columns($pdo,$table);
    $perPage = max(5, min(200, (int)($_GET['per'] ?? 25)));
    $page    = max(1, (int)($_GET['page'] ?? 1));
    $offset  = ($page-1)*$perPage;
    $q       = trim((string)($_GET['q'] ?? ''));
    $order   = $_GET['order'] ?? $cols[0];
    $dir     = strtolower($_GET['dir'] ?? 'desc'); $dir = $dir==='asc'?'ASC':'DESC';
    if (!in_array($order,$cols,true)) $order=$cols[0];

    $whereSql=''; $params=[];
    if ($q!==''){
      $concat='`'.implode('`,`',$cols).'`';
      $whereSql = "WHERE CONCAT_WS(' ', $concat) LIKE :q";
      $params[':q']="%$q%";
    }

    $stmtCount = $pdo->prepare("SELECT COUNT(*) FROM `{$table}` $whereSql");
    $stmtCount->execute($params); $total=(int)$stmtCount->fetchColumn();

    $sql = "SELECT * FROM `{$table}` $whereSql ORDER BY `$order` $dir LIMIT :limit OFFSET :offset";
    $stm = $pdo->prepare($sql);
    foreach($params as $k=>$v){ $stm->bindValue($k,$v,PDO::PARAM_STR);}
    $stm->bindValue(':limit',$perPage,PDO::PARAM_INT);
    $stm->bindValue(':offset',$offset,PDO::PARAM_INT);
    $stm->execute(); $data = $stm->fetchAll();

    $pages = max(1, (int)ceil($total/$perPage));

    echo '<div class="sticky-actions pb-3 mb-3 border-bottom" style="border-color:#e5e7eb">';
    echo '  <div class="d-flex flex-wrap gap-2 align-items-end">';
    echo '    <div><div class="text-secondary small">Tabelle</div><div class="h5 mb-0 code">'.h($table).'</div></div>';
    echo '    <form class="ms-auto d-flex gap-2" method="get" action="">';
    echo '      <input type="hidden" name="table" value="'.h($table).'">';
    echo '      <div><label class="form-label small">Suche</label><input class="form-control" type="search" name="q" placeholder="Volltext…" value="'.h($q).'"/></div>';
    echo '      <div><label class="form-label small">Sortierung</label><select class="form-select" name="order">';
    foreach($cols as $c){ echo '<option value="'.h($c).'"'.($c===$order?' selected':'').'>'.h($c).'</option>'; }
    echo '      </select></div>';
    echo '      <div><label class="form-label small">Richtung</label><select class="form-select" name="dir">';
    echo '        <option value="desc"'.($dir==='DESC'?' selected':'').'>DESC</option>';
    echo '        <option value="asc"'.($dir==='ASC'?' selected':'').'>ASC</option>';
    echo '      </select></div>';
    echo '      <div><label class="form-label small">pro Seite</label><select class="form-select" name="per">';
    foreach([10,25,50,100,200] as $n){ echo '<option value="'.$n.'"'.($perPage===$n?' selected':'').'>'.$n.'</option>'; }
    echo '      </select></div>';
    echo '      <div class="d-flex gap-2 align-items-end">';
    echo '        <button class="btn btn-primary" type="submit">Anwenden</button>';
    echo '        <a class="btn btn-outline-dark" href="?table='.urlencode($table).'&export=csv&q='.urlencode($q).'&order='.urlencode($order).'&dir='.strtolower($dir).'">CSV Export</a>';
    echo '        <a class="btn btn-outline-dark" href="?table='.urlencode($table).'&schema=1">Schema</a>';
    echo '      </div>';
    echo '    </form>';
    echo '  </div>';
    echo '</div>';

    echo '<div class="mb-2 text-secondary">Gesamt: <span class="code">'.number_format($total,0,',','.').'</span> Zeilen • Seite <span class="code">'.$page.'</span>/<span class="code">'.$pages.'</span></div>';

    echo '<div class="table-wrap">';
    echo '<table class="table table-striped table-hover table-sm align-middle">';
    echo '<thead><tr>';
    foreach($cols as $c){
      $toggle = ($order===$c && $dir==='ASC')?'desc':'asc';
      $href='?table='.urlencode($table).'&q='.urlencode($q).'&per='.$perPage.'&order='.urlencode($c).'&dir='.$toggle.'&page=1';
      echo '<th class="code"><a class="text-decoration-none" href="'.$href.'">'.h($c).'</a></th>';
    }
    echo '</tr></thead><tbody>';
    if (!$data){ echo '<tr><td colspan="'.count($cols).'" class="text-center text-secondary">Keine Daten.</td></tr>'; }
    else {
      foreach($data as $row){
        echo '<tr>';
        foreach($cols as $c){
          $val = $row[$c] ?? null;
          if (is_null($val)) echo '<td class="code text-secondary">NULL</td>';
          else {
            $str = (string)$val; if (mb_strlen($str)>120) $str = mb_substr($str,0,120).'…';
            echo '<td class="code">'.h($str).'</td>';
          }
        }
        echo '</tr>';
      }
    }
    echo '</tbody></table></div>';

    // Pagination
    $mk = function($p) use ($table,$q,$perPage,$order,$dir){
      return '?table='.urlencode($table).'&q='.urlencode($q).'&per='.$perPage.'&order='.urlencode($order).'&dir='.strtolower($dir).'&page='.$p;
    };
    echo '<nav aria-label="Seiten"><ul class="pagination pagination-sm">';
    echo '<li class="page-item '.($page<=1?'disabled':'').'"><a class="page-link" href="'.$mk(max(1,$page-1)).'">«</a></li>';
    $start=max(1,$page-3); $end=min($pages,$page+3);
    for($p=$start;$p<=$end;$p++){
      echo '<li class="page-item '.($p===$page?'active':'').'"><a class="page-link" href="'.$mk($p).'">'.$p.'</a></li>';
    }
    echo '<li class="page-item '.($page>=$pages?'disabled':'').'"><a class="page-link" href="'.$mk(min($pages,$page+1)).'">»</a></li>';
    echo '</ul></nav>';
  }
}
?>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
