migrations.sql

-- Schema for wg_admin (extended)

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'user',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  expires_at DATETIME NULL,
  traffic_limit_mb BIGINT NULL,
  last_traffic_bytes BIGINT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
);

CREATE TABLE IF NOT EXISTS servers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  host VARCHAR(255) NOT NULL,
  public_key VARCHAR(255) NULL,
  endpoint VARCHAR(255) NULL,
  port INT DEFAULT 51820,
  config TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
);

-- Mapping users to server peers
CREATE TABLE IF NOT EXISTS user_peers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  server_id INT NOT NULL,
  public_key VARCHAR(255) NOT NULL,
  server_host VARCHAR(255) NOT NULL,
  peer_config TEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE
);

-- Example admin user (password: admin123)
INSERT INTO users (email, password_hash, role, created_at) VALUES (
  'admin@example.com',
  '$2y$10$e0NRX2ZtK1NQ8YvYQGvVveF6Kq0m6o1p0R1p1uVbXx6E0sVbG9a1a', 'admin', NOW()
);