<?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'; // remote DB connection

// CSRF token
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}
$csrf_token = $_SESSION['csrf_token'];

/**
 * Helper: send JSON response and exit
 */
function json_response($data, int $status = 200) {
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($data);
    exit;
}

/**
 * Simple identifier validator (table/column names)
 */
function valid_identifier($name) {
    return (bool)preg_match('/^[A-Za-z0-9_]+$/', $name);
}

/**
 * Get primary key column name (first column with COLUMN_KEY = 'PRI')
 * Returns string or null
 */
function get_primary_key_column($conn, $table) {
    $sql = "
        SELECT COLUMN_NAME
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = ?
          AND COLUMN_KEY = 'PRI'
        ORDER BY ORDINAL_POSITION
        LIMIT 1
    ";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return null;
    }
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $res = $stmt->get_result();
    $pk = null;
    if ($row = $res->fetch_assoc()) {
        $pk = $row['COLUMN_NAME'] ?? null;
    }
    $stmt->close();
    return $pk ?: null;
}

/**
 * AJAX HANDLERS
 */
if (
    ($_SERVER['REQUEST_METHOD'] === 'POST' || $_SERVER['REQUEST_METHOD'] === 'GET') &&
    isset($_REQUEST['ajax']) && $_REQUEST['ajax'] === '1'
) {
    if (!function_exists('user_has_permission') || !user_has_permission('settings')) {
        json_response(['ok' => false, 'error' => 'Access denied'], 403);
    }

    if (empty($_REQUEST['csrf_token']) || !hash_equals($_SESSION['csrf_token'], $_REQUEST['csrf_token'])) {
        json_response(['ok' => false, 'error' => 'Invalid CSRF token'], 400);
    }

    $action = $_REQUEST['action'] ?? '';

    // Use $conn from db_remote.php
    global $conn;
    if (!($conn instanceof mysqli)) {
        json_response(['ok' => false, 'error' => 'Remote DB connection not available'], 500);
    }

    // ---------- COLUMNS (structure) ----------
    if ($action === 'get_columns') {
        $table = $_REQUEST['table'] ?? '';
        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }

        $sql = "
            SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT,
                   COLUMN_KEY, EXTRA, COLUMN_COMMENT
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = ?
            ORDER BY ORDINAL_POSITION
        ";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }
        $stmt->bind_param('s', $table);
        $stmt->execute();
        $res = $stmt->get_result();

        $cols = [];
        while ($row = $res->fetch_assoc()) {
            $cols[] = $row;
        }
        $stmt->close();

        $pk = get_primary_key_column($conn, $table);

        json_response([
            'ok' => true,
            'table' => $table,
            'primary_key' => $pk,
            'columns' => $cols,
        ]);
    }

    // ---------- DATA (rows) ----------
    if ($action === 'get_data') {
        $table = $_REQUEST['table'] ?? '';
        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }

        $page = isset($_REQUEST['page']) ? (int)$_REQUEST['page'] : 1;
        $page = max(1, $page);
        $page_size = isset($_REQUEST['page_size']) ? (int)$_REQUEST['page_size'] : 50;
        $page_size = max(1, min(200, $page_size));
        $offset = ($page - 1) * $page_size;

        $tableEsc = $conn->real_escape_string($table);

        // Count total rows
        $sqlCount = "SELECT COUNT(*) AS cnt FROM `{$tableEsc}`";
        $resCount = $conn->query($sqlCount);
        if (!$resCount) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }
        $rowCount = $resCount->fetch_assoc();
        $total_rows = (int)($rowCount['cnt'] ?? 0);
        $resCount->free();

        // Fetch data page
        $sqlData = "SELECT * FROM `{$tableEsc}` LIMIT ? OFFSET ?";
        $stmt = $conn->prepare($sqlData);
        if (!$stmt) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }
        $stmt->bind_param('ii', $page_size, $offset);
        $stmt->execute();
        $res = $stmt->get_result();

        $rows = [];
        while ($r = $res->fetch_assoc()) {
            $rows[] = $r;
        }
        $stmt->close();

        $pk = get_primary_key_column($conn, $table);
        $editable = $pk !== null;

        $total_pages = $page_size > 0 ? (int)ceil($total_rows / $page_size) : 1;
        if ($total_pages < 1) $total_pages = 1;

        json_response([
            'ok' => true,
            'table' => $table,
            'primary_key' => $pk,
            'editable' => $editable,
            'page' => $page,
            'page_size' => $page_size,
            'total_rows' => $total_rows,
            'total_pages' => $total_pages,
            'rows' => $rows
        ]);
    }

    if ($action === 'update_cell') {
        $table   = $_REQUEST['table'] ?? '';
        $column  = $_REQUEST['column'] ?? '';
        $pkValue = $_REQUEST['pk_value'] ?? '';

        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }
        if (!valid_identifier($column)) {
            json_response(['ok' => false, 'error' => 'Invalid column name'], 400);
        }

        $pk = get_primary_key_column($conn, $table);
        if (!$pk) {
            json_response(['ok' => false, 'error' => 'This table has no primary key. Editing is disabled.'], 400);
        }

        $newValue = $_REQUEST['value'] ?? '';

        $tableEsc = $conn->real_escape_string($table);
        $colEsc   = $conn->real_escape_string($column);
        $pkEsc    = $conn->real_escape_string($pk);

        $sql = "UPDATE `{$tableEsc}` SET `{$colEsc}` = ? WHERE `{$pkEsc}` = ? LIMIT 1";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }
        $stmt->bind_param('ss', $newValue, $pkValue);
        if (!$stmt->execute()) {
            $stmt->close();
            json_response(['ok' => false, 'error' => $stmt->error], 500);
        }
        $affected = $stmt->affected_rows;
        $stmt->close();

        json_response(['ok' => true, 'affected' => $affected]);
    }

    if ($action === 'delete_rows') {
        $table = $_REQUEST['table'] ?? '';
        $ids   = $_REQUEST['ids'] ?? [];

        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }
        if (!is_array($ids) || empty($ids)) {
            json_response(['ok' => false, 'error' => 'No rows selected'], 400);
        }

        $pk = get_primary_key_column($conn, $table);
        if (!$pk) {
            json_response(['ok' => false, 'error' => 'This table has no primary key. Deleting rows is disabled.'], 400);
        }

        $tableEsc = $conn->real_escape_string($table);
        $pkEsc    = $conn->real_escape_string($pk);

        $sql = "DELETE FROM `{$tableEsc}` WHERE `{$pkEsc}` = ? LIMIT 1";
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }

        $deleted = 0;
        foreach ($ids as $id) {
            $id = (string)$id;
            $stmt->bind_param('s', $id);
            if ($stmt->execute()) {
                $deleted += $stmt->affected_rows;
            }
        }
        $stmt->close();

        json_response(['ok' => true, 'deleted' => $deleted]);
    }

    // ---------- TABLES (drop/rename) ----------
    if ($action === 'drop_tables') {
        $tables = $_REQUEST['tables'] ?? [];
        if (!is_array($tables) || empty($tables)) {
            json_response(['ok' => false, 'error' => 'No tables selected'], 400);
        }

        $ok = true;
        $errors = [];
        foreach ($tables as $t) {
            $t = trim($t);
            if (!valid_identifier($t)) {
                $errors[] = "Invalid table name: $t";
                $ok = false;
                continue;
            }
            $sql = "DROP TABLE IF EXISTS `" . $conn->real_escape_string($t) . "`";
            if (!$conn->query($sql)) {
                $ok = false;
                $errors[] = "Error dropping $t: " . $conn->error;
            }
        }

        json_response([
            'ok' => $ok,
            'errors' => $errors,
        ], $ok ? 200 : 500);
    }

    if ($action === 'drop_columns') {
        $table   = $_REQUEST['table'] ?? '';
        $columns = $_REQUEST['columns'] ?? [];

        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }
        if (!is_array($columns) || empty($columns)) {
            json_response(['ok' => false, 'error' => 'No columns selected'], 400);
        }

        $parts = [];
        foreach ($columns as $col) {
            $col = trim($col);
            if (!valid_identifier($col)) {
                json_response(['ok' => false, 'error' => "Invalid column name: $col"], 400);
            }
            $parts[] = "DROP COLUMN `" . $conn->real_escape_string($col) . "`";
        }

        $sql = "ALTER TABLE `" . $conn->real_escape_string($table) . "` " . implode(', ', $parts);
        if (!$conn->query($sql)) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }

        json_response(['ok' => true]);
    }

    if ($action === 'add_column') {
        $table    = $_REQUEST['table'] ?? '';
        $name     = trim($_REQUEST['name'] ?? '');
        $type     = trim($_REQUEST['type'] ?? '');
        $nullable = ($_REQUEST['nullable'] ?? '0') === '1';
        $default  = $_REQUEST['default'] ?? '';
        $hasDefault = ($_REQUEST['has_default'] ?? '0') === '1';

        if (!valid_identifier($table)) {
            json_response(['ok' => false, 'error' => 'Invalid table name'], 400);
        }
        if (!valid_identifier($name)) {
            json_response(['ok' => false, 'error' => 'Invalid column name'], 400);
        }
        if ($type === '') {
            json_response(['ok' => false, 'error' => 'Column type is required'], 400);
        }

        $sql = "ALTER TABLE `" . $conn->real_escape_string($table) . "` ADD COLUMN `" . $conn->real_escape_string($name) . "` " . $type;
        $sql .= $nullable ? " NULL" : " NOT NULL";

        if ($hasDefault) {
            if (strcasecmp($default, 'NULL') === 0 && $nullable) {
                $sql .= " DEFAULT NULL";
            } elseif ($default !== '') {
                $sql .= " DEFAULT '" . $conn->real_escape_string($default) . "'";
            }
        }

        if (!$conn->query($sql)) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }

        json_response(['ok' => true]);
    }

    if ($action === 'rename_table') {
        $old = $_REQUEST['old_name'] ?? '';
        $new = $_REQUEST['new_name'] ?? '';

        if (!valid_identifier($old) || !valid_identifier($new)) {
            json_response(['ok' => false, 'error' => 'Invalid table name(s)'], 400);
        }

        $sql = "RENAME TABLE `" . $conn->real_escape_string($old) . "` TO `" . $conn->real_escape_string($new) . "`";
        if (!$conn->query($sql)) {
            json_response(['ok' => false, 'error' => $conn->error], 500);
        }

        json_response(['ok' => true]);
    }

    // Unsupported action
    json_response(['ok' => false, 'error' => 'Unknown action'], 400);
}

// --------------------------------------------------
// NORMAL PAGE RENDER
// --------------------------------------------------
$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$current_table = isset($_GET['table']) ? trim($_GET['table']) : '';

// Fetch tables from remote DB
$tables = [];
if ($conn instanceof mysqli) {
    $sql = "
        SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLATION
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
    ";
    if ($search !== '') {
        $esc = '%' . $conn->real_escape_string($search) . '%';
        $sql .= " AND TABLE_NAME LIKE '{$esc}'";
    }
    $sql .= " ORDER BY TABLE_NAME ASC";

    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $row['SIZE_MB'] = ($row['DATA_LENGTH'] + $row['INDEX_LENGTH']) / (1024 * 1024);
            $tables[] = $row;
        }
        $res->free();
    }
}

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

<div class="container-fluid py-4" id="ibc-manager-app" data-csrf="<?= htmlspecialchars($csrf_token) ?>">
  <div class="row mb-4">
    <div class="col-12 d-flex justify-content-between align-items-center">
      <h3 class="mb-0">IBC Manager – Remote MySQL</h3>
      <form class="d-flex" method="get">
        <input type="text"
               name="search"
               class="form-control form-control-sm me-2"
               placeholder="Search table..."
               value="<?= htmlspecialchars($search) ?>">
        <button type="submit" class="btn btn-sm btn-primary">Search</button>
      </form>
    </div>
  </div>

  <div class="row">
    <!-- TABLES LIST -->
    <div class="col-lg-4 mb-4">
      <div class="card shadow-sm border-0 h-100">
        <div class="card-header d-flex justify-content-between align-items-center">
          <h6 class="mb-0">Tables</h6>
          <div>
            <button type="button" class="btn btn-sm btn-danger" id="btn-drop-selected-tables" disabled>
              Delete selected
            </button>
          </div>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table align-items-center mb-0 table-sm">
              <thead class="thead-light">
                <tr>
                  <th style="width:32px;">
                    <input type="checkbox" id="check-all-tables">
                  </th>
                  <th>Table</th>
                  <th class="text-end">Rows</th>
                  <th class="text-end">Size MB</th>
                </tr>
              </thead>
              <tbody>
                <?php if (empty($tables)): ?>
                  <tr>
                    <td colspan="4" class="text-center text-muted py-3">No tables found.</td>
                  </tr>
                <?php else: ?>
                  <?php foreach ($tables as $t): ?>
                    <?php
                      $tname = $t['TABLE_NAME'];
                      $isActive = ($tname === $current_table);
                    ?>
                    <tr data-table-name="<?= htmlspecialchars($tname) ?>" class="<?= $isActive ? 'table-active' : '' ?>">
                      <td>
                        <input type="checkbox" class="table-checkbox" value="<?= htmlspecialchars($tname) ?>">
                      </td>
                      <td>
                        <button type="button"
                                class="btn btn-link btn-sm p-0 text-decoration-none text-start btn-open-table">
                          <?= htmlspecialchars($tname) ?>
                        </button>
                        <div class="small text-muted">
                          <?= htmlspecialchars($t['ENGINE'] ?? '') ?> • <?= htmlspecialchars($t['TABLE_COLLATION'] ?? '') ?>
                        </div>
                      </td>
                      <td class="text-end">
                        <?= is_null($t['TABLE_ROWS']) ? '-' : number_format((int)$t['TABLE_ROWS']) ?>
                      </td>
                      <td class="text-end">
                        <?= number_format($t['SIZE_MB'], 2) ?>
                      </td>
                    </tr>
                  <?php endforeach; ?>
                <?php endif; ?>
              </tbody>
            </table>
          </div>
        </div>
        <div class="card-footer small text-muted">
          Remote DB connection: <?= htmlspecialchars($conn->host_info ?? 'remote') ?><br>
          <span class="text-muted">Click on a table to see structure and data.</span>
        </div>
      </div>
    </div>

    <!-- STRUCTURE + DATA -->
    <div class="col-lg-8 mb-4">
      <!-- COLUMNS (STRUCTURE) -->
      <div class="card shadow-sm border-0 mb-4" id="columns-card">
        <div class="card-header d-flex justify-content-between align-items-center">
          <div>
            <h6 class="mb-0">
              Structure –
              <span id="columns-table-name" class="text-primary">
                <?= $current_table ? htmlspecialchars($current_table) : 'no table selected' ?>
              </span>
            </h6>
            <small class="text-muted" id="columns-subtitle">
              <?= $current_table ? 'Loading columns...' : 'Select a table on the left to view its structure.' ?>
            </small>
          </div>
          <div>
            <button type="button" class="btn btn-sm btn-success" id="btn-add-column" disabled>
              Add column
            </button>
            <button type="button" class="btn btn-sm btn-danger" id="btn-drop-selected-columns" disabled>
              Delete selected
            </button>
          </div>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table align-items-center mb-0 table-sm" id="columns-table" style="display:none;">
              <thead class="thead-light">
                <tr>
                  <th style="width:32px;">
                    <input type="checkbox" id="check-all-columns">
                  </th>
                  <th>Name</th>
                  <th>Type</th>
                  <th>Null</th>
                  <th>Default</th>
                  <th>Key</th>
                  <th>Extra</th>
                  <th>Comment</th>
                </tr>
              </thead>
              <tbody id="columns-tbody">
              </tbody>
            </table>
          </div>
          <div id="columns-empty" class="p-3 text-muted small">
            Select a table to view its columns.
          </div>
        </div>
      </div>

      <!-- DATA (ROWS) -->
      <div class="card shadow-sm border-0" id="data-card">
        <div class="card-header d-flex justify-content-between align-items-center">
          <div>
            <h6 class="mb-0">
              Data –
              <span id="data-table-name" class="text-primary">
                <?= $current_table ? htmlspecialchars($current_table) : 'no table selected' ?>
              </span>
            </h6>
            <small class="text-muted" id="data-subtitle">
              <?= $current_table ? 'Loading data...' : 'Select a table to view its data.' ?>
            </small>
            <div id="data-edit-warning" class="small text-warning mt-1" style="display:none;">
              This table has no primary key. Editing and deleting rows are disabled.
            </div>
          </div>
          <div class="d-flex align-items-center gap-2">
            <div class="small text-muted me-3" id="data-page-info" style="min-width:150px; text-align:right;">
              <!-- Page info -->
            </div>
            <button type="button" class="btn btn-sm btn-outline-secondary" id="btn-data-prev" disabled>
              « Prev
            </button>
            <button type="button" class="btn btn-sm btn-outline-secondary" id="btn-data-next" disabled>
              Next »
            </button>
          </div>
        </div>
        <div class="card-body p-0">
          <div class="table-responsive">
            <table class="table align-items-center mb-0 table-sm" id="data-table" style="display:none;">
              <thead class="thead-light">
                <tr id="data-header-row">
                  <!-- Filled by JS -->
                </tr>
              </thead>
              <tbody id="data-tbody">
                <!-- Filled by JS -->
              </tbody>
            </table>
          </div>
          <div id="data-empty" class="p-3 text-muted small">
            Select a table to view its data.
          </div>
        </div>
        <div class="card-footer d-flex justify-content-between align-items-center">
          <div class="small text-muted">
            Click on a cell to edit. Changes are saved immediately.
          </div>
          <div>
            <button type="button" class="btn btn-sm btn-danger" id="btn-delete-selected-rows" disabled>
              Delete selected rows
            </button>
          </div>
        </div>
      </div>

    </div>
  </div>
</div>

<script>
(function() {
  const root        = document.getElementById('ibc-manager-app');
  if (!root) return;
  const csrfToken   = root.dataset.csrf || '';
  let currentTable  = <?= $current_table ? json_encode($current_table) : 'null' ?>;
  let currentPk     = null;

  // Data pagination state
  let dataPage      = 1;
  let dataTotalPages = 1;
  let dataEditable  = false;

  const tableCheckboxes       = () => Array.from(document.querySelectorAll('.table-checkbox'));
  const btnDropSelectedTables = document.getElementById('btn-drop-selected-tables');
  const btnDropSelectedCols   = document.getElementById('btn-drop-selected-columns');
  const btnAddColumn          = document.getElementById('btn-add-column');

  const columnsTable      = document.getElementById('columns-table');
  const columnsTbody      = document.getElementById('columns-tbody');
  const columnsEmpty      = document.getElementById('columns-empty');
  const columnsTableName  = document.getElementById('columns-table-name');
  const columnsSubtitle   = document.getElementById('columns-subtitle');
  const checkAllTables    = document.getElementById('check-all-tables');
  const checkAllColumns   = document.getElementById('check-all-columns');

  // Data DOM elements
  const dataTable         = document.getElementById('data-table');
  const dataHeaderRow     = document.getElementById('data-header-row');
  const dataTbody         = document.getElementById('data-tbody');
  const dataEmpty         = document.getElementById('data-empty');
  const dataTableName     = document.getElementById('data-table-name');
  const dataSubtitle      = document.getElementById('data-subtitle');
  const dataPageInfo      = document.getElementById('data-page-info');
  const dataEditWarning   = document.getElementById('data-edit-warning');
  const btnDataPrev       = document.getElementById('btn-data-prev');
  const btnDataNext       = document.getElementById('btn-data-next');
  const btnDeleteSelectedRows = document.getElementById('btn-delete-selected-rows');

  function setLoadingColumns(isLoading) {
    if (!columnsSubtitle) return;
    if (!currentTable) {
      columnsSubtitle.textContent = 'Select a table on the left to view its structure.';
    } else if (isLoading) {
      columnsSubtitle.textContent = 'Loading columns for ' + currentTable + '...';
    } else {
      columnsSubtitle.textContent = 'Columns of ' + currentTable;
    }
  }

  function setLoadingData(isLoading) {
    if (!dataSubtitle) return;
    if (!currentTable) {
      dataSubtitle.textContent = 'Select a table to view its data.';
    } else if (isLoading) {
      dataSubtitle.textContent = 'Loading data for ' + currentTable + '...';
    } else {
      dataSubtitle.textContent = 'Showing data of ' + currentTable;
    }
  }

  async function apiPost(action, payload = {}) {
    const formData = new FormData();
    formData.append('ajax', '1');
    formData.append('action', action);
    formData.append('csrf_token', csrfToken);
    for (const [k, v] of Object.entries(payload)) {
      if (Array.isArray(v)) {
        v.forEach(item => formData.append(k + '[]', item));
      } else {
        formData.append(k, v);
      }
    }

    const res = await fetch(window.location.href.split('?')[0], {
      method: 'POST',
      body: formData
    });
    let json;
    try {
      json = await res.json();
    } catch (e) {
      throw new Error('Invalid JSON response');
    }
    if (!res.ok || !json.ok) {
      throw new Error(json && json.error ? json.error : 'Request failed');
    }
    return json;
  }

  async function apiGet(action, params = {}) {
    const url = new URL(window.location.href.split('?')[0], window.location.origin);
    url.searchParams.set('ajax', '1');
    url.searchParams.set('action', action);
    url.searchParams.set('csrf_token', csrfToken);
    for (const [k, v] of Object.entries(params)) {
      url.searchParams.set(k, v);
    }
    const res = await fetch(url.toString(), { method: 'GET' });
    const json = await res.json();
    if (!res.ok || !json.ok) {
      throw new Error(json && json.error ? json.error : 'Request failed');
    }
    return json;
  }

  // -------- TABLES --------
  function refreshTableButtons() {
    const selected = tableCheckboxes().filter(ch => ch.checked).length;
    if (btnDropSelectedTables) btnDropSelectedTables.disabled = selected === 0;
  }

  if (checkAllTables) {
    checkAllTables.addEventListener('change', () => {
      const checked = checkAllTables.checked;
      tableCheckboxes().forEach(ch => { ch.checked = checked; });
      refreshTableButtons();
    });
  }

  document.addEventListener('change', (e) => {
    if (e.target.classList.contains('table-checkbox')) {
      refreshTableButtons();
    }
    if (e.target.classList.contains('column-checkbox')) {
      refreshColumnButtons();
    }
    if (e.target.classList.contains('data-row-checkbox')) {
      refreshDataButtons();
    }
  });

  document.addEventListener('click', async (e) => {
    const row = e.target.closest('tr[data-table-name]');

    // OPEN TABLE (STRUCTURE + DATA)
    if (e.target.classList.contains('btn-open-table') || (row && e.target.closest('.btn-open-table'))) {
      const tableName = row ? row.dataset.tableName : null;
      if (!tableName) return;
      e.preventDefault();
      setActiveTableRow(tableName);
      await loadColumns(tableName);
      dataPage = 1;
      await loadData(tableName, dataPage);
    }

    // DROP SELECTED TABLES
    if (e.target === btnDropSelectedTables) {
      const selected = tableCheckboxes().filter(ch => ch.checked).map(ch => ch.value);
      if (!selected.length) return;
      if (!confirm('Delete ' + selected.length + ' selected table(s)? This cannot be undone.')) {
        return;
      }
      try {
        await apiPost('drop_tables', { tables: selected });
        // remove rows
        selected.forEach(name => {
          const row = document.querySelector('tr[data-table-name="' + name + '"]');
          if (row) row.remove();
        });
        refreshTableButtons();
        if (currentTable && selected.includes(currentTable)) {
          currentTable = null;
          clearColumns();
          clearData();
        }
        alert('Selected tables have been deleted.');
      } catch (err) {
        alert('Error: ' + err.message);
      }
    }

    // DROP SELECTED COLUMNS
    if (e.target === btnDropSelectedCols) {
      const selectedCols = Array.from(document.querySelectorAll('.column-checkbox:checked')).map(ch => ch.value);
      if (!currentTable || !selectedCols.length) return;
      if (!confirm('Delete ' + selectedCols.length + ' column(s) from table "' + currentTable + '"?')) {
        return;
      }
      try {
        await apiPost('drop_columns', {
          table: currentTable,
          columns: selectedCols
        });
        await loadColumns(currentTable);
        alert('Selected columns have been deleted.');
      } catch (err) {
        alert('Error: ' + err.message);
      }
    }

    // ADD COLUMN
    if (e.target === btnAddColumn) {
      if (!currentTable) return;
      const name   = prompt('New column name:');
      if (!name) return;
      const type   = prompt('Column type (e.g. VARCHAR(255), INT(11), DATETIME):', 'VARCHAR(255)');
      if (!type) return;
      const nullable = confirm('Allow NULL for this column?');
      const useDefault = confirm('Specify a DEFAULT value? (OK = yes, Cancel = no)');
      let def = '';
      if (useDefault) {
        def = prompt('Default value (use NULL for SQL NULL):', '');
        if (def === null) return; // cancel
      }

      try {
        await apiPost('add_column', {
          table: currentTable,
          name: name,
          type: type,
          nullable: nullable ? '1' : '0',
          has_default: useDefault ? '1' : '0',
          default: def
        });
        await loadColumns(currentTable);
        alert('Column has been added.');
      } catch (err) {
        alert('Error: ' + err.message);
      }
    }

    // DATA: PAGINATION
    if (e.target === btnDataPrev) {
      if (!currentTable || dataPage <= 1) return;
      dataPage--;
      await loadData(currentTable, dataPage);
    }
    if (e.target === btnDataNext) {
      if (!currentTable || dataPage >= dataTotalPages) return;
      dataPage++;
      await loadData(currentTable, dataPage);
    }

    // DATA: DELETE SELECTED ROWS
    if (e.target === btnDeleteSelectedRows) {
      if (!currentTable || !dataEditable) return;
      const selected = Array.from(document.querySelectorAll('.data-row-checkbox:checked'));
      if (!selected.length) return;
      if (!confirm('Delete ' + selected.length + ' selected row(s)? This cannot be undone.')) {
        return;
      }
      const ids = selected.map(ch => ch.dataset.pkValue);
      try {
        await apiPost('delete_rows', {
          table: currentTable,
          ids: ids
        });
        await loadData(currentTable, dataPage);
        alert('Selected rows have been deleted.');
      } catch (err) {
        alert('Error: ' + err.message);
      }
    }
  });

  // INLINE EDITING FOR DATA CELLS
  dataTbody.addEventListener('click', async (e) => {
    const td = e.target.closest('td');
    if (!td || !dataEditable) return;
    if (td.dataset.columnName === undefined) return; // not a data cell
    if (td.classList.contains('data-cell-editing')) return;

    const tr = td.closest('tr');
    const pkValue = tr ? tr.dataset.pkValue : null;
    const columnName = td.dataset.columnName;
    if (!pkValue || !columnName) return;

    const originalText = td.textContent;
    td.classList.add('data-cell-editing');
    td.innerHTML = '';
    const input = document.createElement('input');
    input.type = 'text';
    input.className = 'form-control form-control-sm';
    input.value = originalText;
    td.appendChild(input);
    input.focus();
    input.select();

    let finished = false;

    function cancelEdit() {
      if (finished) return;
      finished = true;
      td.classList.remove('data-cell-editing');
      td.innerHTML = originalText;
    }

    async function saveEdit() {
      if (finished) return;
      finished = true;
      const newValue = input.value;
      td.classList.remove('data-cell-editing');
      td.textContent = newValue;
      if (newValue === originalText) {
        return;
      }
      try {
        await apiPost('update_cell', {
          table: currentTable,
          column: columnName,
          pk_value: pkValue,
          value: newValue
        });
      } catch (err) {
        alert('Error saving value: ' + err.message);
        td.textContent = originalText;
      }
    }

    input.addEventListener('blur', () => {
      saveEdit();
    });

    input.addEventListener('keydown', (ev) => {
      if (ev.key === 'Enter') {
        ev.preventDefault();
        saveEdit();
      } else if (ev.key === 'Escape') {
        ev.preventDefault();
        cancelEdit();
      }
    });
  });

  function setActiveTableRow(tableName) {
    currentTable = tableName;
    document.querySelectorAll('tr[data-table-name]').forEach(tr => {
      if (tr.dataset.tableName === tableName) {
        tr.classList.add('table-active');
      } else {
        tr.classList.remove('table-active');
      }
    });
    if (columnsTableName) columnsTableName.textContent = tableName;
    if (dataTableName) dataTableName.textContent = tableName;
  }

  function clearColumns() {
    if (columnsTbody) columnsTbody.innerHTML = '';
    if (columnsTable) columnsTable.style.display = 'none';
    if (columnsEmpty) columnsEmpty.style.display = 'block';
    if (btnDropSelectedCols) btnDropSelectedCols.disabled = true;
    if (btnAddColumn) btnAddColumn.disabled = !currentTable;
    if (columnsTableName) columnsTableName.textContent = currentTable || 'no table selected';
    setLoadingColumns(false);
  }

  function refreshColumnButtons() {
    const selected = Array.from(document.querySelectorAll('.column-checkbox:checked')).length;
    if (btnDropSelectedCols) btnDropSelectedCols.disabled = selected === 0;
  }

  function refreshDataButtons() {
    const selected = Array.from(document.querySelectorAll('.data-row-checkbox:checked')).length;
    if (btnDeleteSelectedRows) {
      btnDeleteSelectedRows.disabled = !dataEditable || selected === 0;
    }
  }

  function clearData() {
    dataPage = 1;
    dataTotalPages = 1;
    dataEditable = false;
    currentPk = null;
    if (dataTbody) dataTbody.innerHTML = '';
    if (dataHeaderRow) dataHeaderRow.innerHTML = '';
    if (dataTable) dataTable.style.display = 'none';
    if (dataEmpty) {
      dataEmpty.style.display = 'block';
      dataEmpty.textContent = 'Select a table to view its data.';
    }
    if (dataSubtitle) {
      dataSubtitle.textContent = currentTable ? 'No data.' : 'Select a table to view its data.';
    }
    if (dataPageInfo) dataPageInfo.textContent = '';
    if (btnDataPrev) btnDataPrev.disabled = true;
    if (btnDataNext) btnDataNext.disabled = true;
    if (btnDeleteSelectedRows) btnDeleteSelectedRows.disabled = true;
    if (dataEditWarning) dataEditWarning.style.display = 'none';
  }

  if (checkAllColumns) {
    checkAllColumns.addEventListener('change', () => {
      const checked = checkAllColumns.checked;
      document.querySelectorAll('.column-checkbox').forEach(ch => {
        ch.checked = checked;
      });
      refreshColumnButtons();
    });
  }

  async function loadColumns(tableName) {
    if (!tableName) return;
    currentTable = tableName;
    if (btnAddColumn) btnAddColumn.disabled = false;
    setLoadingColumns(true);
    try {
      const data = await apiGet('get_columns', { table: tableName });
      columnsTbody.innerHTML = '';
      const cols = data.columns || [];
      currentPk = data.primary_key || null;

      if (!cols.length) {
        columnsTable.style.display = 'none';
        columnsEmpty.style.display = 'block';
        columnsEmpty.textContent = 'No columns found for table "' + tableName + '".';
        return;
      }
      cols.forEach(col => {
        const tr = document.createElement('tr');
        tr.innerHTML = `
          <td>
            <input type="checkbox" class="column-checkbox" value="${col.COLUMN_NAME}">
          </td>
          <td>
            <code>${col.COLUMN_NAME}</code>
          </td>
          <td>${col.COLUMN_TYPE}</td>
          <td>${col.IS_NULLABLE}</td>
          <td>${col.COLUMN_DEFAULT === null ? '<span class="text-muted">NULL</span>' : col.COLUMN_DEFAULT}</td>
          <td>${col.COLUMN_KEY}</td>
          <td>${col.EXTRA}</td>
          <td>${col.COLUMN_COMMENT || ''}</td>
        `;
        columnsTbody.appendChild(tr);
      });
      columnsTable.style.display = '';
      columnsEmpty.style.display = 'none';
      if (checkAllColumns) {
        checkAllColumns.checked = false;
      }
      refreshColumnButtons();
    } catch (err) {
      columnsTable.style.display = 'none';
      columnsEmpty.style.display = 'block';
      columnsEmpty.textContent = 'Error loading columns: ' + err.message;
    } finally {
      setLoadingColumns(false);
    }
  }

  async function loadData(tableName, page) {
    if (!tableName) return;
    currentTable = tableName;
    if (dataTableName) dataTableName.textContent = tableName;
    setLoadingData(true);
    try {
      const data = await apiGet('get_data', { table: tableName, page: page });
      const rows = data.rows || [];
      currentPk = data.primary_key || null;
      dataEditable = !!data.editable;
      dataPage = data.page || 1;
      dataTotalPages = data.total_pages || 1;

      // Update header info
      if (dataPageInfo) {
        dataPageInfo.textContent = 'Page ' + dataPage + ' of ' + dataTotalPages +
          ' • Rows: ' + (data.total_rows || 0);
      }
      if (btnDataPrev) btnDataPrev.disabled = dataPage <= 1;
      if (btnDataNext) btnDataNext.disabled = dataPage >= dataTotalPages;

      // Warning if not editable
      if (dataEditWarning) {
        if (!dataEditable) {
          dataEditWarning.style.display = 'block';
        } else {
          dataEditWarning.style.display = 'none';
        }
      }

      // Build table header based on first row keys
      dataHeaderRow.innerHTML = '';
      dataTbody.innerHTML = '';

      if (!rows.length) {
        dataTable.style.display = 'none';
        dataEmpty.style.display = 'block';
        dataEmpty.textContent = 'No data found for this table (page ' + dataPage + ').';
        if (dataSubtitle) dataSubtitle.textContent = 'No data for "' + tableName + '".';
        if (btnDeleteSelectedRows) btnDeleteSelectedRows.disabled = true;
        return;
      }

      const columns = Object.keys(rows[0]); // uses first row to determine columns

      // Header: checkbox + columns
      const thSelect = document.createElement('th');
      thSelect.style.width = '32px';
      thSelect.innerHTML = `
        <input type="checkbox" id="check-all-data-rows">
      `;
      dataHeaderRow.appendChild(thSelect);

      columns.forEach(colName => {
        const th = document.createElement('th');
        th.textContent = colName;
        dataHeaderRow.appendChild(th);
      });

      // Body rows
      rows.forEach(row => {
        const tr = document.createElement('tr');
        const pkValue = currentPk ? (row[currentPk] ?? '') : '';
        tr.dataset.pkValue = pkValue;

        // checkbox
        const tdCheck = document.createElement('td');
        tdCheck.innerHTML = `
          <input type="checkbox" class="data-row-checkbox" data-pk-value="${String(pkValue)}">
        `;
        tr.appendChild(tdCheck);

        columns.forEach(colName => {
          const td = document.createElement('td');
          td.dataset.columnName = colName;
          const val = row[colName];
          if (val === null) {
            td.innerHTML = '<span class="text-muted">NULL</span>';
          } else {
            td.textContent = String(val);
          }
          tr.appendChild(td);
        });

        dataTbody.appendChild(tr);
      });

      dataTable.style.display = '';
      dataEmpty.style.display = 'none';
      if (dataSubtitle) dataSubtitle.textContent = 'Showing data of ' + tableName;

      // Check-all for rows
      const checkAllDataRows = document.getElementById('check-all-data-rows');
      if (checkAllDataRows) {
        checkAllDataRows.addEventListener('change', () => {
          const checked = checkAllDataRows.checked;
          document.querySelectorAll('.data-row-checkbox').forEach(ch => {
            ch.checked = checked;
          });
          refreshDataButtons();
        });
      }

      refreshDataButtons();
    } catch (err) {
      dataTable.style.display = 'none';
      dataEmpty.style.display = 'block';
      dataEmpty.textContent = 'Error loading data: ' + err.message;
      if (dataSubtitle) dataSubtitle.textContent = 'Error while loading data.';
      if (btnDeleteSelectedRows) btnDeleteSelectedRows.disabled = true;
      if (dataEditWarning) dataEditWarning.style.display = 'none';
    } finally {
      setLoadingData(false);
    }
  }

  // Auto load if a table is selected via ?table=...
  if (currentTable) {
    setActiveTableRow(currentTable);
    loadColumns(currentTable);
    loadData(currentTable, dataPage);
  } else {
    clearColumns();
    clearData();
  }

  refreshTableButtons();
})();
</script>

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