CREATE TABLE IF NOT EXISTS servers (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    host TEXT NOT NULL UNIQUE,
    port SMALLINT UNSIGNED NOT NULL,
    nick TEXT,
    pass TEXT,
    capabilities TEXT,
    added_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
    UNIQUE (host, port)
);

CREATE TABLE IF NOT EXISTS rooms (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    `name` TEXT NOT NULL UNIQUE,
    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
    departed_at TIMESTAMP,
    INDEX (`name`),
    INDEX (id, server_id)
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
    nick TEXT NOT NULL,
    joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
    departed_at TIMESTAMP,
    INDEX (nick),
    INDEX (server_id)
);

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    command VARCHAR(32) NOT NULL,
    
    params TEXT,
    tags TEXT,

    sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
    INDEX (room_id),
    INDEX (user_id),
    INDEX (command),
    INDEX (room_id, sent_at),
    FULLTEXT INDEX ft_message (params)
);