19 lines
713 B
SQL
19 lines
713 B
SQL
CREATE TABLE IF NOT EXISTS players (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nickname VARCHAR(50) NOT NULL,
|
|
session_id VARCHAR(100) NOT NULL UNIQUE,
|
|
last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX (last_seen)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invitations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
from_player_id INT NOT NULL,
|
|
to_player_id INT NOT NULL,
|
|
room_code VARCHAR(10) NOT NULL,
|
|
status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (from_player_id) REFERENCES players(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (to_player_id) REFERENCES players(id) ON DELETE CASCADE
|
|
);
|