<?php
require_once __DIR__ . '/../config/user_functions.php';
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/../config/users_local.php';

if (function_exists('user_has_permission') && !user_has_permission('settings')) {
    http_response_code(403);
    include '../includes/header.php';
    include '../includes/sidebar.php';
    echo "<div class='container-fluid py-4'><h3 class='text-danger'>Access denied</h3></div>";
    include '../includes/footer.php';
    exit;
}

require_once __DIR__ . '/../config/db_remote.php';

$table = $_GET['table'] ?? '';
if (!preg_match('/^[A-Za-z0-9_]+$/', $table)) {
    die("Invalid table name");
}

// confirm table exists
$chk = $mysqli->query("SHOW TABLES LIKE '{$table}'");
if (!$chk || $chk->num_rows === 0) {
    die("Table not found");
}

// load columns
$cols = [];
$res = $mysqli->query("SHOW COLUMNS FROM `{$table}`");
if ($res) {
    while ($c = $res->fetch_assoc()) {
        $cols[] = $c;
    }
}

$primaryKey = null;
foreach ($cols as $c) {
    if ($c['Key'] === 'PRI') {
        $primaryKey = $c['Field'];
        break;
    }
}
if (!$primaryKey && !empty($cols)) {
    $primaryKey = $cols[0]['Field'];
}

$message = "";

// ====== ACTIONS (POST) ======
if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    // bulk delete
    if (!empty($_POST['bulk_delete']) && !empty($_POST['selected']) && $primaryKey) {
        $ids = array_map('intval', $_POST['selected']);
        if (!empty($ids)) {
            $idList = implode(',', $ids);
            $mysqli->query("DELETE FROM `{$table}` WHERE `{$primaryKey}` IN ({$idList})");
            $message = "Deleted ".count($ids)." row(s).";
        }
    }

    // single delete
    if (!empty($_POST['delete_one']) && $primaryKey) {
        $id = (int)$_POST['delete_one'];
        $mysqli->query("DELETE FROM `{$table}` WHERE `{$primaryKey}` = {$id} LIMIT 1");
        $message = "Row deleted.";
    }

    // save edit
    if (!empty($_POST['save_row']) && $primaryKey) {
        $id = (int)$_POST['save_row'];
        $updateParts = [];
        $params = [];
        foreach ($cols as $c) {
            $f = $c['Field'];
            if ($f === $primaryKey) continue;
            if (array_key_exists("col_{$f}", $_POST)) {
                $val = $_POST["col_{$f}"];
                if ($val === '' && $c['Null'] === 'YES') {
                    $updateParts[] = "`{$f}` = NULL";
                } else {
                    $escaped = $mysqli->real_escape_string($val);
                    $updateParts[] = "`{$f}` = '{$escaped}'";
                }
            }
        }
        if (!empty($updateParts)) {
            $sql = "UPDATE `{$table}` SET ".implode(',', $updateParts)." WHERE `{$primaryKey}` = {$id} LIMIT 1";
            $mysqli->query($sql);
            $message = "Row updated.";
        }
    }

    // insert new row
    if (!empty($_POST['insert_row'])) {
        $fields = [];
        $values = [];
        foreach ($cols as $c) {
            $f = $c['Field'];
            // skip auto_increment PK if empty
            if ($f === $primaryKey && stripos($c['Extra'], 'auto_increment') !== false) {
                continue;
            }
            if (!array_key_exists("new_{$f}", $_POST)) continue;
            $val = $_POST["new_{$f}"];
            $fields[] = "`{$f}`";
            if ($val === '' && $c['Null'] === 'YES') {
                $values[] = "NULL";
            } else {
                $values[] = "'" . $mysqli->real_escape_string($val) . "'";
            }
        }
        if (!empty($fields)) {
            $sql = "INSERT INTO `{$table}` (".implode(',', $fields).") VALUES (".implode(',', $values).")";
            $mysqli->query($sql);
            $message = "New row inserted.";
        }
    }
}

// ====== FILTERS / PAGINATION ======
$page     = max(1, (int)($_GET['page'] ?? 1));
$pageSize = max(1, min(200, (int)($_GET['limit'] ?? 50)));
$offset   = ($page - 1) * $pageSize;

$search        = trim($_GET['search'] ?? '');
$filterColumn  = $_GET['filter_col'] ?? '';
$whereClauses  = [];

if ($search !== '') {
    $like = $mysqli->real_escape_string("%{$search}%");
    if ($filterColumn !== '' && preg_match('/^[A-Za-z0-9_]+$/', $filterColumn)) {
        $whereClauses[] = "`{$filterColumn}` LIKE '{$like}'";
    } else {
        $or = [];
        foreach ($cols as $c) {
            $f = $c['Field'];
            $or[] = "`{$f}` LIKE '{$like}'";
        }
        if (!empty($or)) {
            $whereClauses[] = '(' . implode(' OR ', $or) . ')';
        }
    }
}

$whereSql = '';
if (!empty($whereClauses)) {
    $whereSql = 'WHERE ' . implode(' AND ', $whereClauses);
}

// Count total
$totalRows = 0;
$countRes = $mysqli->query("SELECT COUNT(*) AS c FROM `{$table}` {$whereSql}");
if ($countRes) {
    $row = $countRes->fetch_assoc();
    $totalRows = (int)$row['c'];
}

// Load page data
$orderSql = $primaryKey ? "ORDER BY `{$primaryKey}` DESC" : "";
$sql = "SELECT * FROM `{$table}` {$whereSql} {$orderSql} LIMIT {$pageSize} OFFSET {$offset}";
$dataRes = $mysqli->query($sql);
$rows = [];
if ($dataRes) {
    while ($r = $dataRes->fetch_assoc()) {
        $rows[] = $r;
    }
}

// edit row load if requested
$editRow = null;
if (!empty($_GET['edit_id']) && $primaryKey) {
    $editId = (int)$_GET['edit_id'];
    $r = $mysqli->query("SELECT * FROM `{$table}` WHERE `{$primaryKey}` = {$editId} LIMIT 1");
    if ($r && $r->num_rows) {
        $editRow = $r->fetch_assoc();
    }
}

include '../includes/header.php';
include '../includes/sidebar.php';
?>

<div class="container-fluid py-4">
  <div class="d-flex justify-content-between align-items-center mb-3 flex-wrap gap-2">
    <div>
      <h3 class="mb-0">IBC Data Manager</h3>
      <div class="text-muted small">Table: <code><?= htmlspecialchars($table) ?></code></div>
    </div>
    <a href="ibc_manager.php" class="btn btn-outline-secondary btn-sm">← Back to tables</a>
  </div>

  <?php if ($message): ?>
    <div class="alert alert-success"><?= htmlspecialchars($message) ?></div>
  <?php endif; ?>

  <?php if ($editRow): ?>
    <div class="card mb-4 border-warning">
      <div class="card-header bg-warning-subtle">
        <strong>Edit row</strong> (<?= htmlspecialchars($primaryKey) ?> = <?= (int)$editRow[$primaryKey] ?>)
      </div>
      <div class="card-body">
        <form method="post" class="row g-3">
          <?php foreach ($cols as $c): 
            $f = $c['Field'];
            $val = $editRow[$f] ?? '';
          ?>
            <div class="col-md-4">
              <label class="form-label"><?= htmlspecialchars($f) ?></label>
              <?php if ($f === $primaryKey): ?>
                <input type="text" class="form-control" value="<?= htmlspecialchars($val) ?>" disabled>
              <?php else: ?>
                <input type="text" class="form-control"
                       name="col_<?= htmlspecialchars($f) ?>"
                       value="<?= htmlspecialchars($val) ?>">
              <?php endif; ?>
            </div>
          <?php endforeach; ?>
          <div class="col-12">
            <button class="btn btn-primary" name="save_row" value="<?= (int)$editRow[$primaryKey] ?>">
              Save changes
            </button>
            <a href="ibc_table_view.php?table=<?= urlencode($table) ?>" class="btn btn-secondary">
              Cancel
            </a>
          </div>
        </form>
      </div>
    </div>
  <?php endif; ?>

  <div class="card mb-4">
    <div class="card-header d-flex justify-content-between align-items-center">
      <span>Filters & insert</span>
    </div>
    <div class="card-body">
      <form class="row g-3 align-items-end mb-3" method="get">
        <input type="hidden" name="table" value="<?= htmlspecialchars($table) ?>">
        <div class="col-md-4">
          <label class="form-label">Search</label>
          <input type="text" name="search" class="form-control"
                 value="<?= htmlspecialchars($search) ?>" placeholder="Text search...">
        </div>
        <div class="col-md-3">
          <label class="form-label">Column</label>
          <select name="filter_col" class="form-select">
            <option value="">All columns</option>
            <?php foreach ($cols as $c): ?>
              <option value="<?= htmlspecialchars($c['Field']) ?>"
                <?= $filterColumn === $c['Field'] ? 'selected' : '' ?>>
                <?= htmlspecialchars($c['Field']) ?>
              </option>
            <?php endforeach; ?>
          </select>
        </div>
        <div class="col-md-2">
          <label class="form-label">Rows per page</label>
          <input type="number" name="limit" class="form-control"
                 value="<?= (int)$pageSize ?>" min="1" max="200">
        </div>
        <div class="col-md-3">
          <button class="btn btn-primary">Apply</button>
          <a href="ibc_table_view.php?table=<?= urlencode($table) ?>" class="btn btn-outline-secondary">
            Reset
          </a>
        </div>
      </form>

      <hr>

      <h6>Insert new row</h6>
      <form method="post" class="row g-2">
        <?php foreach ($cols as $c):
          $f = $c['Field'];
          $isPK = ($f === $primaryKey && stripos($c['Extra'], 'auto_increment') !== false);
        ?>
          <div class="col-md-3">
            <label class="form-label small">
              <?= htmlspecialchars($f) ?>
              <?php if ($isPK): ?><span class="text-muted">(auto)</span><?php endif; ?>
            </label>
            <input type="text" class="form-control"
                   name="new_<?= htmlspecialchars($f) ?>"
                   <?= $isPK ? 'placeholder="auto"' : '' ?>>
          </div>
        <?php endforeach; ?>
        <div class="col-12 mt-2">
          <button class="btn btn-success btn-sm" name="insert_row" value="1">Insert row</button>
        </div>
      </form>
    </div>
  </div>

  <form method="post" id="bulkForm">
    <div class="card">
      <div class="card-header d-flex justify-content-between align-items-center">
        <span>Rows (<?= (int)$totalRows ?>)</span>
        <div class="d-flex gap-2">
          <button class="btn btn-sm btn-outline-danger"
                  type="submit" name="bulk_delete" value="1"
                  onclick="return confirm('Delete selected rows? This cannot be undone.');">
            Delete selected
          </button>
          <span class="text-muted small">Page <?= (int)$page ?></span>
        </div>
      </div>
      <div class="card-body p-0">
        <div class="table-responsive">
          <table class="table table-sm table-striped mb-0 align-middle">
            <thead>
              <tr>
                <th style="width:30px;">
                  <input type="checkbox" id="chkAll">
                </th>
                <?php foreach ($cols as $c): ?>
                  <th><?= htmlspecialchars($c['Field']) ?></th>
                <?php endforeach; ?>
                <th style="width:120px;">Actions</th>
              </tr>
            </thead>
            <tbody>
              <?php if (empty($rows)): ?>
                <tr>
                  <td colspan="<?= count($cols) + 2 ?>" class="text-center text-muted py-3">
                    No data for this filter/page.
                  </td>
                </tr>
              <?php else: ?>
                <?php foreach ($rows as $r): ?>
                  <tr>
                    <td>
                      <?php if ($primaryKey): ?>
                        <input type="checkbox" name="selected[]"
                               value="<?= (int)$r[$primaryKey] ?>" class="chkRow">
                      <?php endif; ?>
                    </td>
                    <?php foreach ($cols as $c):
                      $f = $c['Field'];
                    ?>
                      <td style="max-width:200px; overflow:hidden; text-overflow:ellipsis; white-space:nowrap;">
                        <?= htmlspecialchars((string)($r[$f] ?? '')) ?>
                      </td>
                    <?php endforeach; ?>
                    <td>
                      <?php if ($primaryKey): ?>
                        <div class="btn-group btn-group-sm">
                          <a class="btn btn-outline-secondary"
                             href="ibc_table_view.php?table=<?= urlencode($table) ?>&edit_id=<?= (int)$r[$primaryKey] ?>">
                             Edit
                          </a>
                          <button class="btn btn-outline-danger" name="delete_one"
                                  value="<?= (int)$r[$primaryKey] ?>"
                                  onclick="return confirm('Delete this row?');">
                            Delete
                          </button>
                        </div>
                      <?php endif; ?>
                    </td>
                  </tr>
                <?php endforeach; ?>
              <?php endif; ?>
            </tbody>
          </table>
        </div>
      </div>
      <?php if ($totalRows > $pageSize): ?>
        <div class="card-footer d-flex justify-content-between align-items-center">
          <?php
            $totalPages = (int)ceil($totalRows / $pageSize);
          ?>
          <span class="small text-muted">
            Page <?= (int)$page ?> of <?= $totalPages ?>
          </span>
          <div class="btn-group btn-group-sm">
            <?php if ($page > 1): ?>
              <a class="btn btn-outline-secondary"
                 href="?table=<?= urlencode($table) ?>&page=<?= $page-1 ?>&limit=<?= $pageSize ?>&search=<?= urlencode($search) ?>&filter_col=<?= urlencode($filterColumn) ?>">
                ‹ Prev
              </a>
            <?php endif; ?>
            <?php if ($page < $totalPages): ?>
              <a class="btn btn-outline-secondary"
                 href="?table=<?= urlencode($table) ?>&page=<?= $page+1 ?>&limit=<?= $pageSize ?>&search=<?= urlencode($search) ?>&filter_col=<?= urlencode($filterColumn) ?>">
                Next ›
              </a>
            <?php endif; ?>
          </div>
        </div>
      <?php endif; ?>
    </div>
  </form>
</div>

<?php include '../includes/footer.php'; ?>

<script>
document.getElementById('chkAll')?.addEventListener('change', function () {
  const checked = this.checked;
  document.querySelectorAll('.chkRow').forEach(el => el.checked = checked);
});
</script>
