CREATE TABLE IF NOT EXISTS clan_applications (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  clan_name VARCHAR(80) NOT NULL DEFAULT 'Draconian Knights',
  public_token CHAR(32) NOT NULL,
  discord_user_id VARCHAR(32) NULL,

  display_name VARCHAR(80) NOT NULL,
  discord VARCHAR(80) NOT NULL,
  region VARCHAR(40) NOT NULL,
  age_group VARCHAR(20) NOT NULL,
  primary_game VARCHAR(80) NOT NULL,
  playstyle VARCHAR(20) NOT NULL,
  message TEXT NOT NULL,

  steam_profile_url VARCHAR(255) NULL,
  xbox_gamertag VARCHAR(40) NULL,
  psn_id VARCHAR(32) NULL,
  nintendo_friend_code VARCHAR(20) NULL,

  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,

  status ENUM('new','reviewing','accepted','rejected') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY uniq_public_token (public_token),
  INDEX idx_created_at (created_at),
  INDEX idx_status (status),
  INDEX idx_discord_user_id (discord_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
  ip_address VARCHAR(45) NOT NULL,
  last_submit_at TIMESTAMP NULL DEFAULT NULL,
  hour_bucket CHAR(13) NOT NULL,
  hour_count INT NOT NULL DEFAULT 0,
  PRIMARY KEY (ip_address),
  INDEX idx_hour_bucket (hour_bucket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
