CREATE TABLE IF NOT EXISTS User (
        id INTEGER PRIMARY KEY,
        username TEXT NOT NULL UNIQUE,
        password TEXT,
        admin INTEGER NOT NULL DEFAULT 0,
        realname TEXT
);
CREATE TABLE IF NOT EXISTS Network (
        id INTEGER PRIMARY KEY,
        name TEXT,
        user INTEGER NOT NULL,
        addr TEXT NOT NULL,
        nick TEXT,
        username TEXT,
        realname TEXT,
        pass TEXT,
        connect_commands TEXT,
        sasl_mechanism TEXT,
        sasl_plain_username TEXT,
        sasl_plain_password TEXT,
        sasl_external_cert BLOB,
        sasl_external_key BLOB,
        enabled INTEGER NOT NULL DEFAULT 1,
        FOREIGN KEY(user) REFERENCES User(id),
        UNIQUE(user, addr, nick),
        UNIQUE(user, name)
);
CREATE TABLE IF NOT EXISTS Channel (
        id INTEGER PRIMARY KEY,
        network INTEGER NOT NULL,
        name TEXT NOT NULL,
        key TEXT,
        detached INTEGER NOT NULL DEFAULT 0,
        detached_internal_msgid TEXT,
        relay_detached INTEGER NOT NULL DEFAULT 0,
        reattach_on INTEGER NOT NULL DEFAULT 0,
        detach_after INTEGER NOT NULL DEFAULT 0,
        detach_on INTEGER NOT NULL DEFAULT 0,
        FOREIGN KEY(network) REFERENCES Network(id),
        UNIQUE(network, name)
);

CREATE TABLE IF NOT EXISTS DeliveryReceipt (
        id INTEGER PRIMARY KEY,
        network INTEGER NOT NULL,
        target TEXT NOT NULL,
        client TEXT,
        internal_msgid TEXT NOT NULL,
        FOREIGN KEY(network) REFERENCES Network(id),
        UNIQUE(network, target, client)
);

CREATE TABLE IF NOT EXISTS ReadReceipt (
        id INTEGER PRIMARY KEY,
        network INTEGER NOT NULL,
        target TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        FOREIGN KEY(network) REFERENCES Network(id),
        UNIQUE(network, target)
);

