with base_table as (
    SELECT
        json_extract(logicalTimestamp_json, '$.clockIdentifier') AS logic_clock_id
        ,json_extract(logicalTimestamp_json, '$.timestampInNanoseconds') AS logic_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
        ,componentId_json
        ,eventData
        ,anyEventType
        ,clusterRepresentativeId_json
    FROM SiriSELFProcessedEvent
    where eventData is not null
),
es as (
    select
        logic_clock_id,
        p_get(eventData, anyEventType, 'eventMetadata.siriSetupId.value') as siri_setup_id,
        p_get(eventData, anyEventType, 'enrollmentUIStarted.enrollmentMode') as enrollment_mode,
        p_get(eventData, anyEventType, 'enrollmentUIStarted.voiceTriggerType') as voice_trigger_type
    from base_table
    WHERE component_name = 'SIRI_SETUP_CLIENT_EVENT' and event_name = 'enrollmentUIStarted'
),
es_dedup as (
    select
        siri_setup_id,
        max(logic_clock_id) as logic_clock_id,
        max(enrollment_mode) as enrollment_mode,
        max(voice_trigger_type) as voice_trigger_type
    from es
    group by siri_setup_id
),
ec as (
    select
        logic_clock_id,
        p_get(eventData, anyEventType, 'eventMetadata.siriSetupId.value') as siri_setup_id,
        p_get(eventData, anyEventType, 'enrollmentUICompleted.pageNumber') as page_number,
        p_get(eventData, anyEventType, 'enrollmentUICompleted.enrollmentOutcome') as enrollment_outcome
    from base_table
    WHERE component_name = 'SIRI_SETUP_CLIENT_EVENT' and event_name = 'enrollmentUICompleted'
),
ec_dedup as (
    select
        siri_setup_id,
        max(logic_clock_id) as logic_clock_id,
        max(enrollment_outcome) as enrollment_outcome
    from ec
    group by siri_setup_id
),
et as (
    select
        logic_clock_id,
        p_get(eventData, anyEventType, 'eventMetadata.siriSetupId.value') as siri_setup_id,
        p_get(eventData, anyEventType, 'enrollmentUIUtteranceTrainingAttempted.pageNumber') as page_number,
        p_get(eventData, anyEventType, 'enrollmentUIUtteranceTrainingAttempted.trainingOutcome') as training_outcome
    from base_table
    WHERE component_name = 'SIRI_SETUP_CLIENT_EVENT' and event_name = 'enrollmentUIUtteranceTrainingAttempted'
),
et_ranked as (
    select
        siri_setup_id,
        page_number,
        logic_clock_id,
        training_outcome,
        ROW_NUMBER()
            OVER (
                PARTITION BY siri_setup_id
                ORDER BY page_number desc
            ) AS rank
    from et
),
et_dedup as (
    select
        siri_setup_id,
        page_number,
        logic_clock_id,
        training_outcome
    from
        et_ranked
    where
        rank = 1
),
euc as (
    select
        logic_clock_id,
        p_get(eventData, anyEventType, 'eventMetadata.siriSetupId.value') as siri_setup_id,
        p_get(eventData, anyEventType, 'enrollmentUtteranceCompleted.pageNumber') as page_number,
        p_get(eventData, anyEventType, 'enrollmentUtteranceCompleted.phraseId') as phrase_id
    from
        base_table
    where
        component_name = 'SIRI_SETUP_CLIENT_EVENT'
        and event_name = 'enrollmentUtteranceCompleted'
),
euc_ranked as (
    select
        logic_clock_id,
        siri_setup_id,
        page_number,
        phrase_id,
        ROW_NUMBER()
            OVER (
                PARTITION BY siri_setup_id
                ORDER BY page_number desc
            ) AS rank
    from
        euc
),
euc_dedup as (
    select
        logic_clock_id,
        siri_setup_id,
        page_number,
        phrase_id
    from euc_ranked
    where rank = 1
),
global_dim_agg AS (
    SELECT
        logic_clock_id
        ,MIN(logic_timestamp_ns) AS min_clock_ns
        ,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
        ,MAX(IIF(event_name='siriAccountInformation', p_get(eventData, anyEventType, 'siriAccountInformation.siriDeviceId'), NULL)) AS assistant_id
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.deviceAggregationId.value'), NULL)) AS device_aggregation_id
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.userAggregationIdRotationTimestampMs'), NULL)) AS user_aggregation_id_rotation_timestamp_ms
        ,MAX(IIF(event_name='ephemeralToAggregationIdentifierMap', p_get(eventData, anyEventType, 'ephemeralToAggregationIdentifierMap.userAggregationIdExpirationTimestampMs'), NULL)) AS user_aggregation_id_expiration_timestamp_ms
    FROM base_table
    WHERE component_name='DIM_CLIENT_EVENT'
    GROUP BY logic_clock_id
),
joined as (
    select
        s.siri_setup_id as siri_setup_id,
        max(enrollment_mode) as enrollment_mode,
        max(voice_trigger_type) as voice_trigger_type,
        max(enrollment_outcome) as enrollment_outcome,
        max(device_aggregation_id) as device_aggregation_id,
        max(training_outcome) as training_outcome,
        max(t.page_number) as page_number,
        max(phrase_id) as phrase_id,
        max(device_type) as device_type,
        max(time_interval_since1970) as time_interval_since1970,
        max(system_build) as system_build,
        max(program_code) as program_code,
        max(siri_input_locale) as siri_input_locale
    from
        es_dedup s
        left join ec_dedup c on s.siri_setup_id = c.siri_setup_id
        left join euc_dedup uc on s.siri_setup_id = uc.siri_setup_id
        left join et_dedup t on s.siri_setup_id = t.siri_setup_id
        left join global_dim_agg g on s.logic_clock_id = g.logic_clock_id
    group by s.siri_setup_id
)
select *
from joined