-- noinspection SqlNoDataSourceInspectionForFile

-- Chats ----------------------------------
CREATE TABLE IF NOT EXISTS chats
(
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    account       TEXT    NOT NULL COLLATE nocase,
    jid           TEXT    NOT NULL COLLATE nocase,
    type          INTEGER NOT NULL,
    timestamp     INTEGER,
    thread_id     TEXT,
    resource      TEXT,
    nickname      TEXT,
    password      TEXT,
    room_state    INTEGER,
    message_draft TEXT,
    name          TEXT,
    encryption    TEXT,
    options       TEXT
);

CREATE INDEX IF NOT EXISTS chat_jid_idx
    ON chats (jid, account);

CREATE TABLE IF NOT EXISTS chat_history
(
    id                 INTEGER PRIMARY KEY AUTOINCREMENT,
    account            TEXT NOT NULL COLLATE nocase,
    jid                TEXT NOT NULL COLLATE nocase,
    author_jid         TEXT COLLATE nocase,
    author_nickname    TEXT,
    timestamp          INTEGER,
    item_type          INTEGER,
    data               TEXT,
    stanza_id          TEXT,
    state              INTEGER,
    preview            TEXT,
    error              TEXT,
    encryption         TEXT,
    fingerprint        TEXT,
    appendix           TEXT,
    recipient_nickname TEXT,
    server_msg_id      TEXT,
    remote_msg_id      TEXT,
    participant_id     TEXT
);

CREATE INDEX IF NOT EXISTS chat_history_jid_idx
    ON chats (jid, account);

CREATE INDEX IF NOT EXISTS chat_history_account_jid_timestamp_idx
    ON chat_history (account, jid, timestamp);

CREATE INDEX IF NOT EXISTS chat_history_account_jid_state_idx
    ON chat_history (account, jid, state);

CREATE INDEX IF NOT EXISTS chat_history_account_jid_server_msg_id_idx
    ON chat_history (account, server_msg_id);

CREATE TABLE IF NOT EXISTS chats_read
(
    account   TEXT NOT NULL COLLATE NOCASE,
    jid       TEXT NOT NULL COLLATE NOCASE,
    timestamp INTEGER,

    UNIQUE (account, jid)
);


-- Roster -----------------------------------
CREATE TABLE IF NOT EXISTS roster_items
(
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    account      TEXT NOT NULL COLLATE nocase,
    jid          TEXT NOT NULL COLLATE nocase,
    name         TEXT,
    subscription TEXT,
    timestamp    INTEGER,
    ask          INTEGER,
    annotations  TEXT,
    nickname     TEXT
);

CREATE TABLE IF NOT EXISTS roster_groups
(
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

CREATE TABLE IF NOT EXISTS roster_items_groups
(
    item_id  INTEGER NOT NULL,
    group_id INTEGER NOT NULL,
    FOREIGN KEY (item_id) REFERENCES roster_items (id),
    FOREIGN KEY (group_id) REFERENCES roster_groups (id)
);

CREATE INDEX IF NOT EXISTS roster_item_jid_idx
    ON roster_items (jid, account);

CREATE INDEX IF NOT EXISTS roster_item_groups_item_id_idx
    ON roster_items_groups (item_id);
CREATE INDEX IF NOT EXISTS roster_item_groups_group_id_idx
    ON roster_items_groups (group_id);


-- VCards -------------------------------------
CREATE TABLE IF NOT EXISTS vcards_cache
(
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    jid       TEXT NOT NULL COLLATE nocase,
    data      TEXT,
    timestamp INTEGER
);

CREATE INDEX IF NOT EXISTS vcards_cache_jid_idx
    ON vcards_cache (jid);


-- Caps ----------------------------------------
CREATE TABLE IF NOT EXISTS caps_features
(
    node    TEXT NOT NULL,
    feature TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS caps_features_node_idx
    ON caps_features (node);

CREATE INDEX IF NOT EXISTS caps_features_feature_idx
    ON caps_features (feature);

CREATE TABLE IF NOT EXISTS caps_identities
(
    node     TEXT NOT NULL,
    name     TEXT,
    type     TEXT,
    category TEXT
);

CREATE INDEX IF NOT EXISTS caps_indentities_node_idx
    ON caps_identities (node);

CREATE TABLE IF NOT EXISTS avatars_cache
(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    jid     TEXT NOT NULL COLLATE nocase,
    account TEXT NOT NULL COLLATE nocase,
    hash    TEXT NOT NULL,
    type    TEXT NOT NULL
);


-- Avatars -------------------------------------
CREATE INDEX IF NOT EXISTS avatars_cache_jid_idx
    ON avatars_cache (jid);

CREATE TABLE IF NOT EXISTS omemo_sessions
(
    account   TEXT    NOT NULL COLLATE NOCASE,
    name      TEXT    NOT NULL,
    device_id INTEGER NOT NULL,
    key       TEXT    NOT NULL,

    UNIQUE (account, name, device_id) ON CONFLICT REPLACE
);


-- OMEMO ---------------------------------------
CREATE TABLE IF NOT EXISTS omemo_identities
(
    account     TEXT    NOT NULL COLLATE NOCASE,
    name        TEXT    NOT NULL,
    device_id   INTEGER NOT NULL,
    fingerprint TEXT    NOT NULL,
    key         BLOB    NOT NULL,
    own         INTEGER NOT NULL,
    status      INTEGER NOT NULL,

    UNIQUE (account, name, fingerprint) ON CONFLICT IGNORE
);

CREATE TABLE IF NOT EXISTS omemo_pre_keys
(
    account TEXT    NOT NULL COLLATE NOCASE,
    id      INTEGER NOT NULL,
    key     BLOB    NOT NULL,

    UNIQUE (account, id) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS omemo_signed_pre_keys
(
    account TEXT    NOT NULL COLLATE NOCASE,
    id      INTEGER NOT NULL,
    key     BLOB    NOT NULL,

    UNIQUE (account, id) ON CONFLICT REPLACE
);
