CREATE DATABASE IF NOT EXISTS benchmark;
USE benchmark;

create user 'benchmark'@'%' identified by 'benchmark';
grant all privileges on benchmark.* to 'benchmark'@'%';
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS device_message (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message BLOB(1500) NOT NULL,
    is_imei INT DEFAULT 0,
    IMEI varchar(20),
    received_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_device_message_received_time
    ON device_message (received_time);

CREATE TABLE IF NOT EXISTS device (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Device_id varchar(20) NOT NULL,
    IMEI varchar(20) NOT NULL,
    ICCID varchar(50),
    CUSTOMER_ID varchar(50),
    LAST_MESSAGE_RECEIVED_TIME TIMESTAMP DEFAULT NULL,
    IS_AVAILABLE INT DEFAULT 0,
    IMPORTED INT DEFAULT 1,
    UNIQUE(IMEI)
);

CREATE TABLE IF NOT EXISTS device_tenant_id (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id varchar(50) NOT NULL,
    IMEI varchar(20) NOT NULL,
    device_name varchar(200),
    UNIQUE(IMEI, tenant_id)
);

CREATE TABLE IF NOT EXISTS sent_messages (
    msg_id BIGINT UNSIGNED,
    message_type INT,
    message varchar(15000),
    message_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    device_id BIGINT UNSIGNED
);

CREATE INDEX idx_sent_messages_message_timestamp
    ON sent_messages (message_timestamp);

CREATE INDEX idx_sent_messages_device_id
    ON sent_messages (device_id);