-- Jalankan HANYA pada cwwrxvka_mahaelangmobile, tidak pada database website.
-- Backup dahulu. Tidak ada perubahan pada table_user/table_outlet website.
CREATE TABLE IF NOT EXISTS mobile_auth_session (
 id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
 user_id INT NOT NULL,
 channel VARCHAR(16) NOT NULL,
 device_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 created_at DATETIME NOT NULL,
 last_seen_at DATETIME NOT NULL,
 expires_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 KEY session_user (user_id,channel,device_hash),
 KEY session_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS mobile_auth_login_log (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 user_id INT NULL,
 session_id CHAR(32) NULL,
 channel VARCHAR(16) NOT NULL,
 event VARCHAR(32) NOT NULL,
 username_snapshot VARCHAR(255) NOT NULL,
 device_hash CHAR(64) NULL,
 platform VARCHAR(32) NOT NULL,
 device_model VARCHAR(160) NOT NULL,
 os_version VARCHAR(80) NOT NULL,
 ip_address VARCHAR(45) NOT NULL,
 user_agent VARCHAR(512) NOT NULL,
 occurred_at DATETIME NOT NULL,
 KEY log_user_time (user_id,occurred_at),
 KEY log_device_time (device_hash,occurred_at),
 KEY log_event_time (event,occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS mobile_auth_rate (
 bucket CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
 attempts INT NOT NULL,
 window_started DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
