SWDB/install_data/install_data.sql

CREATE TABLE player(
    uid INT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL UNIQUE,
    mail TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    api_key TEXT NOT NULL UNIQUE,
    mana INT NOT NULL CHECK (mana >= 0),
    crystal INT NOT NULL CHECK (crystal >= 0),
    country TEXT,
    lang TEXT,
    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),
    arena_energy_max INT NOT NULL CHECK (arena_energy_max >= 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),
    darkportal_energy_max INT NOT NULL CHECK (darkportal_energy_max >= 0),
    dimension_energy INT NOT NULL CHECK (dimension_energy >= 0),
    dimension_energy_max INT NOT NULL CHECK (dimension_energy_max >= 0),
    costume_point INT NOT NULL CHECK (costume_point >= 0),
    costume_point_max INT NOT NULL CHECK (costume_point_max >= 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,
    public INT NOT NULL DEFAULT 0 CHECK(public IN (0, 1))
);
CREATE TABLE record(
    uid INT NOT NULL REFERENCES player(id),
    area_type INT NOT NULL REFERENCES k_area_type,
    area INT NOT NULL REFERENCES k_area,
    stage INT CHECK(stage >= 0),
    time INT,
    score INT,
    rank TEXT,
    PRIMARY KEY (uid, area_type, area)
);
CREATE TABLE record_party(
    uid INT NOT NULL REFERENCES player(id),
    area_type INT NOT NULL REFERENCES k_area_type,
    area INT NOT NULL REFERENCES k_area,
    unit INT NOT NULL REFERENCES unit(id),
    k_unit INT NOT NULL REFERENCES k_unit(id),
    leader INT NOT NULL DEFAULT 0,
    front INT NOT NULL DEFAULT 0
);
CREATE TABLE collection(
    uid INT NOT NULL REFERENCES player(id),
    unit INT NOT NULL REFERENCES k_unit(id),
    open INT CHECK(open IN (0, 1))
);
CREATE TABLE scenario(
    uid INT NOT NULL REFERENCES player(id),
    region INT NOT NULL REFERENCES k_area(id),
    difficulty INT NOT NULL REFERENCES k_difficulty(id),
    cleared INT CHECK(cleared IN (0, 1)),
    max_cleared INT CHECK(cleared BETWEEN 0 AND 6),
    PRIMARY KEY (uid, region, difficulty, max_cleared)
);
CREATE TABLE defense(
    uid INT NOT NULL REFERENCES player(id),
    unit INT NOT NULL REFERENCES unit(id),
    position INT NOT NULL CHECK(position BETWEEN 1 AND 4),
    PRIMARY KEY (uid, unit)
);
CREATE TABLE gw_defense(
    uid INT NOT NULL REFERENCES player(id),
    unit INT NOT NULL REFERENCES unit(id),
    position INT NOT NULL CHECK(position BETWEEN 1 AND 6),
    PRIMARY KEY (uid, unit)
);
CREATE TABLE unit(
    uid INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    building INT REFERENCES building(id),
    unit 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 k_source(id),
    create_time DATETIME NOT NULL,
    homunculus_name TEXT,
    lock INT NOT NULL CHECK(lock IN (0, 1))
);
CREATE TABLE unit_skill(
    unit INT NOT NULL REFERENCES unit(id),
    skill INT NOT NULL REFERENCES k_skill(id),
    level INT NOT NULL CHECK(level > 0),
    PRIMARY KEY (unit, skill)
);
CREATE TABLE rune(
    uid INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    assigned_to INT REFERENCES unit(id),
    type INT REFERENCES k_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 k_quality(id),
    original_quality INT NOT NULL REFERENCES k_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 k_rune_stat(id),
    main_stat_value INT NOT NULL CHECK(main_stat_value > 0),
    innate_stat INT REFERENCES k_rune_stat(id),
    innate_stat_value INT CHECK(innate_stat_value >= 0),
    substat_1 INT REFERENCES k_rune_stat(id),
    substat_1_value INT CHECK(substat_1_value >= 0),
    substat_1_enchant INT CHECK(substat_1_enchant IN (0, 1)),
    substat_1_grind INT CHECK(substat_1_grind >= 0),
    substat_2 INT REFERENCES k_rune_stat(id),
    substat_2_value INT CHECK(substat_2_value >= 0),
    substat_2_enchant INT CHECK(substat_2_enchant IN (0, 1)),
    substat_2_grind INT CHECK(substat_2_grind >= 0),
    substat_3 INT REFERENCES k_rune_stat(id),
    substat_3_value INT CHECK(substat_3_value >= 0),
    substat_3_enchant INT CHECK(substat_3_enchant IN (0, 1)),
    substat_3_grind INT CHECK(substat_3_grind >= 0),
    substat_4 INT REFERENCES k_rune_stat(id),
    substat_4_value INT CHECK(substat_4_value >= 0),
    substat_4_enchant INT CHECK(substat_4_enchant IN (0, 1)),
    substat_4_grind INT CHECK(substat_4_grind >= 0),
    locked INT NOT NULL CHECK(locked IN (0, 1))
);
CREATE TABLE artifact(
    uid INT NOT NULL REFERENCES player(id),
    id INT PRIMARY KEY NOT NULL,
    assigned_to INT REFERENCES unit(id),
    type INT REFERENCES k_archetype(id),
    attribute INT REFERENCES k_element(id),
    archetype INT REFERENCES k_archetype(id),
    level INT NOT NULL CHECK(level BETWEEN 0 AND 15),
    quality INT NOT NULL REFERENCES k_quality(id),
    original_quality INT NOT NULL REFERENCES k_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 k_stat(id),
    main_stat_value INT NOT NULL CHECK(main_stat_value > 0),
    effect_1 INT REFERENCES k_artiffact_effect(id),
    effect_1_value INT CHECK(effect_1_value >= 0),
    effect_1_enchant INT,
    effect_1_grind INT,
    effect_2 INT REFERENCES k_artiffact_effect(id),
    effect_2_value INT CHECK(effect_2_value >= 0),
    effect_2_enchant INT,
    effect_2_grind INT,
    effect_3 INT REFERENCES k_artiffact_effect(id),
    effect_3_value INT CHECK(effect_3_value >= 0),
    effect_3_enchant INT,
    effect_3_grind INT,
    effect_4 INT REFERENCES k_artiffact_effect(id),
    effect_4_value INT CHECK(effect_4_value >= 0),
    effect_4_enchant INT,
    effect_4_grind INT,
    locked INT NOT NULL CHECK(locked IN (0, 1))
);
CREATE TABLE inventory(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL REFERENCES k_inventory(id),
    type INT NOT NULL REFERENCES k_inventory_type(id),
    amount INT NOT NULL CHECK(amount >= 0)
);
CREATE TABLE summon_special(
    week DATETIME NOT NULL,
    unit INT NOT NULL REFERENCES k_unit(id),
    PRIMARY KEY (week, unit)
);
CREATE TABLE 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 guild_member(
    id 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 enchantment(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL,
    quality INT NOT NULL REFERENCES k_quality(id),
    rune INT NOT NULL REFERENCES k_rune_set(id),
    stat INT NOT NULL REFERENCES k_rune_stat(id),
    value INT NOT NULL CHECK(value >= 0),
    amount INT NOT NULL CHECK(amount >= 0)
);
CREATE TABLE run_party(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES unit(id),
    k_unit INT NOT NULL REFERENCES k_unit(id),
    leader INT NOT NULL DEFAULT 0,
    front INT NOT NULL DEFAULT 0,
    PRIMARY KEY (run, unit)
);
CREATE TABLE run_drop_enchantment(
    run INT NOT NULL REFERENCES run(id),
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL REFERENCES k_enchantment(id), -- TODO: Duplicated??
    quality INT NOT NULL REFERENCES k_quality(id),
    rune INT NOT NULL REFERENCES k_rune_set(id),
    stat INT NOT NULL REFERENCES k_rune_stat(id),
    value INT NOT NULL CHECK(value >= 0)
);
CREATE TABLE run_drop_unit(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES kunit(id),
    PRIMARY KEY (run, unit)
);
CREATE TABLE run_drop_sd(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES kunit(id),
    PRIMARY KEY (run, unit)
);
CREATE TABLE 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 run_drop_shapeshifting(
    run INT NOT NULL PRIMARY KEY REFERENCES run(id),
    amount INT NOT NULL CHECK(amount > 0)
);
CREATE TABLE 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 run(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    dtime TIMESTAMP NOT NULL,
    area_type INT NOT NULL REFERENCES k_area_type,
    area INT NOT NULL REFERENCES k_area,
    stage INT CHECK(stage > 0),
    difficulty INT REFERENCES k_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),
    helper INT NOT NULL CHECK(helper IN (0, 1)),
    score INT,
    rank TEXT
);
CREATE TABLE run_drop_rune(
    run INT NOT NULL REFERENCES run(id),
    id INT NOT NULL PRIMARY KEY REFERENCES rune(id),
    type INT REFERENCES k_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 k_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 k_rune_stat(id),
    main_stat_value INT NOT NULL CHECK(value > 0),
    innate_stat INT REFERENCES k_rune_stat(id),
    innate_stat_value INT CHECK(innate_stat_value >= 0),
    substat_1 INT REFERENCES k_rune_stat(id),
    substat_1_value INT CHECK(substat_1_value >= 0),
    substat_2 INT REFERENCES k_rune_stat(id),
    substat_2_value INT CHECK(substat_2_value >= 0),
    substat_3 INT REFERENCES k_rune_stat(id),
    substat_3_value INT CHECK(substat_3_value >= 0),
    substat_4 INT REFERENCES k_rune_stat(id),
    substat_4_value INT CHECK(substat_4_value >= 0)
);
CREATE TABLE run_drop_item(
    run INT NOT NULL REFERENCES run(id),
    item INT NOT NULL REFERENCES k_inventory(id),
    amount INT NOT NULL CHECK(amount > 0),
    PRIMARY KEY (run, item)
);
CREATE TABLE filter(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    page TEXT NOT NULL,
    name INT NOT NULL,
    description TEXT,
    condition TEXT NOT NULL
);
CREATE TABLE team(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    name INT NOT NULL,
    description TEXT,
    area_type INT NOT NULL REFERENCES k_area_type(id),
    area INT REFERENCES k_area(id),
    stage INT,
    difficulty INT REFERENCES k_difficulty(id),
    score INT NOT NULL DEFAULT 0
);
CREATE TABLE building(
    -- No primary key, can have multiple towers
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL,
    building INT NOT NULL REFERENCES k_building(id),
    gain INT NOT NULL CHECK(gain >= 0)
);
CREATE TABLE decoration(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL,
    decoration INT NOT NULL REFERENCES k_decoration(id),
    level INT NOT NULL CHECK(level > 0),
    PRIMARY KEY (uid, decoration)
);