cloud/schema.sql

DROP DATABASE IF EXISTS `auth`;

CREATE DATABASE `auth`;

DROP TABLE IF EXISTS auth.account_status;

CREATE TABLE auth.account_status (
    id INT PRIMARY KEY AUTO_INCREMENT,
    clientStatus VARCHAR(20) NOT NULL,
    allowLogin BOOLEAN NOT NULL,
    description TEXT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS auth.account;

CREATE TABLE auth.account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    lastLogin DATETIME NULL,
    deleteCode VARCHAR(255) NOT NULL,
    accountStatusId INT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (accountStatusId) REFERENCES account_status(id) ON DELETE CASCADE
);

INSERT INTO auth.account_status (`allowLogin`, `clientStatus`, `description`)
VALUES (TRUE, 'OK', 'Default Status');

INSERT INTO auth.account (`deleteCode`, `email`, `lastLogin`, `password`, 
`accountStatusId`, `username`)
VALUES ('1234567', 'admin@test.com', NULL, 
'$2b$05$KXeREc2TNuUR6IcgzUiX4.WA/0i3Yd3WpUHMtAcQi1ojWRdeQ9ExS', 1, 'admin');

INSERT INTO auth.account (`deleteCode`, `email`, `lastLogin`, `password`, 
`accountStatusId`, `username`)
VALUES ('1234567', 'admin1@test.com', NULL, 
'$2b$05$KXeREc2TNuUR6IcgzUiX4.WA/0i3Yd3WpUHMtAcQi1ojWRdeQ9ExS', 1, 'admin1');

DROP DATABASE IF EXISTS `game`;

CREATE DATABASE `game`;

DROP TABLE IF EXISTS game.player;

CREATE TABLE game.player (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    accountId INT UNSIGNED NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    empire TINYINT UNSIGNED NOT NULL,
    playerClass TINYINT UNSIGNED NOT NULL,
    skillGroup TINYINT UNSIGNED NOT NULL,
    playTime INT UNSIGNED NOT NULL DEFAULT 0,
    level INT UNSIGNED NOT NULL DEFAULT 1,
    experience INT UNSIGNED NOT NULL DEFAULT 0,
    gold INT UNSIGNED NOT NULL DEFAULT 0,
    st INT UNSIGNED NOT NULL DEFAULT 0,
    ht INT UNSIGNED NOT NULL DEFAULT 0,
    dx INT UNSIGNED NOT NULL DEFAULT 0,
    iq INT UNSIGNED NOT NULL DEFAULT 0,
    positionX INT NOT NULL,
    positionY INT NOT NULL,
    health BIGINT NOT NULL,
    mana BIGINT NOT NULL,
    stamina BIGINT NOT NULL,
    bodyPart INT UNSIGNED NOT NULL DEFAULT 0,
    hairPart INT UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(24) NOT NULL,
    givenStatusPoints INT UNSIGNED NOT NULL DEFAULT 0,
    availableStatusPoints INT UNSIGNED NOT NULL DEFAULT 0,
    slot TINYINT UNSIGNED NOT NULL DEFAULT 0
);

DROP TABLE IF EXISTS game.item;

CREATE TABLE game.item (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ownerId INT UNSIGNED NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    window TINYINT UNSIGNED NOT NULL,
    position TINYINT UNSIGNED NOT NULL,
    count TINYINT UNSIGNED NOT NULL,
    protoId INT UNSIGNED NOT NULL DEFAULT 0,
    socket0 INT UNSIGNED DEFAULT 0,
    socket1 INT UNSIGNED DEFAULT 0,
    socket2 INT UNSIGNED DEFAULT 0,
    attributeType0 INT UNSIGNED DEFAULT 0,
    attributeValue0 INT UNSIGNED DEFAULT 0,
    attributeType1 INT UNSIGNED DEFAULT 0,
    attributeValue1 INT UNSIGNED DEFAULT 0,
    attributeType2 INT UNSIGNED DEFAULT 0,
    attributeValue2 INT UNSIGNED DEFAULT 0,
    attributeType3 INT UNSIGNED DEFAULT 0,
    attributeValue3 INT UNSIGNED DEFAULT 0,
    attributeType4 INT UNSIGNED DEFAULT 0,
    attributeValue4 INT UNSIGNED DEFAULT 0,
    attributeType5 INT UNSIGNED DEFAULT 0,
    attributeValue5 INT UNSIGNED DEFAULT 0,
    attributeType6 INT UNSIGNED DEFAULT 0,
    attributeValue6 INT UNSIGNED DEFAULT 0,
    FOREIGN KEY (ownerId)
        REFERENCES game.player(id)
        ON DELETE CASCADE
);
-- Starter high-gold chars for admin (account id 1 after seed)
INSERT INTO game.player
(accountId, empire, playerClass, skillGroup, playTime, level, experience, gold, st, ht, dx, iq,
 positionX, positionY, health, mana, stamina, bodyPart, hairPart, name, givenStatusPoints, availableStatusPoints, slot)
VALUES
(1, 1, 0, 0, 0, 120, 0, 999999999, 90, 90, 90, 90, 469300, 964200, 50000, 20000, 10000, 0, 0, 'AdminWar', 0, 0, 0),
(1, 1, 1, 0, 0, 120, 0, 999999999, 90, 90, 90, 90, 469300, 964200, 50000, 20000, 10000, 0, 0, 'AdminNin', 0, 0, 1),
(1, 1, 2, 0, 0, 120, 0, 999999999, 90, 90, 90, 90, 469300, 964200, 50000, 20000, 10000, 0, 0, 'AdminSur', 0, 0, 2),
(1, 1, 3, 0, 0, 120, 0, 999999999, 90, 90, 90, 90, 469300, 964200, 50000, 20000, 10000, 0, 0, 'AdminSha', 0, 0, 3);

-- Learned skills (engine skill system)
CREATE TABLE IF NOT EXISTS game.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 game.player(id) ON DELETE CASCADE
);

-- Guilds
CREATE TABLE IF NOT EXISTS game.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 game.guild_member (
    guildId INT UNSIGNED NOT NULL,
    playerId INT UNSIGNED NOT NULL,
    joinedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (playerId),
    FOREIGN KEY (guildId) REFERENCES game.guild(id) ON DELETE CASCADE,
    FOREIGN KEY (playerId) REFERENCES game.player(id) ON DELETE CASCADE
);