CREATE VIEW memorin.zoned_devices_logs AS
    SELECT
    device_serial,
    version_id,
    zone_id,
    latitude AS lat,
    longitude AS long,
    temperature AS temp,
    battery_percentage AS battery,
    signal_strength
    FROM
    memorin.device_logs AS logs
    WHERE
    device_serial IN (
        SELECT serial_number
        FROM memorin.devices
        WHERE deactivated_at IS NULL
    )
    AND version_id IN (
        SELECT id
        FROM memorin.device_versions
        WHERE id = logs.version_id
    )
    AND temperature BETWEEN 34 AND 45
    AND signal_strength BETWEEN 0 AND 5
    AND battery_percentage BETWEEN 0 AND 100
    AND longitude BETWEEN (
        SELECT min_longitude
        FROM memorin.geographic_zones
        WHERE id = logs.zone_id
    )
    AND (
        SELECT max_longitude
        FROM memorin.geographic_zones
        WHERE id = logs.zone_id
    )
    AND latitude BETWEEN (
        SELECT min_latitude
        FROM memorin.geographic_zones
        WHERE id = logs.zone_id
    )
    AND (
        SELECT max_latitude
        FROM memorin.geographic_zones
        WHERE id = logs.zone_id
    )
    WITH CHECK OPTION;