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 TEXT, -- TODO
    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,
    attribute INT, -- TODO: Element?
    source INT REFERENCES k_source(id),
    create_time DATETIME NOT NULL,
    homunculus INT NOT NULL CHECK(homunculus IN (0, 1)),
    homunculus_name TEXT,
    lock INT NOT NULL CHECK(homunculus 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),
    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)
);