CREATE TABLE IF NOT EXISTS devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 provider VARCHAR(30) NOT NULL DEFAULT '',
 receiver VARCHAR(50) NOT NULL DEFAULT '',
 method ENUM('personal','merchant') NOT NULL DEFAULT 'personal',
 label VARCHAR(100) NOT NULL,
 key_hash CHAR(64) NOT NULL UNIQUE,
 active TINYINT NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX ix_device_client(client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS device_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 device_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 event_id VARCHAR(120) NOT NULL,
 trx_id VARCHAR(150) NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 receiver VARCHAR(50) NOT NULL,
 provider VARCHAR(50) NOT NULL,
 order_ref VARCHAR(120) NOT NULL DEFAULT '',
 method ENUM('personal','merchant') NOT NULL,
 payload_hash CHAR(64) NOT NULL,
 sms_at DATETIME NULL,
 validated_sms TINYINT NOT NULL DEFAULT 0,
 received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_device_event(device_id,event_id),
 INDEX ix_event_client(client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;