CREATE TABLE IF NOT EXISTS devices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    nickname VARCHAR(100) DEFAULT NULL,
    site_id VARCHAR(50) DEFAULT NULL,
    user_ip VARCHAR(64) DEFAULT NULL,
    fingerprint VARCHAR(255) DEFAULT NULL,
    first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_by VARCHAR(64) DEFAULT NULL,
    UNIQUE KEY uq_devices_device_id (device_id),
    KEY idx_devices_site_id (site_id),
    KEY idx_devices_last_seen_at (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_bans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(128) NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    banned_by VARCHAR(100) DEFAULT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_device_bans_device_id (device_id),
    KEY idx_device_bans_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

