USE betpa_game;

CREATE TABLE IF NOT EXISTS challenge_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  requester_user_id BIGINT UNSIGNED NOT NULL,
  requested_user_id BIGINT UNSIGNED NOT NULL,
  rule_set ENUM('normal','joker') NOT NULL DEFAULT 'normal',
  status ENUM('pending','accepted','ignored','cancelled','expired') NOT NULL DEFAULT 'pending',
  room_id BIGINT UNSIGNED NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (requested_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CHECK (requester_user_id <> requested_user_id),
  INDEX idx_challenge_inbox (requested_user_id, status, expires_at),
  INDEX idx_challenge_outbox (requester_user_id, status, expires_at)
);