SWDB/install_data/02_CREATE_DATA.sql

CREATE TABLE data.user (
    id INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    mail TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    api_key TEXT NOT NULL UNIQUE,
    admin INT NOT NULL DEFAULT 0 CHECK(admin IN (0, 1))
);

CREATE TABLE data.token (
    user INT NOT NULL REFERENCES user(id),
    token TEXT NOT NULL,
    expiry DATETIME NOT NULL
);

CREATE TABLE data.player(
    id INT PRIMARY KEY NOT NULL,
    user INT NOT NULL REFERENCES user(id),
    server INT NOT NULL,
    country TEXT,
    lang TEXT,
    name TEXT NOT NULL UNIQUE,
    mana INT NOT NULL CHECK (mana >= 0),
    crystal INT NOT NULL CHECK (crystal >= 0),
    level INT NOT NULL CHECK (level BETWEEN 1 AND 50),
    experience INT NOT NULL CHECK (experience >= 0),
    energy INT NOT NULL CHECK (energy >= 0),
    energy_max INT NOT NULL CHECK (energy_max >= 0),
    energy_per_min INT NOT NULL CHECK (energy_per_min >= 0),
    arena_energy INT NOT NULL CHECK (arena_energy >= 0),
    rep INT REFERENCES unit(id),
    social_point INT NOT NULL CHECK (social_point >= 0),
    honor_point INT NOT NULL CHECK (honor_point >= 0),
    guild_point INT NOT NULL CHECK (guild_point >= 0),
    darkportal_energy INT NOT NULL CHECK (darkportal_energy >= 0),
    dimension_energy INT NOT NULL CHECK (dimension_energy >= 0),
    costume_point INT NOT NULL CHECK (costume_point >= 0),
    honor_medal INT NOT NULL CHECK (honor_medal >= 0),
    honor_mark INT NOT NULL CHECK (honor_mark >= 0),
    event_coin INT NOT NULL CHECK (event_coin >= 0),
    storage_slots INT NOT NULL CHECK (storage_slots >= 0),
    island INT NOT NULL CHECK (island >= 0),
    joined TIMESTAMP,
    top_rank_arena INT DEFAULT 0,
    top_rank_world_arena INT DEFAULT 0,
    top_rank_special_league INT DEFAULT 0,
    top_rank_gw INT DEFAULT 0,
    top_rank_siege INT DEFAULT 0,
    top_rank_wboss INT DEFAULT 0,
    top_rank_toan INT DEFAULT 0,
    top_rank_toah INT DEFAULT 0,
    top_rank_toal INT DEFAULT 0,
    public INT NOT NULL DEFAULT 0 CHECK(public IN (0, 1)),
    mode INT NOT NULL CHECK(mode IN (0, 1)) DEFAULT 0,
    last_access DATETIME
);
CREATE TABLE data.record(
    player INT NOT NULL REFERENCES player(id),
    area_type INT NOT NULL, --REFERENCES key.area_type,
    area INT NOT NULL, --REFERENCES key.area,
    stage INT CHECK(stage >= 0),
    time INT,
    score INT,
    rank TEXT,
    PRIMARY KEY (player, area_type, area)
);
CREATE TABLE data.record_party(
    player INT NOT NULL REFERENCES player(id),
    area_type INT NOT NULL, --REFERENCES key.area_type,
    area INT NOT NULL, --REFERENCES key.area,
    unit INT NOT NULL REFERENCES unit(id),
    monster INT NOT NULL, --REFERENCES key.unit(id),
    leader INT NOT NULL DEFAULT 0,
    front INT NOT NULL DEFAULT 0
);
CREATE TABLE data.collection(
    player INT NOT NULL REFERENCES player(id),
    monster INT NOT NULL, --REFERENCES key.monster(id),
    open INT CHECK(open IN (0, 1))
);

CREATE TABLE data.defense(
    player INT NOT NULL REFERENCES player(id),
    area_type INT NOT NULL REFERENCES area_type(id),
    slot INT NOT NULL CHECK(slot BETWEEN 0 AND 9) DEFAULT 0,
    unit INT NOT NULL REFERENCES unit(id),
    position INT NOT NULL CHECK(position BETWEEN 1 AND 4),
    PRIMARY KEY (player, area_type, unit)
);
CREATE TABLE data.unit(
    player INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    building INT REFERENCES building(id),
    monster INT NOT NULL REFERENCES unit(id),
    level INT NOT NULL CHECK(level BETWEEN 1 AND 40),
    stars INT INT NOT NULL CHECK(stars BETWEEN 1 AND 6),
    hp INT NOT NULL CHECK(hp >= 0),
    attack INT NOT NULL CHECK(attack >= 0),
    defense INT NOT NULL CHECK(defense >= 0),
    speed INT NOT NULL CHECK(speed >= 0),
    crit_rate INT NOT NULL CHECK(crit_rate >= 0),
    crit_damage INT NOT NULL CHECK(crit_damage >= 0),
    resistance INT NOT NULL CHECK(resistance >= 0),
    accuracy INT NOT NULL CHECK(accuracy >= 0),
    experience INT NOT NULL CHECK(experience >= 0),
    exp_gained INT NOT NULL CHECK(exp_gained >= 0),
    exp_gain_rate INT NOT NULL CHECK(exp_gain_rate >= 0),
    costume INT,
    source INT, --REFERENCES key.source(id),
    create_time DATETIME NOT NULL,
    homunculus_name TEXT,
    lock INT NOT NULL CHECK(lock IN (0, 1))
);
CREATE TABLE data.unit_skill(
    unit INT NOT NULL REFERENCES unit(id),
    skill INT NOT NULL, --REFERENCES key.skill(id),
    level INT NOT NULL CHECK(level > 0),
    PRIMARY KEY (unit, skill)
);
CREATE TABLE data.rune(
    player INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    type INT, --REFERENCES key.rune_set(id),
    slot INT NOT NULL CHECK(slot BETWEEN 1 AND 6),
    stars INT NOT NULL CHECK(stars BETWEEN 1 AND 6),
    ancient INT NOT NULL CHECK(ancient IN (0, 1)),
    level INT NOT NULL CHECK(level BETWEEN 0 AND 15),
    quality INT NOT NULL, --REFERENCES key.quality(id),
    original_quality INT NOT NULL, --REFERENCES key.quality(id),
    value INT NOT NULL CHECK(value > 0),
    efficiency FLOAT CHECK(efficiency BETWEEN 0 AND 100),
    max_efficiency FLOAT CHECK(max_efficiency BETWEEN 0 AND 100),
    locked INT NOT NULL CHECK(locked IN (0, 1))
);
CREATE TABLE data.rune_stat(
    rune INT NOT NULL references rune(id),
    slot INT NOT NULL CHECK(slot BETWEEN -1 AND 4),
    stat INT NOT NULL, --REFERENCES key.rune_stat(id),
    value INT NOT NULL CHECK(value >= 0),
    enchant INT NOT NULL CHECK(enchant IN (0, 1)),
    grind INT NOT NULL CHECK(grind >= 0),
    rolls INT NOT NULL CHECK(rolls BETWEEN 0 AND 4),
    PRIMARY KEY (rune, slot)
);
CREATE TABLE data.unit_rune(
    unit INT NOT NULL REFERENCES unit(id),
    rune INT NOT NULL REFERENCES rune(id),
    rta INT NOT NULL DEFAULT 0 CHECK(rta IN (0, 1)),
    PRIMARY KEY(unit, rune, rta)
);
CREATE TABLE data.artifact(
    player INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    type INT, --REFERENCES key.archetype(id),
    attribute INT, --REFERENCES key.element(id),
    archetype INT, --REFERENCES key.archetype(id),
    level INT NOT NULL CHECK(level BETWEEN 0 AND 15),
    quality INT NOT NULL, --REFERENCES key.quality(id),
    original_quality INT NOT NULL, --REFERENCES key.quality(id),
    value INT NOT NULL CHECK(value >= 0),
    efficiency FLOAT NOT NULL CHECK(efficiency BETWEEN 0 AND 100),
    max_efficiency FLOAT NOT NULL CHECK(max_efficiency BETWEEN 0 AND 100),
    locked INT NOT NULL CHECK(locked IN (0, 1))
);
CREATE TABLE data.artifact_stat(
    artifact INT NOT NULL references artifact(id),
    stat INT NOT NULL, --REFERENCES key.artifact_stat(id),
    value INT NOT NULL CHECK(value >= 0),
    PRIMARY KEY (artifact)
);
CREATE TABLE data.artifact_effect(
    artifact INT NOT NULL references artifact(id),
    slot INT NOT NULL CHECK(slot BETWEEN 1 AND 4),
    effect INT NOT NULL, --REFERENCES key.artifact_effect(id),
    value FLOAT NOT NULL CHECK(value >= 0),
    enchant INT NOT NULL CHECK(enchant IN (0, 1)),
    grind INT NOT NULL CHECK(grind >= 0),
    rolls INT NOT NULL CHECK(rolls BETWEEN 0 AND 4),
    PRIMARY KEY (artifact, slot)
);
CREATE TABLE data.unit_artifact(
    unit INT NOT NULL REFERENCES unit(id),
    artifact INT NOT NULL REFERENCES artifact(id),
    rta INT NOT NULL DEFAULT 0 CHECK(rta IN (0, 1)),
    PRIMARY KEY(unit, artifact, rta)
);
CREATE TABLE data.item(
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL, --REFERENCES key.inventory(id),
    type INT NOT NULL, --REFERENCES key.inventory_type(id),
    amount INT NOT NULL CHECK(amount >= 0)
);
CREATE TABLE data.summon_special(
    week DATETIME NOT NULL,
    unit INT NOT NULL, --REFERENCES key.unit(id),
    PRIMARY KEY (week, unit)
);
CREATE TABLE data.guild(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    level INT NOT NULL CHECK(level BETWEEN 0 AND 100),
    experience NOT NULL CHECK(experience >= 0),
    recruiting NOT NULL CHECK(recruiting IN (0, 1)),
    members INT NOT NULL CHECK(level BETWEEN 0 AND 30),
    leader INT REFERENCES guild_member(id),
    comment TEXT,
    notice TEXT
);
CREATE TABLE data.guild_member(
    member INT NOT NULL PRIMARY KEY,
    guild INT NOT NULL REFERENCES guild(id),
    grade INT NOT NULL CHECK(grade BETWEEN 1 AND 3),
    name TEXT NOT NULL,
    level INT NOT NULL CHECK(level BETWEEN 1 AND 50),
    rating INT NOT NULL CHECK(rating >= 0),
    arena_score INT NOT NULL CHECK(arena_score >= 0),
    joined TIMESTAMP NOT NULL,
    last_login TIMESTAMP NOT NULL,
    in_war INT NOT NULL CHECK(in_war IN (0, 1)),
    has_defense INT NOT NULL CHECK(has_defense IN (0, 1))
);
CREATE TABLE data.enchantment(
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL,
    quality INT NOT NULL, --REFERENCES key.quality(id),
    rune INT NOT NULL, --REFERENCES key.rune_set(id),
    stat INT NOT NULL, --REFERENCES key.rune_stat(id),
    value INT NOT NULL CHECK(value >= 0),
    amount INT NOT NULL CHECK(amount >= 0)
);
CREATE TABLE data.run_party(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES unit(id),
    monster INT NOT NULL, --REFERENCES key.unit(id),
    leader INT NOT NULL DEFAULT 0,
    front INT NOT NULL DEFAULT 0,
    PRIMARY KEY (run, unit)
);
CREATE TABLE data.run_drop_enchantment(
    run INT NOT NULL REFERENCES run(id),
    id INT NOT NULL,
    type INT NOT NULL, --REFERENCES key.enchantment(id), -- TODO: Duplicated??
    quality INT NOT NULL, --REFERENCES key.quality(id),
    rune INT NOT NULL, --REFERENCES key.rune_set(id),
    stat INT NOT NULL, --REFERENCES key.rune_stat(id),
    value INT NOT NULL CHECK(value >= 0)
);
CREATE TABLE data.run_drop_unit(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES kunit(id),
    PRIMARY KEY (run, unit)
);
CREATE TABLE data.run_drop_sd(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES kunit(id),
    PRIMARY KEY (run, unit)
);
CREATE TABLE data.run_drop_unit_pieces(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES kunit(id),
    amount INT NOT NULL CHECK(amount > 0),
    PRIMARY KEY (run, unit)
);
CREATE TABLE data.run_drop_shapeshifting(
    run INT NOT NULL PRIMARY KEY REFERENCES run(id),
    amount INT NOT NULL CHECK(amount > 0)
);
CREATE TABLE data.team_unit(
    team INT NOT NULL REFERENCES team(id),
    unit INT NOT NULL REFERENCES unit(id),
    leader INT NOT NULL DEFAULT 0,
    front INT NOT NULL DEFAULT 0,
    PRIMARY KEY(team, unit)
);
CREATE TABLE data.run(
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    dtime TIMESTAMP NOT NULL,
    area_type INT NOT NULL, --REFERENCES key.area_type,
    area INT NOT NULL, --REFERENCES key.area,
    stage INT CHECK(stage > 0),
    difficulty INT, --REFERENCES key.difficulty(id),
    win INT NOT NULL CHECK(win IN (0, 1)),
    time INT NOT NULL CHECK(time > 0),
    mana INT NOT NULL CHECK(mana >= 0),
    energy INT NOT NULL CHECK(energy >= 0),
    crystal INT NOT NULL CHECK(crystal >= 0),
    guild_points NOT NULL CHECK(guild_points >= 0),
    honor_points NOT NULL CHECK(honor_points >= 0),
    helper INT NOT NULL CHECK(helper IN (0, 1)),
    score INT,
    rank TEXT
);
CREATE TABLE data.run_drop_rune(
    run INT NOT NULL REFERENCES run(id),
    id INT NOT NULL PRIMARY KEY REFERENCES rune(id),
    type INT, --REFERENCES key.rune_set(id),
    slot INT NOT NULL CHECK(slot BETWEEN 1 AND 6),
    stars INT NOT NULL CHECK(stars BETWEEN 1 AND 6),
    ancient INT NOT NULL CHECK(ancient IN (0, 1)),
    quality INT NOT NULL, --REFERENCES key.quality(id),
    value INT NOT NULL CHECK(value > 0),
    efficiency FLOAT NOT NULL CHECK(efficiency BETWEEN 0 AND 100),
    max_efficiency FLOAT NOT NULL CHECK(max_efficiency BETWEEN 0 AND 100),
    main_stat INT NOT NULL, --REFERENCES key.rune_stat(id),
    main_stat_value INT NOT NULL CHECK(value > 0),
    innate_stat INT, --REFERENCES key.rune_stat(id),
    innate_stat_value INT CHECK(innate_stat_value >= 0),
    substat_1 INT, --REFERENCES key.rune_stat(id),
    substat_1_value INT CHECK(substat_1_value >= 0),
    substat_2 INT, --REFERENCES key.rune_stat(id),
    substat_2_value INT CHECK(substat_2_value >= 0),
    substat_3 INT, --REFERENCES key.rune_stat(id),
    substat_3_value INT CHECK(substat_3_value >= 0),
    substat_4 INT, --REFERENCES key.rune_stat(id),
    substat_4_value INT CHECK(substat_4_value >= 0)
);

CREATE TABLE data.run_drop_artifact(
    run INT NOT NULL REFERENCES run(id),
    id INT PRIMARY KEY NOT NULL,
    type INT, --REFERENCES key.archetype(id),
    attribute INT, --REFERENCES key.element(id),
    archetype INT, --REFERENCES key.archetype(id),
    quality INT NOT NULL, --REFERENCES key.quality(id),
    value INT NOT NULL CHECK(value >= 0),
    efficiency FLOAT CHECK(efficiency BETWEEN 0 AND 100),
    max_efficiency FLOAT CHECK(max_efficiency BETWEEN 0 AND 100),
    stat INT NOT NULL, --REFERENCES key.artifact_stat(id),
    stat_value INT NOT NULL CHECK(value > 0),
    effect_1 INT, --REFERENCES key.artifact_effect(id),
    effect_1_value INT CHECK(effect_1_value >= 0),
    effect_2 INT, --REFERENCES key.artifact_effect(id),
    effect_2_value INT CHECK(effect_2_value >= 0),
    effect_3 INT, --REFERENCES key.artifact_effect(id),
    effect_3_value INT CHECK(effect_3_value >= 0),
    effect_4 INT, --REFERENCES key.artifact_effect(id),
    effect_4_value INT CHECK(effect_4_value >= 0)
);

CREATE TABLE data.run_drop_item(
    run INT NOT NULL REFERENCES run(id),
    item INT NOT NULL, --REFERENCES key.inventory(id),
    amount INT NOT NULL CHECK(amount > 0),
    PRIMARY KEY (run, item)
);

CREATE TABLE data.team(
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    name INT NOT NULL,
    description TEXT,
    area_type INT NOT NULL, --REFERENCES key.area_type(id),
    area INT, --REFERENCES key.area(id),
    stage INT,
    difficulty INT, --REFERENCES key.difficulty(id),
    score INT NOT NULL DEFAULT 0
);
CREATE TABLE data.building(
    -- No primary key, can have multiple towers
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL,
    buildable INT NOT NULL, --REFERENCES key.buildable(id),
    gain INT NOT NULL CHECK(gain >= 0)
);
CREATE TABLE data.decoration(
    player INT NOT NULL REFERENCES player(id),
    id INT NOT NULL,
    decorative INT NOT NULL, --REFERENCES key.decorative(id),
    level INT NOT NULL CHECK(level > 0),
    PRIMARY KEY (player, decorative)
);