admin/games/manage.php

<?php
declare(strict_types=1);

require __DIR__ . '/../inc/admin_boot.php';
$pdo = Database::pdo();
$err=''; $msg='';

/* detect optional is_featured column */
$hasFeatured = false;
try {
  $cols = $pdo->query("SHOW COLUMNS FROM games")->fetchAll(PDO::FETCH_COLUMN);
  $cols = array_map('strtolower',$cols);
  $hasFeatured = in_array('is_featured',$cols,true);
} catch (Throwable $e) {}

/* ensure column hint */
$featuredHint = '';
if (!$hasFeatured) {
  $featuredHint = "Heads up: your games table has no is_featured column. Run:
  ALTER TABLE `games` ADD COLUMN `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_published`;";
}

/* -------------------------------------------------
   EXPORT ALL GAMES (ignores filters & pagination)
------------------------------------------------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['export_all']) && $_POST['export_all'] === '1') {
  csrf_check();
  try {
    // Determine game columns (excluding id + category_id)
    $colStmt = $pdo->query("SHOW COLUMNS FROM games");
    $gameCols = [];
    while ($c = $colStmt->fetch(PDO::FETCH_ASSOC)) {
      $name = (string)$c['Field'];
      if ($name === 'id' || $name === 'category_id') {
        continue;
      }
      $gameCols[] = $name;
    }

    // Fetch ALL games with category name
    $st = $pdo->query("
      SELECT g.*, c.name AS category_name
      FROM games g
      LEFT JOIN categories c ON c.id = g.category_id
      ORDER BY g.id ASC
    ");
    $games = [];
    while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
      $fields = [];
      foreach ($gameCols as $col) {
        if (array_key_exists($col, $row)) {
          $fields[$col] = $row[$col];
        }
      }
      $games[] = [
        'fields'        => $fields,
        'category_name' => $row['category_name'] ?? '',
      ];
    }

    $payload = [
      'export_type'  => 'games',
      'scope'        => 'all',
      'exported_at'  => gmdate('c'),
      'source_host'  => $_SERVER['HTTP_HOST'] ?? '',
      'games_count'  => count($games),
      'games'        => $games,
    ];

    header('Content-Type: application/json; charset=utf-8');
    header('Content-Disposition: attachment; filename="games-export-ALL-'.date('Ymd-His').'.json"');
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
    exit;
  } catch (Throwable $e) {
    $err = 'Export ALL failed: '.$e->getMessage();
  }
}

/* -------------------------------------------------
   BULK ACTIONS (selected rows)
------------------------------------------------- */
if ($_SERVER['REQUEST_METHOD']==='POST' && isset($_POST['bulk_action'])) {
  csrf_check();
  $ids = array_filter(array_map('intval', $_POST['ids'] ?? []));
  $act = $_POST['bulk_action'] ?? '';

  if ($ids) {
    $ph = implode(',', array_fill(0,count($ids),'?'));

    /* Export selected games as JSON for import on another installation */
    if ($act === 'export_json') {
      try {
        // Determine game columns (excluding id + category_id)
        $colStmt = $pdo->query("SHOW COLUMNS FROM games");
        $gameCols = [];
        while ($c = $colStmt->fetch(PDO::FETCH_ASSOC)) {
          $name = (string)$c['Field'];
          if ($name === 'id' || $name === 'category_id') {
            continue;
          }
          $gameCols[] = $name;
        }

        // Fetch selected games with category name
        $st = $pdo->prepare("
          SELECT g.*, c.name AS category_name
          FROM games g
          LEFT JOIN categories c ON c.id = g.category_id
          WHERE g.id IN ($ph)
        ");
        $st->execute($ids);
        $games = [];
        while ($row = $st->fetch(PDO::FETCH_ASSOC)) {
          $fields = [];
          foreach ($gameCols as $col) {
            if (array_key_exists($col, $row)) {
              $fields[$col] = $row[$col];
            }
          }
          $games[] = [
            'fields'        => $fields,
            'category_name' => $row['category_name'] ?? '',
          ];
        }

        $payload = [
          'export_type'  => 'games',
          'scope'        => 'selected',
          'exported_at'  => gmdate('c'),
          'source_host'  => $_SERVER['HTTP_HOST'] ?? '',
          'games_count'  => count($games),
          'games'        => $games,
        ];

        header('Content-Type: application/json; charset=utf-8');
        header('Content-Disposition: attachment; filename="games-export-'.date('Ymd-His').'.json"');
        echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
        exit;
      } catch (Throwable $e) {
        $err = 'Export failed: '.$e->getMessage();
      }
    }

    // Normal bulk actions
    try {
      if ($act==='publish') {
        $st=$pdo->prepare("UPDATE games SET is_published=1,updated_at=NOW() WHERE id IN ($ph)");
        $st->execute($ids); $msg="Published ".$st->rowCount()." game(s).";
      } elseif ($act==='unpublish') {
        $st=$pdo->prepare("UPDATE games SET is_published=0,updated_at=NOW() WHERE id IN ($ph)");
        $st->execute($ids); $msg="Unpublished ".$st->rowCount()." game(s).";
      } elseif ($act==='delete') {
        $st=$pdo->prepare("DELETE FROM games WHERE id IN ($ph)");
        $st->execute($ids); $msg="Deleted ".$st->rowCount()." game(s).";
      } elseif ($act==='feature_on' && $hasFeatured) {
        $st=$pdo->prepare("UPDATE games SET is_featured=1,updated_at=NOW() WHERE id IN ($ph)");
        $st->execute($ids); $msg="Set Featured on ".$st->rowCount()." game(s).";
      } elseif ($act==='feature_off' && $hasFeatured) {
        $st=$pdo->prepare("UPDATE games SET is_featured=0,updated_at=NOW() WHERE id IN ($ph)");
        $st->execute($ids); $msg="Removed Featured on ".$st->rowCount()." game(s).";
      } else {
        if ($act !== 'export_json') {
          $err='Unknown action or missing is_featured column.';
        }
      }
    } catch (Throwable $e) { $err=$e->getMessage(); }
  } else { $err='No rows selected.'; }
}

/* single-row feature toggle (legacy POST: feature_id + feature_set)
   Kept for backward-compatibility (non-JS). The UI below now uses AJAX. */
if ($_SERVER['REQUEST_METHOD']==='POST' && isset($_POST['feature_id']) && $hasFeatured) {
  csrf_check();
  $gid=(int)($_POST['feature_id']??0);
  $set=(int)($_POST['feature_set']??0);
  if ($gid>0) {
    $st=$pdo->prepare("UPDATE games SET is_featured=:s,updated_at=NOW() WHERE id=:id");
    $st->execute([':s'=>$set,':id'=>$gid]);
    $msg = $set? 'Marked as featured.' : 'Removed from featured.';
  }
}

/* FILTERS */
$q        = trim((string)($_GET['q'] ?? ''));
$cat      = (int)($_GET['cat'] ?? 0);
$prov     = trim((string)($_GET['provider'] ?? ''));
$status   = $_GET['status'] ?? 'any';       // any|1|0
$feat     = $_GET['feat'] ?? 'any';         // any|1|0
$sort     = $_GET['sort'] ?? 'new';         // new|name|popular|featured
$page     = max(1, (int)($_GET['page'] ?? 1));
$perPage  = min(100, max(10, (int)($_GET['per'] ?? 30)));

$where = ['1=1'];
$args  = [];

/* SAFE SEARCH (unique placeholders per column to avoid PDO duplicate-name issue) */
if ($q !== '') {
  $like = "%{$q}%";
  $searchCols = ['g.name','g.slug','g.description','g.external_id'];
  $ors = [];
  $i = 0;
  foreach ($searchCols as $col) {
    $i++;
    $ph = ":q{$i}";
    $ors[] = "$col LIKE $ph";
    $args[$ph] = $like;
  }
  $where[] = '(' . implode(' OR ', $ors) . ')';
}

if ($cat>0) { $where[]='g.category_id=:cat'; $args[':cat']=$cat; }
if ($prov!=='') { $where[]='g.provider=:prov'; $args[':prov']=$prov; }
if ($status==='1' || $status==='0') { $where[]='g.is_published=:st'; $args[':st']=(int)$status; }
if ($hasFeatured && ($feat==='1' || $feat==='0')) { $where[]='g.is_featured=:ft'; $args[':ft']=(int)$feat; }

$orderSql='g.created_at DESC';
switch ($sort) {
  case 'name':     $orderSql='g.name ASC'; break;
  case 'popular':  $orderSql='g.plays DESC, g.created_at DESC'; break;
  case 'featured': $orderSql= $hasFeatured? 'g.is_featured DESC, g.created_at DESC' : 'g.created_at DESC'; break;
  default:         $orderSql='g.created_at DESC';
}

$whereSql='WHERE '.implode(' AND ',$where);

/* COUNT */
$st=$pdo->prepare("SELECT COUNT(*) FROM games g $whereSql");
$st->execute($args); $total=(int)$st->fetchColumn();
$pages=max(1,(int)ceil($total/$perPage));
$offset=($page-1)*$perPage;

/* FETCH */
$selectFeatured = $hasFeatured ? ', g.is_featured' : '';
$sql="SELECT g.id,g.name,g.slug,g.provider,g.external_id,g.is_published$selectFeatured,
             g.thumb_url,g.category_id,c.name AS category_name,g.created_at,g.updated_at,g.plays
      FROM games g
      LEFT JOIN categories c ON c.id=g.category_id
      $whereSql
      ORDER BY $orderSql
      LIMIT :lim OFFSET :off";
$st=$pdo->prepare($sql);
foreach ($args as $k=>$v) $st->bindValue($k,$v);
$st->bindValue(':lim',$perPage,PDO::PARAM_INT);
$st->bindValue(':off',$offset,PDO::PARAM_INT);
$st->execute();
$rows=$st->fetchAll(PDO::FETCH_ASSOC);

/* FILTER DATA */
$cats=$pdo->query("SELECT id,name FROM categories ORDER BY name ASC")->fetchAll(PDO::FETCH_ASSOC);
$providers=$pdo->query("SELECT DISTINCT provider FROM games WHERE provider IS NOT NULL AND provider<>'' ORDER BY provider ASC")->fetchAll(PDO::FETCH_COLUMN);

require __DIR__ . '/../inc/header.php';
?>
<!-- Expose CSRF for AJAX -->
<meta name="csrf-token" content="<?= h($_SESSION['csrf'] ?? ($_SESSION['csrf_token'] ?? '')) ?>">

<section class="section"><div class="container">
  <div class="level">
    <div class="level-left"><h1 class="title">Manage Games</h1></div>
    <div class="level-right" style="gap:0.5rem; align-items:center;">
      <a class="button is-primary is-light" href="/admin/games/edit.php">Add Game</a>
      <a class="button is-link is-light" href="/admin/games/externalimport.php">External Import</a>
      <!-- Export ALL Games (separate POST form) -->
      <form method="post" style="margin-left:0.5rem;">
        <?php csrf_field(); ?>
        <input type="hidden" name="export_all" value="1">
        <button class="button is-success is-light" type="submit">
          Export ALL Games
        </button>
      </form>
    </div>
  </div>

  <?php if($featuredHint): ?>
    <article class="message is-warning">
      <div class="message-body"><?= nl2br(h($featuredHint)) ?></div>
    </article>
  <?php endif; ?>

  <?php if($msg): ?><div class="notification is-success"><?= h($msg) ?></div><?php endif; ?>
  <?php if($err): ?><div class="notification is-danger"><?= h($err) ?></div><?php endif; ?>

  <!-- Filters -->
  <form method="get" class="box" style="border-radius:12px;">
    <div class="columns is-multiline">
      <div class="column is-4">
        <label class="label">Search</label>
        <input class="input" name="q" value="<?= h($q) ?>" placeholder="Name, slug, external id…">
      </div>
      <div class="column is-3">
        <label class="label">Category</label>
        <div class="select is-fullwidth"><select name="cat">
          <option value="0">All</option>
          <?php foreach($cats as $c): ?>
            <option value="<?= (int)$c['id'] ?>" <?= $cat===(int)$c['id']?'selected':''; ?>><?= h($c['name']) ?></option>
          <?php endforeach; ?>
        </select></div>
      </div>
      <div class="column is-2">
        <label class="label">Provider</label>
        <div class="select is-fullwidth"><select name="provider">
          <option value="">All</option>
          <?php foreach($providers as $p): ?>
            <option value="<?= h($p) ?>" <?= $prov===$p?'selected':''; ?>><?= h($p) ?></option>
          <?php endforeach; ?>
        </select></div>
      </div>
      <div class="column is-1">
        <label class="label">Status</label>
        <div class="select is-fullwidth"><select name="status">
          <option value="any" <?= $status==='any'?'selected':''; ?>>Any</option>
          <option value="1"   <?= $status==='1'?'selected':''; ?>>Published</option>
          <option value="0"   <?= $status==='0'?'selected':''; ?>>Hidden</option>
        </select></div>
      </div>
      <div class="column is-1">
        <label class="label">Featured</label>
        <div class="select is-fullwidth"><select name="feat" <?= $hasFeatured?'':'disabled'; ?>>
          <option value="any" <?= $feat==='any'?'selected':''; ?>>Any</option>
          <option value="1"   <?= $feat==='1'?'selected':''; ?>>Yes</option>
          <option value="0"   <?= $feat==='0'?'selected':''; ?>>No</option>
        </select></div>
      </div>
      <div class="column is-1">
        <label class="label">Sort</label>
        <div class="select is-fullwidth"><select name="sort">
          <option value="new" <?= $sort==='new'?'selected':''; ?>>New</option>
          <option value="popular" <?= $sort==='popular'?'selected':''; ?>>Popular</option>
          <option value="name" <?= $sort==='name'?'selected':''; ?>>A–Z</option>
          <option value="featured" <?= $sort==='featured'?'selected':''; ?> <?= $hasFeatured?'':'disabled'; ?>>Featured</option>
        </select></div>
      </div>
      <div class="column is-1">
        <label class="label">Per</label>
        <div class="select is-fullwidth"><select name="per">
          <?php foreach([10,20,30,50,100] as $pp): ?>
            <option value="<?= $pp ?>" <?= $perPage===$pp?'selected':''; ?>><?= $pp ?></option>
          <?php endforeach; ?>
        </select></div>
      </div>
      <div class="column is-12">
        <button class="button is-primary">Apply</button>
        <a class="button is-light" href="/admin/games/manage.php">Reset</a>
        <span class="ml-3 has-text-grey">Found <strong><?= number_format($total) ?></strong> result(s)</span>
      </div>
    </div>
  </form>

  <!-- Bulk actions (selected rows) -->
  <form method="post" id="games-bulk-form">
    <?php csrf_field(); ?>
    <div class="level">
      <div class="level-left">
        <div class="field has-addons">
          <div class="control">
            <div class="select"><select name="bulk_action" required>
              <option value="">Bulk action…</option>
              <option value="publish">Publish</option>
              <option value="unpublish">Unpublish</option>
              <option value="delete">Delete</option>
              <?php if ($hasFeatured): ?>
                <option value="feature_on">Set Featured</option>
                <option value="feature_off">Remove Featured</option>
              <?php endif; ?>
              <option value="export_json">Export as JSON (selected)</option>
            </select></div>
          </div>
          <div class="control"><button class="button is-danger">Apply</button></div>
        </div>
      </div>
      <div class="level-right">
        <!-- You can keep this empty or add more controls if needed -->
      </div>
    </div>

    <div class="table-container">
      <table class="table is-striped is-hoverable is-fullwidth">
        <thead>
          <tr>
            <th><label class="checkbox"><input type="checkbox" id="check-all"></label></th>
            <th>Game</th>
            <th>Provider / Ext.ID</th>
            <th>Category</th>
            <?php if ($hasFeatured): ?><th>Featured</th><?php endif; ?>
            <th>Status</th>
            <th>Plays</th>
            <th>Created</th>
            <th style="width:160px;">Actions</th>
          </tr>
        </thead>
        <tbody>
          <?php if (!$rows): ?>
            <tr><td colspan="<?= $hasFeatured?9:8 ?>" class="has-text-centered has-text-grey">No games.</td></tr>
          <?php else: foreach ($rows as $r): ?>
            <tr>
              <td><label class="checkbox"><input type="checkbox" name="ids[]" value="<?= (int)$r['id'] ?>"></label></td>
              <td>
                <div style="display:flex; gap:10px; align-items:center;">
                  <img src="<?= h($r['thumb_url'] ?: '/assets/images/placeholder-thumb.jpg') ?>" alt="" style="width:56px;height:56px;object-fit:cover;border-radius:8px;">
                  <div>
                    <strong><?= h($r['name']) ?></strong><br>
                    <span class="is-size-7 has-text-grey"><?= h($r['slug']) ?></span>
                  </div>
                </div>
              </td>
              <td>
                <span class="tag is-light"><?= h($r['provider'] ?: '—') ?></span><br>
                <span class="is-size-7"><?= h($r['external_id'] ?: '—') ?></span>
              </td>
              <td><?= $r['category_name'] ? h($r['category_name']) : '—' ?></td>
              <?php if ($hasFeatured): ?>
                <td>
                  <?php $f = (int)($r['is_featured'] ?? 0); ?>
                  <button
                    class="button is-small js-toggle-feature <?= $f? 'is-warning is-light':'' ?>"
                    data-id="<?= (int)$r['id'] ?>"
                    data-set="<?= $f? 0:1 ?>"
                    title="<?= $f? 'Click to unfeature':'Click to feature' ?>"
                    type="button"
                  >
                    <?= $f? '★ Featured':'☆ Feature' ?>
                  </button>
                </td>
              <?php endif; ?>
              <td>
                <?php if ((int)$r['is_published']===1): ?>
                  <span class="tag is-success">Published</span>
                <?php else: ?>
                  <span class="tag is-warning">Hidden</span>
                <?php endif; ?>
              </td>
              <td><?= (int)$r['plays'] ?></td>
              <td class="is-size-7"><?= h($r['created_at']) ?></td>
              <td>
                <div class="buttons are-small">
                  <a class="button is-link is-light" href="/pages/game.php?slug=<?= urlencode($r['slug']) ?>" target="_blank">View</a>
                  <a class="button is-info is-light" href="/admin/games/edit.php?id=<?= (int)$r['id'] ?>">Edit</a>
                </div>
              </td>
            </tr>
          <?php endforeach; endif; ?>
        </tbody>
      </table>
    </div>
  </form>

  <!-- Pagination -->
  <?php if ($pages>1): ?>
    <nav class="pagination is-centered mt-4" role="navigation" aria-label="pagination">
      <?php
        $base=$_GET;
        $prev=$page>1?array_merge($base,['page'=>$page-1]):null;
        $next=$page<$pages?array_merge($base,['page'=>$page+1]):null;
        $mk=function($p) use($base){ $x=$base; $x['page']=$p; return '?'.http_build_query($x); };
      ?>
      <a class="pagination-previous" <?= $page<=1?'disabled':''; ?> href="<?= $prev?('?'.http_build_query($prev)):'#' ?>">Previous</a>
      <a class="pagination-next" <?= $page>>=
$pages?'disabled':''; ?> href="<?= $next?('?'.http_build_query($next)):'#' ?>">Next page</a>
      <ul class="pagination-list">
        <?php
          $win=2; $start=max(1,$page-$win); $end=min($pages,$page+$win);
          if ($start>1) { echo '<li><a class="pagination-link" href="'.$mk(1).'">1</a></li>'; if ($start>2) echo '<li><span class="pagination-ellipsis">&hellip;</span></li>'; }
          for($p=$start;$p<=$end;$p++){ $cur=$p===$page?' is-current':''; echo '<li><a class="pagination-link'.$cur.'" href="'.$mk($p).'">'.$p.'</a></li>'; }
          if ($end<$pages) { if ($end<$pages-1) echo '<li><span class="pagination-ellipsis">&hellip;</span></li>'; echo '<li><a class="pagination-link" href="'.$mk($pages).'">'.$pages.'</a></li>'; }
        ?>
      </ul>
    </nav>
  <?php endif; ?>
</div></section>

<?php require __DIR__ . '/../inc/footer.php'; ?>

<script>
// Check all
const chkAll=document.getElementById('check-all');
if(chkAll){
  chkAll.addEventListener('change',()=>{
    document.querySelectorAll('input[name="ids[]"]').forEach(cb=>cb.checked=chkAll.checked);
  });
}

// AJAX feature toggle
(function(){
  const token = document.querySelector('meta[name="csrf-token"]')?.getAttribute('content') || '';
  function setBtnLoading(btn, yes){
    btn.disabled = !!yes;
    if(yes){ btn.dataset._text = btn.textContent; btn.textContent = '…'; }
    else if(btn.dataset._text){ btn.textContent = btn.dataset._text; delete btn.dataset._text; }
  }
  async function toggleFeature(btn){
    const id  = btn.getAttribute('data-id');
    const set = btn.getAttribute('data-set');
    if(!id || (set!=='0' && set!=='1')) return;
    setBtnLoading(btn, true);
    try {
      const res = await fetch('/admin/games/feature_toggle.php', {
        method: 'POST',
        headers: {'Content-Type': 'application/x-www-form-urlencoded'},
        body: new URLSearchParams({ id, set, csrf: token })
      });
      const json = await res.json();
      if(!json.ok) throw new Error(json.error || 'Request failed');

      const featured = Number(json.is_featured) === 1;
      btn.classList.toggle('is-warning', featured);
      btn.classList.add('is-light');
      btn.textContent = featured ? '★ Featured' : '☆ Feature';
      btn.setAttribute('data-set', featured ? '0' : '1');
      btn.title = featured ? 'Click to unfeature' : 'Click to feature';
    } catch(err){
      alert('Failed: ' + err.message);
    } finally {
      setBtnLoading(btn, false);
    }
  }
  document.querySelectorAll('.js-toggle-feature').forEach(btn=>{
    btn.addEventListener('click', (e)=>{ e.preventDefault(); toggleFeature(btn); });
  });
})();
</script>
admin/games/manage.php

Comments

Embed Code
