CREATE TABLE nameservers
(
    nameserver_id INTEGER NOT NULL
        CONSTRAINT nameservers_pk
            PRIMARY KEY autoincrement,
    address text NOT NULL,
    port INT NOT NULL
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX nameservers_nameserver_id_uindex
    ON nameservers (nameserver_id);
CREATE TABLE users
(
    user_id INTEGER NOT NULL
        CONSTRAINT users_pk
            PRIMARY KEY autoincrement,
    name INT NOT NULL,
    nameserver_id INTEGER
        CONSTRAINT users_nameservers_nameserver_id_fk
            REFERENCES nameservers
);
CREATE UNIQUE INDEX users_user_id_uindex
    ON users (user_id);
CREATE TABLE KEYS
(
    key_pair_id INTEGER NOT NULL
        CONSTRAINT keys_pk
            PRIMARY KEY autoincrement,
    pubkey text NOT NULL,
    privkey text NOT NULL,
    nameserver_id INTEGER NOT NULL
        CONSTRAINT keys_nameservers_nameserver_id_fk
            REFERENCES nameservers,
    user_id INTEGER NOT NULL
        CONSTRAINT keys_users_user_id_fk
            REFERENCES users
);
CREATE UNIQUE INDEX keys_key_pair_id_uindex
    ON KEYS (key_pair_id);
CREATE TABLE IF NOT EXISTS "sessions"
(
    session_id string NOT NULL
        CONSTRAINT sessions_pk
            PRIMARY KEY,
    KEY text NOT NULL,
    nameserver_id INTEGER NOT NULL
        REFERENCES nameservers,
    user_id INTEGER NOT NULL
        REFERENCES users
);
CREATE UNIQUE INDEX sessions_session_id_uindex
    ON sessions (session_id);
CREATE TABLE IF NOT EXISTS "chats"
(
    chat_id INTEGER NOT NULL
        CONSTRAINT chats_pk
            PRIMARY KEY autoincrement,
    initiator_id INTEGER NOT NULL,
    consolidator_id INTEGER NOT NULL,
    KEY text NOT NULL,
    accepted BOOLEAN DEFAULT FALSE NOT NULL,
    last_seen_message_id INTEGER DEFAULT 0 NOT NULL,
    nameserver_id INTEGER NOT NULL
        REFERENCES nameservers,
    user_id INTEGER NOT NULL
        CONSTRAINT chats_users_user_id_fk
            REFERENCES users
);
CREATE UNIQUE INDEX chats_chat_id_uindex
    ON chats (chat_id);