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)
);
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 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(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)
);
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 rune_craft(
    uid INT NOT NULL REFERENCES player(id),
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL CHECK(type BETWEEN 1 AND 4),
    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_party(
    run INT NOT NULL REFERENCES run(id),
    unit INT NOT NULL REFERENCES unit(id),
    k_unit INT NOT NULL REFERENCES k_unit(id),
    PRIMARY KEY (run, unit)
);
CREATE TABLE run_drop_rune_craft(
    run INT NOT NULL REFERENCES run(id),
    id INT NOT NULL PRIMARY KEY,
    type INT NOT NULL REFERENCES k_rune_craft_type(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 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))
);
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),
    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)
);
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)
);