cloud/migrate_engine_v2.sql

-- Run on existing game DB to add engine v2 tables (skills + guilds)
USE game;

CREATE TABLE IF NOT EXISTS player_skill (
    playerId INT UNSIGNED NOT NULL,
    skillId INT UNSIGNED NOT NULL,
    skillLevel TINYINT UNSIGNED NOT NULL DEFAULT 1,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (playerId, skillId),
    FOREIGN KEY (playerId) REFERENCES player(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS guild (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(24) NOT NULL UNIQUE,
    masterId INT UNSIGNED NOT NULL,
    notice VARCHAR(120) NOT NULL DEFAULT '',
    level INT UNSIGNED NOT NULL DEFAULT 1,
    exp INT UNSIGNED NOT NULL DEFAULT 0,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS guild_member (
    guildId INT UNSIGNED NOT NULL,
    playerId INT UNSIGNED NOT NULL,
    joinedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (playerId),
    FOREIGN KEY (guildId) REFERENCES guild(id) ON DELETE CASCADE,
    FOREIGN KEY (playerId) REFERENCES player(id) ON DELETE CASCADE
);