drop table if exists servers;
create table servers (
   id integer primary key autoincrement,
   name char(15) not null,
   host char(15) not null,
   port smallint not null,
   -- 0: MAINTENANCE
   -- 1: GOOD
   -- 2: NORMAL
   -- 3: OVERLOADED
   -- 4: QUEUE
   status tinyint not null check (status <= 4)
);

insert into servers values (null, "login", "84.248.5.167", 10000, 1);
insert into servers values (null, "BENIS", "84.248.5.167", 10100, 1);
insert into servers values (null, "maintenance", "84.248.5.167", 10100, 0);
insert into servers values (null, "normal", "84.248.5.167", 10100, 2);
insert into servers values (null, "overloaded", "84.248.5.167", 10100, 3);
insert into servers values (null, "queue", "84.248.5.167", 10100, 4);

drop table if exists accounts;
create table accounts (
   id integer primary key autoincrement,
   username varchar(16) not null,
   password varchar(64) not null,
   email varchar(64) not null,
   last_login datetime default null,
   created datetime not null default current_timestamp
);

insert into accounts values (null, "benis", "benis", "", null, current_timestamp);

drop table if exists sessions;
create table sessions (
   id integer primary key not null check (id < 0xffffffff),
   account_id integer not null,
   char_id integer,
   server char(15) not null,
   created datetime not null default current_timestamp
);

drop table if exists characters;
create table characters (
   id integer primary key autoincrement check (id < 0xffffffff),
   account_id integer not null,
   last_login datetime,
   created datetime not null default current_timestamp,
   -- 1: Haru
   -- 2: Ed
   -- 3: Lily
   -- 4: Jin
   -- 5: Stella
   -- 6: Iris
   -- 7: Chi
   model tinyint not null check (id > 0 and id <= 7),
   name char(12) not null,
   evolution tinyint not null default 0,
   lvl tinyint not null default 1 check (lvl > 0)
);

insert into characters values (null, 1, null, current_timestamp, 6, "Iris", 1, 255);
insert into characters values (null, 1, null, current_timestamp, 3, "Lily", 1, 255);