discord-bot/db/migrations/1_setup.sql

-- setup initial tables 

-- this handles the users "floating" account
CREATE TABLE IF NOT EXISTS wolves (
    id_wolves integer PRIMARY KEY,
    email     text    not null,
    discord   integer,
    minecraft text
);
CREATE INDEX IF NOT EXISTS index_discord ON wolves (discord);

-- used to verify the users email address
CREATE TABLE IF NOT EXISTS wolves_verify (
    discord     integer PRIMARY KEY,
    email       text    not null,
    auth_code   text    not null,
    date_expiry text    not null
);
CREATE INDEX IF NOT EXISTS index_date_expiry ON wolves_verify (date_expiry);

-- information on teh server the bot is registered on
CREATE TABLE IF NOT EXISTS servers (
    server          integer PRIMARY KEY,
    wolves_api      text    not null,
    role_past       integer,
    role_current    integer,
    member_past     integer DEFAULT 0,
    member_current  integer DEFAULT 0
);

-- keep track of the members on the server
CREATE TABLE IF NOT EXISTS server_members (
    server    integer not null,
    id_wolves integer not null,
    expiry    text    not null,
    PRIMARY KEY(server,id_wolves),
    FOREIGN KEY (id_wolves) REFERENCES wolves (id_wolves)
);