-- Migration for Multiplayer Ludo and Notifications
CREATE TABLE IF NOT EXISTS ludo_matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creator_id INT NOT NULL,
    bet_amount DECIMAL(20, 8) NOT NULL,
    status ENUM('waiting', 'playing', 'completed', 'cancelled') DEFAULT 'waiting',
    player_count INT DEFAULT 1,
    max_players INT DEFAULT 4,
    winner_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES players(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ludo_match_players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL,
    player_id INT NOT NULL,
    color ENUM('red', 'yellow', 'green', 'blue') NOT NULL,
    status ENUM('active', 'left') DEFAULT 'active',
    FOREIGN KEY (match_id) REFERENCES ludo_matches(id),
    FOREIGN KEY (player_id) REFERENCES players(id),
    UNIQUE(match_id, player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id INT NOT NULL,
    sender_id INT NOT NULL,
    type ENUM('invite', 'system') DEFAULT 'invite',
    message VARCHAR(255),
    data JSON,
    status ENUM('unread', 'read', 'accepted', 'declined') DEFAULT 'unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES players(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;