sqlite3 /opt/spider/db/events.db <<EOF
-- Introduced in v2.1.0.
CREATE TABLE IF NOT EXISTS smtp (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    ip              TEXT NOT NULL DEFAULT '',

    -- SMTP Reply.
    reply           TEXT NOT NULL DEFAULT '',

    -- Reject or retry reason.
    reason          TEXT NOT NULL DEFAULT '',

    -- Extra information
    msg             TEXT NOT NULL DEFAULT '',

    huid            TEXT NOT NULL DEFAULT '',
    message_id      TEXT NOT NULL DEFAULT '',
    epoch           INTEGER NOT NULL DEFAULT 0
) STRICT;

CREATE INDEX IF NOT EXISTS idx_smtp_ip ON smtp (ip);
CREATE INDEX IF NOT EXISTS idx_smtp_reply ON smtp (reply);
CREATE INDEX IF NOT EXISTS idx_smtp_reason ON smtp (reason);
CREATE INDEX IF NOT EXISTS idx_smtp_huid ON smtp (huid);
CREATE INDEX IF NOT EXISTS idx_smtp_message_id ON smtp (message_id);
CREATE INDEX IF NOT EXISTS idx_smtp_epoch ON smtp (epoch);
EOF