WITH base AS (
    SELECT
        JSON_EXTRACT(logicaltimestamp_json, '$.clockIdentifier') AS logic_clock_id,
        JSON_EXTRACT(logicaltimestamp_json, '$.timestampInNanoseconds') AS logical_timestamp_ns,
        JSON_EXTRACT(clusterrepresentativeid_json, '$.uuid') AS cluster_id,
        LOOKUP_ANY_EVENT_TYPE(anyeventtype) AS component_name,
        LOOKUP_INNER_TYPE(anyeventtype, innertype) AS event_name,
        anyeventtype,
        eventdata,
        mobileSoftwareUpdateInstallTimestamp() AS software_update_install_timestamp
    FROM SiriSELFProcessedEvent
),

class_c_unlock_events AS (
    SELECT eventTimestamp
    FROM "Device.KeybagLocked"
    WHERE starting = 0
),

boot_session_events AS (
    SELECT eventTimestamp
    FROM "Device.BootSession"
    WHERE starting = 1
),

-- DeviceFixedContext (DFC)/ephemeral map anchored by logical clock; provides device dims
-- and the min logical timestamp per clock to reconstruct wall-clock event times
global_dim_agg AS (
    SELECT
        logic_clock_id AS dfc_logic_clock_id,
        MIN(logical_timestamp_ns) AS min_clock_ns,
        software_update_install_timestamp,
        IIF(event_name = 'deviceFixedContext', DATETIME(P_GET(eventdata, anyeventtype, 'deviceFixedContext.timeIntervalSince1970'), 'unixepoch'), NULL) AS event_timestamp,
        MAX(IIF(event_name = 'ephemeralToAggregationIdentifierMap', P_GET(eventdata, anyeventtype, 'ephemeralToAggregationIdentifierMap.deviceAggregationId.value'), NULL)) AS device_aggregation_id,
        MIN(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.timeIntervalSince1970'), NULL)) AS time_interval_since1970,
        MAX(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.deviceType'), NULL)) AS device_type,
        MAX(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.systemBuild'), NULL)) AS system_build,
        MAX(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.programCode'), NULL)) AS program_code,
        MAX(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.dataSharingOptInState'), NULL)) AS data_sharing_opt_in_state,
        MAX(IIF(event_name = 'deviceFixedContext', P_GET(eventdata, anyeventtype, 'deviceFixedContext.siriInputLocale'), NULL)) AS siri_input_locale
    FROM base
    WHERE component_name = 'DIM_CLIENT_EVENT'
    GROUP BY dfc_logic_clock_id
),

filtered_request_event AS (
    SELECT
        *,
        IIF(component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'requestContext', P_JSON(eventdata, anyeventtype), NULL) AS payload
    FROM base
    WHERE component_name = 'ORCH_CLIENT_EVENT' AND event_name = 'requestContext' AND eventdata IS NOT NULL
),

-- Reconstruct request wall-clock time using DFC anchor + logical ns
include_device_dims_with_requests AS (
    SELECT
        r.component_name,
        r.event_name,
        r.payload,
        r.cluster_id,
        g.device_aggregation_id,
        DATETIME(g.time_interval_since1970 + (r.logical_timestamp_ns - g.min_clock_ns) / 1000000000.0, 'unixepoch') AS event_timestamp,
        g.software_update_install_timestamp,
        g.dfc_logic_clock_id AS dfc_logic_clock_id,
        g.time_interval_since1970 AS dfc_time_interval_since1970,
        g.min_clock_ns AS dfc_min_clock_ns
    FROM filtered_request_event r
    LEFT JOIN global_dim_agg g ON r.logic_clock_id = g.dfc_logic_clock_id
),

-- Extract request context fields
request_context_base AS (
    SELECT
        d.component_name,
        d.event_name,
        d.payload,
        d.cluster_id,
        d.device_aggregation_id,
        d.event_timestamp,
        d.software_update_install_timestamp,
        d.dfc_logic_clock_id,
        d.dfc_time_interval_since1970,
        d.dfc_min_clock_ns,
        JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.requestType') AS request_type,
        COALESCE(JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.siriNlMode'), 'ORCHSIRINLMODE_UNKNOWN') AS nl_mode,
        COALESCE(JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.siriAsrMode'), 'ORCHSIRIASRMODE_UNKNOWN') AS asr_mode,
        JSON_EXTRACT(d.payload, '$.eventMetadata.requestId.value') AS request_id,
        CASE
            WHEN JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.isAssistantEngineRequest') IN (1, '1', 'true', 'TRUE') THEN 1
            WHEN JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.isAssistantEngineRequest') IN (0, '0', 'false', 'FALSE') THEN 0
            ELSE NULL
        END AS isAssistantEngineRequest
    FROM include_device_dims_with_requests d
    WHERE
        JSON_EXTRACT(d.payload, '$.requestContext.startedOrChanged.requestType') IN ('ORCHREQUESTTYPE_SPEECH','ORCHREQUESTTYPE_TEXT','ORCHREQUESTTYPE_START_LOCAL_REQUEST')
),

-- Filter requests on or after MobileSoftwareUpdate install timestamp
extract_request_context_fields AS (
    SELECT
        d.*,
        d.software_update_install_timestamp AS msu_software_update_install_timestamp,
        NULL AS self_software_update_install_timestamp,
        NULL AS previous_system_build
    FROM request_context_base d
    WHERE
        d.software_update_install_timestamp IS NOT NULL
        AND DATETIME(d.event_timestamp) >= DATETIME(d.software_update_install_timestamp, 'unixepoch')
),

-- SELF build history is used solely for fallback or debugging purposes
self_system_build_history AS (
    SELECT
        system_build,
        MIN(event_timestamp) AS build_change_timestamp
    FROM global_dim_agg
    WHERE system_build IS NOT NULL
    GROUP BY system_build
),

self_build_windows AS (
    SELECT
        system_build,
        build_change_timestamp AS start_ts,
        LEAD(build_change_timestamp) OVER (ORDER BY build_change_timestamp) AS next_ts,
        LAG(system_build) OVER (ORDER BY build_change_timestamp) AS previous_system_build
    FROM self_system_build_history
),

 -- Filter requests by current build if a MobileSoftwareUpdate install timestamp is missing
self_extract_request_context_fields AS (
    SELECT
        d.*,
        NULL AS msu_software_update_install_timestamp,
        CAST(strftime('%s', w.start_ts) AS INTEGER) AS self_software_update_install_timestamp,
        w.previous_system_build
    FROM request_context_base d
    JOIN self_build_windows w
      ON DATETIME(d.event_timestamp) >= DATETIME(w.start_ts)
     AND (w.next_ts IS NULL OR DATETIME(d.event_timestamp) < DATETIME(w.next_ts))
    WHERE
        d.software_update_install_timestamp IS NULL
        AND w.next_ts IS NULL
),

self_first_request_context_per_build AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY self_software_update_install_timestamp
            ORDER BY event_timestamp ASC NULLS LAST
        ) AS request_rank_since_self_build
    FROM self_extract_request_context_fields
),

self_first_request_event AS (
    SELECT
        component_name,
        event_name,
        payload,
        cluster_id,
        device_aggregation_id,
        event_timestamp,
        software_update_install_timestamp,
        msu_software_update_install_timestamp,
        self_software_update_install_timestamp,
        dfc_logic_clock_id,
        dfc_time_interval_since1970,
        dfc_min_clock_ns,
        request_type,
        nl_mode,
        asr_mode,
        request_id,
        isAssistantEngineRequest,
        previous_system_build,
        2 AS source_priority
    FROM self_first_request_context_per_build
    WHERE request_rank_since_self_build = 1
    ORDER BY self_software_update_install_timestamp DESC, event_timestamp ASC NULLS LAST
    LIMIT 1
),

msu_first_request_event AS (
    SELECT
        component_name,
        event_name,
        payload,
        cluster_id,
        device_aggregation_id,
        event_timestamp,
        software_update_install_timestamp,
        msu_software_update_install_timestamp,
        self_software_update_install_timestamp,
        dfc_logic_clock_id,
        dfc_time_interval_since1970,
        dfc_min_clock_ns,
        request_type,
        nl_mode,
        asr_mode,
        request_id,
        isAssistantEngineRequest,
        previous_system_build,
        1 AS source_priority
    FROM extract_request_context_fields
    ORDER BY event_timestamp ASC NULLS LAST
    LIMIT 1
),

resolved_first_request_event AS (
    SELECT *
    FROM msu_first_request_event
    UNION ALL
    SELECT *
    FROM self_first_request_event
    -- Only include SELF-derived requests if there is no MSU-derived request
    WHERE NOT EXISTS (SELECT 1 FROM msu_first_request_event)
),

-- Find the first class C unlock timestamp after the Software Update install timestamp
class_c_unlock_per_request AS (
    SELECT
        e.request_id,
        MIN(c.eventTimestamp) AS class_c_unlock_timestamp
    FROM resolved_first_request_event e
    LEFT JOIN class_c_unlock_events c
    -- If the Software Update install timestamp is null, use the SELF-derived install timestamp
      ON c.eventTimestamp >= COALESCE(
            CAST(e.msu_software_update_install_timestamp AS INTEGER),
            CAST(e.self_software_update_install_timestamp AS INTEGER)
        )
    GROUP BY e.request_id
),
-- Find the most recent boot session timestamp before the Software Update install timestamp
boot_session_per_request AS (
    SELECT
        e.request_id,
        MAX(b.eventTimestamp) AS software_update_boot_timestamp
    FROM resolved_first_request_event e
    LEFT JOIN boot_session_events b
      ON b.eventTimestamp <= COALESCE(
            CAST(e.msu_software_update_install_timestamp AS INTEGER),
            CAST(e.self_software_update_install_timestamp AS INTEGER)
        )
    GROUP BY e.request_id
),

first_request_cluster AS (
    SELECT cluster_id
    FROM resolved_first_request_event
),

-- AudioInputRoute values come from siri/uei/uei_event_schemas.proto; we repurpose them as follows:
--   * AUDIOINPUTROUTE_CARPLAY => ASSISTANTVIEWMODE_CARPLAY
--   * Steering wheel invocations => ASSISTANTVIEWMODE_EYES_FREE
--   * Bluetooth hands-free / Do-AP routes => ASSISTANTVIEWMODE_BLUETOOTH_CAR
--   * Personal accessories (AUDIOINPUTROUTE_APPLE_BLUETOOTH / THIRD_PARTY_BLUETOOTH / SIRI_ACCESSORY) => ASSISTANTVIEWMODE_VOICE_ONLY (HomePods land here)
--   * Everything else (built-in device mic) will fall back later to PROGRAMCODE-driven defaults (e.g. COMPACT for iPhone).
view_mode_signals AS (
    SELECT
        b.cluster_id,
        MAX(
            CASE
                WHEN b.component_name = 'MH_CLIENT_EVENT'
                    AND b.event_name = 'assistantDaemonAudioRecordingContext'
                    AND P_GET(b.eventdata, b.anyeventtype, 'assistantDaemonAudioRecordingContext.startedOrChanged.audioInputRoute') = 'AUDIOINPUTROUTE_CARPLAY'
                THEN 1 ELSE 0 END
        ) AS has_carplay_audio_route,
        MAX(
            CASE
                WHEN b.component_name = 'MH_CLIENT_EVENT'
                    AND b.event_name = 'assistantDaemonAudioRecordingContext'
                    AND P_GET(b.eventdata, b.anyeventtype, 'assistantDaemonAudioRecordingContext.startedOrChanged.audioInputRoute') IN (
                        -- Automotive hands-free profiles
                        'AUDIOINPUTROUTE_APPLE_BLUETOOTH_HANDS_FREE_DEVICE',
                        'AUDIOINPUTROUTE_THIRD_PARTY_BLUETOOTH_HANDS_FREE_DEVICE',
                        'AUDIOINPUTROUTE_APPLE_BLUETOOTH_DO_AP_DEVICE',
                        'AUDIOINPUTROUTE_THIRD_PARTY_BLUETOOTH_DO_AP_DEVICE'
                    )
                THEN 1 ELSE 0 END
        ) AS has_bluetooth_handsfree_route,
        MAX(
            CASE
                WHEN b.component_name = 'MH_CLIENT_EVENT'
                    AND b.event_name = 'assistantDaemonAudioRecordingContext'
                    AND P_GET(b.eventdata, b.anyeventtype, 'assistantDaemonAudioRecordingContext.startedOrChanged.audioInputRoute') IN (
                        -- Personal accessories (AirPods/Beats/etc.)
                        'AUDIOINPUTROUTE_APPLE_BLUETOOTH',
                        'AUDIOINPUTROUTE_THIRD_PARTY_BLUETOOTH',
                        'AUDIOINPUTROUTE_SIRI_ACCESSORY'
                    )
                THEN 1 ELSE 0 END
        ) AS has_personal_bluetooth_route,
        MAX(
            CASE
                WHEN b.component_name = 'UEI_CLIENT_EVENT'
                    AND b.event_name = 'invocation'
                    AND P_GET(b.eventdata, b.anyeventtype, 'invocation.invocationSource') = 'INVOCATIONSOURCE_STEERING_WHEEL_BUTTON'
                THEN 1 ELSE 0 END
        ) AS has_eyes_free_invocation,
        MAX(
            CASE
                WHEN b.component_name = 'UEI_CLIENT_EVENT'
                    AND b.event_name = 'invocation'
                    AND (
                        P_GET(b.eventdata, b.anyeventtype, 'invocation.invocationSource') LIKE '%CARPLAY%'
                        OR P_GET(b.eventdata, b.anyeventtype, 'invocation.carPlayInvocationContext.carPlayConnection') IS NOT NULL
                    )
                THEN 1 ELSE 0 END
        ) AS has_carplay_invocation_signal
    FROM base b
    INNER JOIN first_request_cluster c
      ON b.cluster_id = c.cluster_id
    WHERE b.component_name IN ('MH_CLIENT_EVENT', 'UEI_CLIENT_EVENT')
    GROUP BY b.cluster_id
),

resolved_view_interface AS (
    SELECT
        cluster_id,
        CASE
            WHEN has_carplay_audio_route = 1 OR has_carplay_invocation_signal = 1 THEN 'ASSISTANTVIEWMODE_CARPLAY'
            WHEN has_eyes_free_invocation = 1 THEN 'ASSISTANTVIEWMODE_EYES_FREE'
            WHEN has_bluetooth_handsfree_route = 1 THEN 'ASSISTANTVIEWMODE_BLUETOOTH_CAR'
            WHEN has_personal_bluetooth_route = 1 THEN 'ASSISTANTVIEWMODE_VOICE_ONLY'
            ELSE NULL
        END AS viewInterface
    FROM view_mode_signals
),

filtered_request_fallback_event AS (
    SELECT
        *,
        P_JSON(eventdata, anyeventtype) AS payload
    FROM base
    WHERE component_name = 'ORCH_CLIENT_EVENT'
      AND event_name = 'serverFallbackContext'
      AND eventdata IS NOT NULL
      AND JSON_EXTRACT(P_JSON(eventdata, anyeventtype), '$.serverFallbackContext.startedOrChanged.fallbackReason') = 'ORCHSERVERFALLBACKREASON_MISSING_ASSET'
),

get_server_fallback_events AS (
    SELECT
        JSON_EXTRACT(payload, '$.eventMetadata.requestId.value') AS request_id,
        JSON_EXTRACT(payload, '$.serverFallbackContext.startedOrChanged.missingAsset') AS missing_assets_reason,
        1 AS is_server_fallback
    FROM filtered_request_fallback_event
),
-- Calculate the duration from software update to first request
first_request_metrics AS (
    SELECT
        e.request_type,
        e.device_aggregation_id,
        e.component_name,
        e.event_timestamp,
        c.class_c_unlock_timestamp AS class_c_unlock_timestamp,
        COALESCE(e.msu_software_update_install_timestamp, e.self_software_update_install_timestamp) AS software_update_install_timestamp,
        e.msu_software_update_install_timestamp,
        b.software_update_boot_timestamp AS software_update_boot_timestamp,
        e.self_software_update_install_timestamp,
        e.dfc_logic_clock_id,
        e.request_id,
        e.asr_mode,
        e.nl_mode,
        e.cluster_id,
        e.isAssistantEngineRequest,
        e.previous_system_build,
        COALESCE(f.is_server_fallback, 0) AS is_server_fallback,
        f.missing_assets_reason,
        CAST(
            strftime('%s', e.event_timestamp)
            - COALESCE(
                CAST(e.msu_software_update_install_timestamp AS INTEGER),
                CAST(e.self_software_update_install_timestamp AS INTEGER)
            ) AS INTEGER
        ) AS durationFromSoftwareUpdateToFirstRequestSeconds,
        CASE
            WHEN b.software_update_boot_timestamp IS NULL THEN NULL
            ELSE CAST(strftime('%s', e.event_timestamp) - CAST(b.software_update_boot_timestamp AS INTEGER) AS INTEGER)
        END AS durationFromBootToFirstRequestSeconds,
        CASE
            WHEN class_c_unlock_timestamp IS NULL THEN NULL
            ELSE CAST(strftime('%s', e.event_timestamp) - CAST(class_c_unlock_timestamp AS INTEGER) AS INTEGER)
        END AS durationFromClassCUnlockToFirstRequestSeconds,
        CAST(strftime('%s', e.event_timestamp) AS INTEGER) AS debugRequestFirstTsSec,
        CAST(
            COALESCE(
                CAST(e.msu_software_update_install_timestamp AS INTEGER),
                CAST(e.self_software_update_install_timestamp AS INTEGER)
            ) AS INTEGER
        ) AS debugBuildStartTsSec,
        CAST(b.software_update_boot_timestamp AS INTEGER) AS debugBootSeconds
    FROM resolved_first_request_event e
    LEFT JOIN class_c_unlock_per_request c
      ON e.request_id = c.request_id
    LEFT JOIN get_server_fallback_events f
      ON e.request_id = f.request_id
    LEFT JOIN boot_session_per_request b
      ON e.request_id = b.request_id
),

-- Count requests and server fallback requests
first_request_counts AS (
    SELECT
        device_aggregation_id,
        dfc_logic_clock_id,
        software_update_install_timestamp,
        msu_software_update_install_timestamp,
        self_software_update_install_timestamp,
        DATE(event_timestamp) AS event_date,
        request_type,
        component_name,
        asr_mode,
        nl_mode,
        cluster_id,
        isAssistantEngineRequest,
        previous_system_build,
        durationFromSoftwareUpdateToFirstRequestSeconds,
        durationFromBootToFirstRequestSeconds,
        durationFromClassCUnlockToFirstRequestSeconds,
        debugRequestFirstTsSec,
        debugBuildStartTsSec,
        debugBootSeconds,
        CASE WHEN is_server_fallback THEN 1 ELSE 0 END AS num_server_fallback_requests,
        1 AS num_requests,
        missing_assets_reason
    FROM first_request_metrics
),

sanitized_device_dimensions AS (
    SELECT DISTINCT
        dfc_logic_clock_id,
        device_aggregation_id AS deviceAggregationId,
        device_type AS deviceType,
        system_build AS systemBuild,
        program_code AS programCode,
        data_sharing_opt_in_state AS dataSharingOptInState,
        siri_input_locale AS siriInputLocale,
        DATE(event_timestamp) AS event_date
    FROM global_dim_agg
),

include_device_dimensions AS (
    SELECT
        r.software_update_install_timestamp,
        r.msu_software_update_install_timestamp,
        r.self_software_update_install_timestamp,
        d.deviceType,
        d.systemBuild,
        d.programCode,
        d.dataSharingOptInState,
        d.siriInputLocale,
        r.device_aggregation_id AS deviceAggregationId,
        r.event_date AS eventDate,
        r.request_type AS requestType,
        r.component_name AS componentName,
        r.nl_mode AS nlMode,
        r.asr_mode AS asrMode,
        r.num_requests AS numRequests,
        r.num_server_fallback_requests AS numUnavailableRequests,
        r.missing_assets_reason AS assetFailureReason,
        r.isAssistantEngineRequest,
        r.debugRequestFirstTsSec,
        r.debugBootSeconds,
        CASE
            WHEN v.viewInterface IS NOT NULL THEN v.viewInterface
            -- Infer view interface from device type and program code
            WHEN d.deviceType LIKE 'AudioAccessory%' THEN 'ASSISTANTVIEWMODE_VOICE_ONLY'
            WHEN d.deviceType LIKE 'Watch%' THEN 'ASSISTANTVIEWMODE_VOICE_ONLY'
            WHEN d.deviceType LIKE 'AppleTV%' THEN 'ASSISTANTVIEWMODE_TV'
            WHEN d.programCode = 'PROGRAMCODE_TVOS' THEN 'ASSISTANTVIEWMODE_TV'
            WHEN d.programCode = 'PROGRAMCODE_VISIONOS' THEN 'ASSISTANTVIEWMODE_VISION'
            WHEN d.programCode = 'PROGRAMCODE_WATCHOS' THEN 'ASSISTANTVIEWMODE_VOICE_ONLY'
            WHEN d.programCode IN ('PROGRAMCODE_IOS', 'PROGRAMCODE_MACOS') THEN 'ASSISTANTVIEWMODE_COMPACT'
            ELSE 'ASSISTANTVIEWMODE_UNKNOWN'
        END AS viewInterface,
        r.previous_system_build AS previousSystemBuild,
        CAST(r.software_update_install_timestamp AS INTEGER) AS buildInstallationTimestampInSecondsSince1970,
        r.durationFromSoftwareUpdateToFirstRequestSeconds AS durationFromSoftwareUpdateToFirstRequestSeconds,
        r.durationFromBootToFirstRequestSeconds AS durationFromBootToFirstRequestSeconds,
        r.durationFromClassCUnlockToFirstRequestSeconds AS durationFromClassCUnlockToFirstRequestSeconds
    FROM first_request_counts r
    LEFT JOIN sanitized_device_dimensions d
      ON r.dfc_logic_clock_id = d.dfc_logic_clock_id
    LEFT JOIN resolved_view_interface v
      ON r.cluster_id = v.cluster_id
)
SELECT DISTINCT
    eventDate,
    deviceAggregationId,
    systemBuild,
    deviceType,
    programCode,
    siriInputLocale,
    dataSharingOptInState,
    requestType,
    componentName,
    nlMode,
    asrMode,
    numRequests,
    numUnavailableRequests,
    assetFailureReason,
    viewInterface,
    isAssistantEngineRequest AS isIntelligenceEngineRequest,
    previousSystemBuild AS previousSystemBuild,
    debugRequestFirstTsSec,
    DATETIME(debugRequestFirstTsSec, 'unixepoch') AS debugRequestFirstTsSecUnixEpoch,
    debugBootSeconds,
    DATETIME(debugBootSeconds, 'unixepoch') AS debugBootSecondsUnixEpoch,
    software_update_install_timestamp AS debugBuildStartTsSec,
    buildInstallationTimestampInSecondsSince1970,
    DATETIME(buildInstallationTimestampInSecondsSince1970, 'unixepoch') AS debugBuildStartTsSecUnixEpoch,
    durationFromSoftwareUpdateToFirstRequestSeconds,
    durationFromBootToFirstRequestSeconds,
    durationFromClassCUnlockToFirstRequestSeconds,
    'com.apple.siri.understanding' AS assetSetName
FROM include_device_dimensions
ORDER BY debugRequestFirstTsSec ASC
LIMIT 1