SWDB/install_data/01_CREATE_KEYS.sql

CREATE TABLE key.server(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE key.difficulty(
    id INT NOT NULL PRIMARY KEY CHECK(id BETWEEN 0 AND 3),
    name TEXT NOT NULL
);

CREATE TABLE key.source(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    farmable INT NOT NULL CHECK(farmable IN (1, 0))
);

CREATE TABLE key.element(
    id INT NOT NULL PRIMARY KEY CHECK(id BETWEEN 0 AND 6),
    name TEXT NOT NULL UNIQUE,
    strong_against INT REFERENCES element(id),
    weak_against INT REFERENCES element(id)
);

CREATE TABLE key.archetype(
    id INT NOT NULL PRIMARY KEY CHECK(id BETWEEN 0 AND 5),
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE key.stat(
    id INT NOT NULL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE key.effect_area(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE key.area_type(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    effect INT
);

CREATE TABLE key.area (
    id INT NOT NULL,
    type INT NOT NULL REFERENCES area_type(id),
    name TEXT NOT NULL UNIQUE,
    PRIMARY KEY (id, type)
);

CREATE TABLE key.decorative(
    id INT NOT NULL PRIMARY KEY,
    area REFERENCES area_type(id),
    affected_stat INT REFERENCES stat(id),
    element INT REFERENCES element(id),
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    max_level INT CHECK(max_level > 0)
);

CREATE TABLE key.buildable(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE key.inventory_type(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE key.inventory(
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL REFERENCES inventory_type(id),
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE key.experience(
    stars INT NOT NULL CHECK(stars BETWEEN 1 AND 6),
    level INT NOT NULL CHECK(level BETWEEN 1 AND 40),
    exp INT NOT NULL CHECK(exp >= 0),
    PRIMARY KEY(stars, level)
);

CREATE TABLE key.guild_skill_group(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE key.guild_skill(
    id INT NOT NULL PRIMARY KEY,
    skill_group INT NOT NULL REFERENCES guild_skill_group(id),
    level INT NOT NULL CHECK(level > 0),
    effect TEXT
);

CREATE TABLE key.rune_set(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    amount INT NOT NULL CHECK(amount IN (2, 4)),
    description TEXT
);

CREATE TABLE key.stat_rune(
    id INT NOT NULL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE key.quality(
    id INT NOT NULL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE key.stat_artifact(
    id INT NOT NULL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE key.effect_artifact(
    id INT NOT NULL PRIMARY KEY,
    name TEXT UNIQUE,
    deprecated INT NOT NULL CHECK(deprecated IN (1, 0))
);

CREATE TABLE key.artifact_roll_type(
    id INT NOT NULL PRIMARY KEY,
    ancient INT NOT NULL CHECK(ancient IN (1, 0)),
    initial INT NOT NULL CHECK(initial IN (1, 0)),
    upgrade INT NOT NULL CHECK(upgrade IN (1, 0)),
    description TEXT
);

CREATE TABLE key.artifact_effect_roll(
    type INT NOT NULL REFERENCES artifact_roll_type(id),
    effect INT NOT NULL REFERENCES artifact_effect(id),
    min FLOAT NOT NULL,
    max FLOAT NOT NULL,
    PRIMARY KEY(type, effect)
);

CREATE TABLE key.enchantment_type(
    id INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    gem INT NOT NULL CHECK(gem IN (1, 0)),
    inmemorial INT NOT NULL CHECK(inmemorial IN (1, 0)),
    ancient INT NOT NULL CHECK(ancient IN (1, 0))
);

CREATE TABLE key.enchantment_range(
    gem INT NOT NULL CHECK(gem IN (1, 0)),
    ancient INT NOT NULL CHECK(ancient IN (1, 0)),
    quality INT NOT NULL REFERENCES quality(id),
    stat INT INT NOT NULL REFERENCES rune_stat(id),
    min INT NOT NULL CHECK(min > 0),
    max INT NOT NULL CHECK(max > 0)
);

CREATE TABLE key.decorative_level(
    decorative INT NOT NULL REFERENCES decorative(id),
    level INT NOT NULL CHECK(level BETWEEN 1 AND 10),
    bonus INT NOT NULL CHECK(bonus > 0),
    cost INT NOT NULL CHECK(cost > 0),
    PRIMARY KEY(decorative, level)
);

CREATE TABLE key.effect(
    id INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    is_buff INT NOT NULL CHECK(is_buff IN (1, 0)),
    description TEXT
);

CREATE TABLE key.skill(
    id INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    slot INT NOT NULL CHECK(slot >= -1 AND slot <= 5),
    cooltime INT CHECK(cooltime IS NULL OR cooltime >= 0),
    hits INT INT CHECK(hits >= 0),
    passive INT NOT NULL CHECK(passive IN (1, 0)),
    aoe INT NOT NULL CHECK(aoe IN (1, 0)),
    max_level INT NOT NULL CHECK(max_level >= 0),
    multiplier_formula TEXT
);

CREATE TABLE key.skill_level(
    skill INT NOT NULL REFERENCES skill(id),
    level INT NOT NULL CHECK(level BETWEEN 1 AND 10),
    description TEXT,
    PRIMARY KEY(skill, level)
);

CREATE TABLE key.skill_effect(
    skill INT NOT NULL REFERENCES skill(id),
    effect INT NOT NULL REFERENCES effect(id)
    -- No primary key, a skill can inflict an effect multiple times
);

CREATE TABLE key.leader_skill(
    id INT NOT NULL PRIMARY KEY,
    stat INT NOT NULL REFERENCES stat(id),
    amount INT NOT NULL CHECK(amount > 0 AND amount <= 100),
    area INT REFERENCES area_type(id),
    element INT REFERENCES element(id)
);

CREATE TABLE key.monster(
    id INT PRIMARY KEY NOT NULL,
    family INT NOT NULL,
    name TEXT NOT NULL,
    element INT REFERENCES element(id),
    archetype INT REFERENCES archetype(id),
    base_stars INT NOT NULL CHECK(base_stars > 0 AND base_stars <= 6),
    natural_stars INT NOT NULL CHECK(natural_stars > 0 AND natural_stars <= 5),
    obtainable INT NOT NULL CHECK(obtainable IN (1, 0)),
    can_awaken INT NOT NULL CHECK(can_awaken IN (1, 0)),
    awaken_bonus TEXT,
    skill_ups_to_max INT,
    leader_skill INT REFERENCES leader_skill(id),
    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),
    raw_hp INT NOT NULL CHECK(raw_hp >= 0),
    raw_attack INT NOT NULL CHECK(raw_attack >= 0),
    raw_defense INT NOT NULL CHECK(raw_defense >= 0),
    max_lvl_hp INT NOT NULL CHECK(max_lvl_hp >= 0),
    max_lvl_attack INT NOT NULL CHECK(max_lvl_attack >= 0),
    max_lvl_defense INT NOT NULL CHECK(max_lvl_defense >= 0),
    awakens_from INT REFERENCES monster(id),
    awakens_to INT REFERENCES monster(id),
    fusion_food INT NOT NULL CHECK(fusion_food IN (1, 0)),
    homunculus INT NOT NULL CHECK(homunculus IN (1, 0)),
    craft_cost INT
);

CREATE TABLE key.monster_skill(
    monster INT NOT NULL REFERENCES monster(id),
    skill INT NOT NULL REFERENCES skill(id),
    PRIMARY KEY(monster, skill)
);

CREATE TABLE key.monster_source(
    monster INT NOT NULL REFERENCES monster(id),
    source INT NOT NULL REFERENCES source(id),
    PRIMARY KEY(monster, source)
);

CREATE TABLE key.monster_essence(
    monster INT NOT NULL REFERENCES monster(id),
    item INT NOT NULL REFERENCES inventory(id),
    amount INT NOT NULL CHECK(amount > 0),
    PRIMARY KEY(monster, item)
);

CREATE TABLE key.secret_dungeon(
    monster IT NOT NULL PRIMARY KEY REFERENCES monster(id)
);

CREATE TABLE key.monster_transformation(
    id INT PRIMARY KEY NOT NULL,
    family INT NOT NULL,
    monster INT NOT NULL REFERENCES monster(id)
);

CREATE TABLE key.rune_roll_type(
    id INT NOT NULL PRIMARY KEY,
    ancient INT NOT NULL CHECK(ancient IN (1, 0)),
    initial INT NOT NULL CHECK(initial IN (1, 0)),
    upgrade INT NOT NULL CHECK(upgrade IN (1, 0)),
    description TEXT
);

CREATE TABLE key.rune_stat_roll(
    type INT NOT NULL REFERENCES rune_roll_type(id),
    stat INT NOT NULL REFERENCES rune_stat(id),
    stars INT NOT NULL CHECK(stars BETWEEN 1 AND 6),
    min INT NOT NULL,
    max INT NOT NULL,
    PRIMARY KEY(type, stat, stars)
);

CREATE TABLE key.rune_grind_roll_type(
    id INT NOT NULL PRIMARY KEY,
    ancient INT NOT NULL CHECK(ancient IN (1, 0)),
    initial INT NOT NULL CHECK(initial IN (1, 0)),
    upgrade INT NOT NULL CHECK(upgrade IN (1, 0)),
    description TEXT
);

CREATE TABLE key.rune_grind_roll(
    type INT NOT NULL REFERENCES rune_grind_roll_type(id),
    stat INT NOT NULL REFERENCES rune_stat(id),
    quality INT NOT NULL REFERENCES quality(id),
    min INT NOT NULL,
    max INT NOT NULL,
    PRIMARY KEY(type, stat, quality)
);