User Details Store Structure MySQL

CREATE TABLE `user_device_info` (
    `device_id` INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for the device
    `user_id` INT NOT NULL,                      -- Foreign key to link this device to a specific user
    `device_model` VARCHAR(255) COMMENT 'The model of the device (e.g., Pixel 6, iPhone 13)',  -- The model of the device
    `internal_model_number` VARCHAR(50) COMMENT 'The internal model number assigned by the manufacturer (e.g., CPH2591)',
    `manufacturer` VARCHAR(255) COMMENT 'Manufacturer of the device (e.g., Google, Apple)',  -- Manufacturer of the device
    `brand` VARCHAR(255) COMMENT 'Brand of the device (e.g., Samsung, Google)',  -- Brand of the device
    `os_version` VARCHAR(50) COMMENT 'The operating system version (e.g., Android 13, iOS 16)',  -- The OS version
    `api_level` INT COMMENT 'The API level of the OS (e.g., API 30 for Android 11). For iOS, store the iOS version.',  -- The OS version level (Android API or iOS version)
    `os_build_number` VARCHAR(255) COMMENT 'Build number of the operating system (e.g., build ID of Android)',  -- OS build number
    `kernel_version` VARCHAR(255) COMMENT 'The kernel version of the device (e.g., Linux kernel 5.4)',  -- Kernel version of the OS
    `time_zone` VARCHAR(100) COMMENT 'Time zone of the device (e.g., UTC, GMT+5)',  -- Time zone of the device
    `language` VARCHAR(50) COMMENT 'Language set on the device (e.g., English, Spanish)',  -- Language set on the device

    `total_ram` BIGINT COMMENT 'Total RAM available on the device (in bytes)',  -- Total RAM of the device
    `available_ram` BIGINT COMMENT 'Amount of available RAM at the current time (in bytes)',  -- Available RAM
    `total_storage` BIGINT COMMENT 'Total storage capacity of the device (in bytes)',  -- Total storage capacity of the device
    `available_storage` BIGINT COMMENT 'Amount of available storage at the current time (in bytes)',  -- Available storage space

    `ip_address` VARCHAR(45) COMMENT 'IP address of the device (supports both IPv4 and IPv6)',  -- Device IP address
    `wifi_network_name` VARCHAR(255) COMMENT 'The name of the Wi-Fi network to which the device is connected (SSID)',  -- SSID of connected Wi-Fi
    `mobile_network` VARCHAR(50) COMMENT 'Type of mobile network (e.g., 4G, 5G, LTE)',  -- Type of mobile network
    `bluetooth_status` BOOLEAN COMMENT 'Whether Bluetooth is enabled or not (TRUE/FALSE)',  -- Bluetooth status

    `battery_level` INT COMMENT 'The current battery level of the device (percentage)',  -- Battery level percentage
    `battery_status` VARCHAR(50) COMMENT 'The status of the battery (e.g., charging, discharging, full)',  -- Battery charging status
    `battery_health` VARCHAR(50) COMMENT 'The health of the battery (e.g., good, poor)',  -- Battery health status

    `device_modifications` VARCHAR(50) COMMENT 'Whether the device is rooted (Android) or jailbroken (iPhone)',  -- Whether the device is rooted (Android) or jailbroken (iPhone)
    `device_encryption` BOOLEAN COMMENT 'Whether the device\'s storage is encrypted (TRUE/FALSE)',  -- Encryption status of the device
    `security_patch_level` DATE COMMENT 'Date of the latest security patch (e.g., 2023-11-01). For iPhone, store the date of the last iOS update.',  -- Last security patch date for Android or last iOS update for iPhone

    `screen_resolution` VARCHAR(50) COMMENT 'The resolution of the device\'s display (e.g., 1080x2400)',  -- Screen resolution (width x height)
    `screen_density` INT COMMENT 'Screen density (DPI) of the display (e.g., 320 DPI)',  -- Screen density in DPI
    `screen_size` VARCHAR(50) COMMENT 'Physical screen size (in inches) (e.g., 6.5 inches)',  -- Screen size in inches
    `orientation` VARCHAR(20) COMMENT 'Orientation of the screen (e.g., portrait, landscape)',  -- Screen orientation

    `sensors_available` TEXT COMMENT 'List of sensors available on the device (e.g., accelerometer, gyroscope). These may differ between Android and iOS.',  -- Sensors available on the device
    `camera_info` TEXT COMMENT 'Camera specifications (e.g., 12MP rear, 8MP front). These can vary across devices.',  -- Camera information (Android and iOS)
    `gps_status` BOOLEAN COMMENT 'Whether GPS is enabled on the device (TRUE/FALSE)',  -- GPS status

    `app_version` VARCHAR(50) COMMENT 'The version of your app installed on the device (e.g., v1.2.3)',  -- Version of the app installed
    `last_app_update_date` DATE COMMENT 'The date the app was last updated on the device (e.g., 2023-12-01)',  -- Last app update date

    `device_logs` TEXT COMMENT 'Optional: Logs for troubleshooting purposes (use with caution)',  -- Device logs (if needed for debugging)
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp when the record was created',  -- Record creation timestamp
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Timestamp when the record was last updated'  -- Record update timestamp
);