<?php
require_once __DIR__ . '/../config/user_functions.php';

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

// Need at least view permission
if (!(user_has_permission('ibc_tables') || user_has_permission('ibc_tables_view'))) {
    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_ibc.php';

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

// helpers
function ibc_get_tables($conn) {
    $tables = [];
    if ($res = $conn->query("SHOW TABLES")) {
        while ($row = $res->fetch_array()) {
            $tables[] = $row[0];
        }
    }
    return $tables;
}

function ibc_get_columns($conn, $table) {
    $cols = [];
    if ($res = $conn->query("DESCRIBE `$table`")) {
        while ($row = $res->fetch_assoc()) {
            $cols[] = $row;
        }
    }
    return $cols;
}

function ibc_get_primary_key($conn, $table) {
    $pk = [];
    if ($res = $conn->query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'")) {
        while ($row = $res->fetch_assoc()) {
            $pk[] = $row['Column_name'];
        }
    }
    return $pk; // may be empty or multi-column
}

$tables = ibc_get_tables($ibc_conn);
$selected = $_GET['table'] ?? '';
$selected = in_array($selected, $tables, true) ? $selected : '';

// pagination
$per_page = 100;
$page     = max(1, (int)($_GET['p'] ?? 1));
$offset   = ($page - 1) * $per_page;

// filters
$search = trim($_GET['search'] ?? '');
$filter_col = $_GET['filter_col'] ?? '';
$filter_val = trim($_GET['filter_val'] ?? '');

?>
<div class="container-fluid py-4">
  <h3 class="mb-4">IBC Tables (External DB)</h3>

  <form method="get" class="row g-3 mb-4">
    <div class="col-md-4">
      <label class="form-label">Select table</label>
      <select name="table" class="form-select" onchange="this.form.submit()">
        <option value="">-- choose table --</option>
        <?php foreach ($tables as $t): ?>
          <option value="<?= htmlspecialchars($t) ?>"
            <?= ($selected === $t) ? 'selected' : '' ?>>
            <?= htmlspecialchars($t) ?>
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <?php if ($selected): ?>
    <div class="col-md-4">
      <label class="form-label">Global search</label>
      <input type="text" name="search" class="form-control"
             value="<?= htmlspecialchars($search) ?>"
             placeholder="Search in any column">
    </div>
    <div class="col-md-2">
      <label class="form-label">Filter column</label>
      <select name="filter_col" class="form-select">
        <option value="">-- any --</option>
        <?php
        $cols = ibc_get_columns($ibc_conn, $selected);
        foreach ($cols as $c):
          $name = $c['Field'];
        ?>
          <option value="<?= htmlspecialchars($name) ?>"
            <?= ($filter_col === $name) ? 'selected' : '' ?>>
            <?= htmlspecialchars($name) ?>
          </option>
        <?php endforeach; ?>
      </select>
    </div>
    <div class="col-md-2">
      <label class="form-label">Filter value</label>
      <input type="text" name="filter_val" class="form-control"
             value="<?= htmlspecialchars($filter_val) ?>">
    </div>
    <div class="col-md-12">
      <button class="btn btn-outline-secondary mt-2">Apply</button>
    </div>
    <?php endif; ?>
  </form>

  <?php if ($selected): ?>
    <?php
    $cols = ibc_get_columns($ibc_conn, $selected);
    $pk_cols = ibc_get_primary_key($ibc_conn, $selected);

    // build WHERE
    $where = [];
    if ($search !== '') {
        $parts = [];
        foreach ($cols as $c) {
            $col = $c['Field'];
            $esc = $ibc_conn->real_escape_string($search);
            $parts[] = "`$col` LIKE '%$esc%'";
        }
        if ($parts) {
            $where[] = '(' . implode(' OR ', $parts) . ')';
        }
    }
    if ($filter_col !== '' && $filter_val !== '') {
        foreach ($cols as $c) {
            if ($c['Field'] === $filter_col) {
                $esc = $ibc_conn->real_escape_string($filter_val);
                $where[] = "`$filter_col` LIKE '%$esc%'";
                break;
            }
        }
    }
    $where_sql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    // count total
    $count = 0;
    if ($res = $ibc_conn->query("SELECT COUNT(*) AS c FROM `$selected` $where_sql")) {
        if ($row = $res->fetch_assoc()) {
            $count = (int)$row['c'];
        }
    }

    $total_pages = $count > 0 ? (int)ceil($count / $per_page) : 1;
    if ($page > $total_pages) $page = $total_pages;
    $offset = ($page - 1) * $per_page;

    $sql = "SELECT * FROM `$selected` $where_sql LIMIT $offset, $per_page";
    $res = $ibc_conn->query($sql);
    ?>

    <div class="d-flex justify-content-between align-items-center mb-2">
      <h5 class="mb-0">Table: <?= htmlspecialchars($selected) ?></h5>
      <div>
        <?php if (user_has_permission('ibc_tables') || user_has_permission('ibc_tables_edit')): ?>
          <a href="ibc_tables_edit.php?action=add&amp;table=<?= urlencode($selected) ?>"
             class="btn btn-sm btn-primary">Add row</a>
        <?php endif; ?>
      </div>
    </div>

    <p class="text-muted">
      Rows: <?= $count ?> |
      Page <?= $page ?> of <?= $total_pages ?>
    </p>

    <div class="card">
      <div class="card-body table-responsive">
        <?php if ($res && $res->num_rows > 0): ?>
          <table class="table table-striped table-bordered table-sm align-middle">
            <thead class="table-light">
              <tr>
                <?php foreach ($cols as $c): ?>
                  <th><?= htmlspecialchars($c['Field']) ?></th>
                <?php endforeach; ?>
                <?php if (user_has_permission('ibc_tables') || user_has_permission('ibc_tables_edit') || user_has_permission('ibc_tables_delete')): ?>
                  <th style="width:120px;">Actions</th>
                <?php endif; ?>
              </tr>
            </thead>
            <tbody>
              <?php while ($row = $res->fetch_assoc()): ?>
                <tr>
                  <?php foreach ($cols as $c):
                    $field = $c['Field'];
                  ?>
                    <td><?= htmlspecialchars((string)($row[$field] ?? '')) ?></td>
                  <?php endforeach; ?>

                  <?php if (user_has_permission('ibc_tables') || user_has_permission('ibc_tables_edit') || user_has_permission('ibc_tables_delete')): ?>
                    <td>
                      <div class="btn-group btn-group-sm" role="group">
                        <?php
                        $pk_query = '';
                        if ($pk_cols) {
                            $pairs = [];
                            foreach ($pk_cols as $pkc) {
                                $pairs[] = rawurlencode($pkc) . '=' . rawurlencode((string)($row[$pkc] ?? ''));
                            }
                            $pk_query = implode('&', $pairs);
                        }
                        ?>
                        <?php if ($pk_cols && (user_has_permission('ibc_tables') || user_has_permission('ibc_tables_edit'))): ?>
                          <a href="ibc_tables_edit.php?action=edit&amp;table=<?= urlencode($selected) ?>&amp;<?= $pk_query ?>"
                             class="btn btn-outline-secondary">Edit</a>
                        <?php endif; ?>
                        <?php if ($pk_cols && (user_has_permission('ibc_tables') || user_has_permission('ibc_tables_delete'))): ?>
                          <a href="ibc_tables_delete.php?table=<?= urlencode($selected) ?>&amp;<?= $pk_query ?>"
                             class="btn btn-outline-danger"
                             onclick="return confirm('Delete this row?');">Del</a>
                        <?php endif; ?>
                      </div>
                    </td>
                  <?php endif; ?>
                </tr>
              <?php endwhile; ?>
            </tbody>
          </table>

          <nav>
            <ul class="pagination pagination-sm">
              <?php
              $base = '?table=' . urlencode($selected)
                    . '&search=' . urlencode($search)
                    . '&filter_col=' . urlencode($filter_col)
                    . '&filter_val=' . urlencode($filter_val);
              ?>
              <li class="page-item <?= ($page <= 1) ? 'disabled' : '' ?>">
                <a class="page-link" href="<?= $page > 1 ? $base . '&p=' . ($page-1) : '#' ?>">« Prev</a>
              </li>
              <li class="page-item disabled">
                <span class="page-link"><?= $page ?> / <?= $total_pages ?></span>
              </li>
              <li class="page-item <?= ($page >= $total_pages) ? 'disabled' : '' ?>">
                <a class="page-link" href="<?= $page < $total_pages ? $base . '&p=' . ($page+1) : '#' ?>">Next »</a>
              </li>
            </ul>
          </nav>

        <?php else: ?>
          <p class="text-muted mb-0">No data found for this table.</p>
        <?php endif; ?>
      </div>
    </div>
  <?php endif; ?>
</div>
<?php include '../includes/footer.php'; ?>
