--
-- init.sql -- initialize sqlite database
--
-- Copyright (c) 2013-2017 Malikania Authors
--
-- Permission to use, copy, modify, and/or distribute this software for any
-- purpose with or without fee is hereby granted, provided that the above
-- copyright notice and this permission notice appear in all copies.
--
-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
-- ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
-- ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
-- OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
--

create table if not exists mk_info(
    if_id varchar(32),
    if_value varchar(256)
);

create table if not exists mk_account(
    ac_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ac_name varchar(32),
    ac_email varchar(128),
    ac_firstname varchar(32),
    ac_lastname varchar(32),
    ac_salt varchar(8),
    ac_password varchar(256),
    ac_joindate date
);

create table if not exists mk_log_history(
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    log_ac_id integer,
    FOREIGN KEY(log_ac_id) REFERENCES mk_account(ac_id)
);

create table if not exists mk_character(
    ch_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ch_ac_id integer,
    ch_class varchar(32),
    ch_createdate date,
    FOREIGN KEY(ch_ac_id) REFERENCES mk_account(ac_id)
);

create table if not exists mk_log_history_item(
    lhi_id INTEGER PRIMARY KEY AUTOINCREMENT,
    lhi_log_id integer,
    lhi_date time,
    FOREIGN KEY(lhi_log_id) REFERENCES mk_log_history(log_id)
);

create table if not exists mk_status(
    st_id INTEGER PRIMARY KEY AUTOINCREMENT,
    st_ch_id integer,
    st_hp int,
    st_mp int,
    FOREIGN KEY(st_ch_id) REFERENCES mk_character(ch_id)
);

create table if not exists mk_points(
    pt_id INTEGER PRIMARY KEY AUTOINCREMENT,
    pt_st_id integer,
    pt_avail smallint,
    pt_force smallint,
    pt_defense smallint,
    pt_agility smallint,
    FOREIGN KEY(pt_st_id) REFERENCES mk_status(st_id)
);

create table if not exists mk_build(
    bd_id INTEGER PRIMARY KEY AUTOINCREMENT,
    bd_ac_id integer,
    bd_position int,
    bd_title varchar(32),
    bd_password varchar(25),
    bd_kind varchar(32),
    FOREIGN KEY(bd_ac_id) REFERENCES mk_account(ac_id)
);

create table if not exists mk_chest(
    cht_id INTEGER PRIMARY KEY AUTOINCREMENT,
    cht_bd_id integer,
    cht_name varchar(32),
    FOREIGN KEY(cht_bd_id) REFERENCES mk_build(bd_id)
);

create table if not exists mk_inventory(
    inv_id INTEGER PRIMARY KEY AUTOINCREMENT,
    inv_ch_id integer,
    FOREIGN KEY(inv_ch_id) REFERENCES mk_character(ch_id)
);

create table if not exists mk_artefact(
    atf_id INTEGER PRIMARY KEY AUTOINCREMENT,
    atf_ch_id integer,
    atf_name varchar(32),
    FOREIGN KEY(atf_ch_id) REFERENCES mk_character(ch_id)
);

create table if not exists mk_inventory_object(
    invo_id INTEGER PRIMARY KEY AUTOINCREMENT,
    invo_inv_id integer,
    invo_name varchar(32),
    invo_count smallint,
    FOREIGN KEY(invo_inv_id) REFERENCES mk_inventory(inv_id)
);

create table if not exists mk_quest(
    qs_id INTEGER PRIMARY KEY AUTOINCREMENT,
    qs_ch_id integer,
    qs_name varchar(32),
    FOREIGN KEY(qs_ch_id) REFERENCES mk_character(ch_id)
);

create table if not exists mk_quest_property(
    qp_id INTEGER PRIMARY KEY AUTOINCREMENT,
    qp_qs_id integer,
    qp_name varchar(32),
    qp_value text,
    FOREIGN KEY(qp_qs_id) REFERENCES mk_quest(qs_id)
);