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

session_start();
require_once __DIR__ . '/../config/db_remote.php';

$user = $_SESSION['user'] ?? null;
$role = $user['role'] ?? 'viewer';
if ($role !== 'admin') {
    die('Not allowed.');
}

$mysqli->set_charset('utf8mb4');

function ibc_sanitize_table(string $name): ?string {
    $name = trim($name);
    if ($name === '') return null;
    if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) return null;
    return $name;
}

// Load table list
$tables = [];
if ($res = $mysqli->query('SHOW TABLES')) {
    while ($row = $res->fetch_array(MYSQLI_NUM)) {
        $tables[] = $row[0];
    }
    sort($tables, SORT_NATURAL | SORT_FLAG_CASE);
}

$currentTable = ibc_sanitize_table($_GET['table'] ?? '');
if (!$currentTable && !empty($_SESSION['ibc_last_table'])) {
    $currentTable = ibc_sanitize_table($_SESSION['ibc_last_table']);
}
if ($currentTable && !in_array($currentTable, $tables, true)) {
    $currentTable = null;
}

$message = '';
$error   = '';

if ($currentTable && $_SERVER['REQUEST_METHOD'] === 'POST') {
    // Add column
    if (isset($_POST['add_column'])) {
        $name = trim($_POST['col_name'] ?? '');
        $type = trim($_POST['col_type'] ?? '');
        if ($name && preg_match('/^[A-Za-z0-9_]+$/', $name) && $type) {
            $nameEsc = $mysqli->real_escape_string($name);
            $typeClean = preg_replace('/[^A-Za-z0-9(),_ ]/', '', $type);
            try {
                $mysqli->query("ALTER TABLE `{$currentTable}` ADD COLUMN `{$nameEsc}` {$typeClean}");
                $message = "Column {$nameEsc} added.";
            } catch (mysqli_sql_exception $e) {
                $error = 'Error adding column: ' . $e->getMessage();
            }
        } else {
            $error = 'Invalid column name or type.';
        }
    }

    // Drop column
    if (isset($_POST['drop_column'])) {
        $name = trim($_POST['drop_col_name'] ?? '');
        if ($name && preg_match('/^[A-Za-z0-9_]+$/', $name)) {
            $nameEsc = $mysqli->real_escape_string($name);
            try {
                $mysqli->query("ALTER TABLE `{$currentTable}` DROP COLUMN `{$nameEsc}`");
                $message = "Column {$nameEsc} dropped.";
            } catch (mysqli_sql_exception $e) {
                $error = 'Error dropping column: ' . $e->getMessage();
            }
        }
    }

    // Rename column
    if (isset($_POST['rename_column'])) {
        $old = trim($_POST['old_col_name'] ?? '');
        $new = trim($_POST['new_col_name'] ?? '');
        $type = trim($_POST['rename_col_type'] ?? '');
        if ($old && $new && preg_match('/^[A-Za-z0-9_]+$/', $old) && preg_match('/^[A-Za-z0-9_]+$/', $new) && $type) {
            $oldEsc = $mysqli->real_escape_string($old);
            $newEsc = $mysqli->real_escape_string($new);
            $typeClean = preg_replace('/[^A-Za-z0-9(),_ ]/', '', $type);
            try {
                $mysqli->query("ALTER TABLE `{$currentTable}` CHANGE `{$oldEsc}` `{$newEsc}` {$typeClean}");
                $message = "Column {$oldEsc} renamed to {$newEsc}.";
            } catch (mysqli_sql_exception $e) {
                $error = 'Error renaming column: ' . $e->getMessage();
            }
        } else {
            $error = 'Invalid rename parameters.';
        }
    }

    // Change type only
    if (isset($_POST['change_type'])) {
        $col = trim($_POST['type_col_name'] ?? '');
        $type = trim($_POST['type_col_type'] ?? '');
        if ($col && preg_match('/^[A-Za-z0-9_]+$/', $col) && $type) {
            $colEsc = $mysqli->real_escape_string($col);
            $typeClean = preg_replace('/[^A-Za-z0-9(),_ ]/', '', $type);
            try {
                $mysqli->query("ALTER TABLE `{$currentTable}` MODIFY `{$colEsc}` {$typeClean}");
                $message = "Column {$colEsc} type changed.";
            } catch (mysqli_sql_exception $e) {
                $error = 'Error changing column type: ' . $e->getMessage();
            }
        } else {
            $error = 'Invalid type change parameters.';
        }
    }
}

// Load columns
$columns = [];
if ($currentTable) {
    if ($res = $mysqli->query("SHOW COLUMNS FROM `{$currentTable}`")) {
        while ($c = $res->fetch_assoc()) {
            $columns[] = $c;
        }
    }
}
?>
<?php include __DIR__ . '/../includes/header.php'; ?>
<?php include __DIR__ . '/../includes/sidebar.php'; ?>

<div class="container-fluid mt-3">
  <div class="d-flex justify-content-between align-items-center mb-2">
    <div>
      <div class="small text-muted">IBC Manager · Step 3</div>
      <h3 class="mb-0">Table structure</h3>
    </div>
    <div>
      <a href="/smproduce/pages/ibc_tables.php" class="btn btn-sm btn-outline-secondary me-2">← Back to tables</a>
      <?php if ($currentTable): ?>
        <a href="/smproduce/pages/ibc_table_view.php?table=<?= urlencode($currentTable) ?>" class="btn btn-sm btn-outline-primary">Back to rows</a>
      <?php endif; ?>
    </div>
  </div>

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

  <?php if (!$currentTable): ?>
    <div class="alert alert-info">No table selected. Go back to <a href="/smproduce/pages/ibc_tables.php">IBC Tables</a> and choose one.</div>
  <?php else: ?>
    <div class="card shadow-sm card-rounded mb-3">
      <div class="card-body d-flex justify-content-between align-items-center">
        <div>
          <div class="small text-muted">Current table</div>
          <div class="fs-5 fw-semibold"><?= htmlspecialchars($currentTable) ?></div>
        </div>
      </div>
    </div>

    <div class="row g-3">
      <div class="col-lg-7">
        <div class="card shadow-sm card-rounded h-100">
          <div class="card-header">
            <strong>Columns</strong>
          </div>
          <div class="card-body p-0">
            <div class="table-responsive" style="max-height:60vh;overflow:auto;">
              <table class="table table-sm table-striped mb-0">
                <thead class="table-light">
                  <tr>
                    <th>Name</th>
                    <th>Type</th>
                    <th>Null</th>
                    <th>Key</th>
                    <th>Default</th>
                    <th>Extra</th>
                  </tr>
                </thead>
                <tbody>
                  <?php foreach ($columns as $c): ?>
                    <tr>
                      <td><?= htmlspecialchars($c['Field']) ?></td>
                      <td><?= htmlspecialchars($c['Type']) ?></td>
                      <td><?= htmlspecialchars($c['Null']) ?></td>
                      <td><?= htmlspecialchars($c['Key']) ?></td>
                      <td><?= htmlspecialchars((string)$c['Default']) ?></td>
                      <td><?= htmlspecialchars($c['Extra']) ?></td>
                    </tr>
                  <?php endforeach; ?>
                  <?php if (!$columns): ?>
                    <tr><td colspan="6" class="text-muted">No columns found.</td></tr>
                  <?php endif; ?>
                </tbody>
              </table>
            </div>
          </div>
        </div>
      </div>

      <div class="col-lg-5">
        <div class="card shadow-sm card-rounded mb-3">
          <div class="card-header"><strong>Add column</strong></div>
          <div class="card-body">
            <form method="post" class="small">
              <div class="mb-2">
                <label class="form-label form-label-sm">Name</label>
                <input type="text" name="col_name" class="form-control form-control-sm" placeholder="ex: new_field">
              </div>
              <div class="mb-2">
                <label class="form-label form-label-sm">Type (full)</label>
                <input type="text" name="col_type" class="form-control form-control-sm" placeholder="ex: VARCHAR(100) NULL">
              </div>
              <button class="btn btn-sm btn-success" name="add_column">Add column</button>
            </form>
          </div>
        </div>

        <div class="card shadow-sm card-rounded mb-3">
          <div class="card-header"><strong>Drop column</strong></div>
          <div class="card-body">
            <form method="post" class="small">
              <div class="mb-2">
                <label class="form-label form-label-sm">Select column</label>
                <select name="drop_col_name" class="form-select form-select-sm">
                  <?php foreach ($columns as $c):
                    if ($c['Field'] === 'id') continue;
                  ?>
                    <option value="<?= htmlspecialchars($c['Field'], ENT_QUOTES) ?>"><?= htmlspecialchars($c['Field']) ?></option>
                  <?php endforeach; ?>
                </select>
              </div>
              <button class="btn btn-sm btn-danger" name="drop_column" onclick="return confirm('Drop this column?');">Drop</button>
            </form>
          </div>
        </div>

        <div class="card shadow-sm card-rounded">
          <div class="card-header"><strong>Rename / Change type</strong></div>
          <div class="card-body">
            <form method="post" class="small mb-3">
              <div class="mb-1">
                <label class="form-label form-label-sm">Old name</label>
                <select name="old_col_name" class="form-select form-select-sm">
                  <?php foreach ($columns as $c): ?>
                    <option value="<?= htmlspecialchars($c['Field'], ENT_QUOTES) ?>"><?= htmlspecialchars($c['Field']) ?></option>
                  <?php endforeach; ?>
                </select>
              </div>
              <div class="mb-1">
                <label class="form-label form-label-sm">New name</label>
                <input type="text" name="new_col_name" class="form-control form-control-sm">
              </div>
              <div class="mb-2">
                <label class="form-label form-label-sm">Full type</label>
                <input type="text" name="rename_col_type" class="form-control form-control-sm" placeholder="ex: VARCHAR(100) NULL">
              </div>
              <button class="btn btn-sm btn-warning" name="rename_column">Rename column</button>
            </form>

            <form method="post" class="small">
              <div class="mb-1">
                <label class="form-label form-label-sm">Column</label>
                <select name="type_col_name" class="form-select form-select-sm">
                  <?php foreach ($columns as $c): ?>
                    <option value="<?= htmlspecialchars($c['Field'], ENT_QUOTES) ?>"><?= htmlspecialchars($c['Field']) ?></option>
                  <?php endforeach; ?>
                </select>
              </div>
              <div class="mb-2">
                <label class="form-label form-label-sm">New type (full)</label>
                <input type="text" name="type_col_type" class="form-control form-control-sm" placeholder="ex: INT NOT NULL">
              </div>
              <button class="btn btn-sm btn-primary" name="change_type">Change type</button>
            </form>
          </div>
        </div>

      </div>
    </div>
  <?php endif; ?>
</div>
