-- Source table needs to match the schema in `/ODDIFramework/UnifiedSiriTurn.swift`
with step1 as (
    SELECT
        turnId as turn_id,
        assistantId as assistant_id,
        deviceAggregationId as device_aggregation_id,
        userAggregationId as user_aggregation_id,
        date(datetime(clockStartTime, 'unixepoch')) as event_date,
        userAggIdRotationDate * 1000.0 as user_aggregation_id_rotation_timestamp_ms,
        userAggIdExpirationDate * 1000.0 as user_aggregation_id_expiration_timestamp_ms,
        productId as product_id,
        deviceType as device_type,
        systemBuild as system_build,
        programCode as program_code,
        dataSharingOptInState as data_sharing_opt_in_state,
        siriInputLocale as siri_input_locale,
        dictationUsedLocale as dictation_used_locale,
        isCarplay as is_carplay,
        mhAudioVendorId as mh_audio_vendor_id,
        mhAudioProductId as mh_audio_product_id,
        asrLocation as asr_location,
        nlLocation as nl_location,
        productArea as sub_domain,
        isAssistantEngineRequest as is_assistant_engine_request,
        ieRouting as ie_routing,
        invocationSource as invocation_source,
        siriResponse as siri_response,
        isTurnTaken as is_turn_taken
    from
        UnifiedSiriTurn
),
experiment_context as (
    select
        DISTINCT turnId as turn_id,
        json_extract(value, '$.treatmentId.value') AS treatment_id,
        json_extract(value, '$.experimentId') AS experiment_id,
        json_extract(value, '$.deploymentId') AS deployment_id
    from
        UnifiedSiriTurn,
        json_each(
            json_extract(
                pstr_json(
                    json_extract(events_dim, "$.experimentContext[0]"),
                    "DIM_CLIENT_EVENT"
                ),
                "$.experimentContext.experimentInfo"
            )
        )
    where
        json_extract(
            pstr_json(
                json_extract(events_dim, "$.experimentContext[0]"),
                "DIM_CLIENT_EVENT"
            ),
            "$.experimentContext.experimentInfo"
        ) != ''
)
select
    assistant_id,
    device_aggregation_id,
    user_aggregation_id,
    event_date,
    user_aggregation_id_rotation_timestamp_ms,
    user_aggregation_id_expiration_timestamp_ms,
    product_id,
    device_type,
    system_build,
    program_code,
    data_sharing_opt_in_state,
    siri_input_locale,
    dictation_used_locale,
    is_carplay,
    mh_audio_vendor_id,
    mh_audio_product_id,
    asr_location,
    nl_location,
    sub_domain,
    is_assistant_engine_request,
    ie_routing,
    invocation_source,
    IIF(
        siri_response IS NOT NULL,
        convertToSiriResponseId(siri_response),
        NULL
    ) AS siri_response_id,
    IIF(
        siri_response IS NOT NULL,
        getSiriResponseCategory(convertToSiriResponseId(siri_response)),
        NULL
    ) AS siri_response_category,
    treatment_id,
    experiment_id,
    deployment_id,
    'EXPTREATMENTALLOCATIONSTATUS_ACTIVATED' AS allocation_status,
    COUNT(1) AS turn_cnt,
    SUM(is_turn_taken) AS user_turn_cnt,
    json_group_array(turn_id) AS turn_ids
from
    step1
    join experiment_context using (turn_id)
group by
    assistant_id,
    device_aggregation_id,
    event_date,
    user_aggregation_id_rotation_timestamp_ms,
    user_aggregation_id_expiration_timestamp_ms,
    product_id,
    device_type,
    system_build,
    program_code,
    data_sharing_opt_in_state,
    siri_input_locale,
    dictation_used_locale,
    is_carplay,
    mh_audio_vendor_id,
    mh_audio_product_id,
    asr_location,
    nl_location,
    sub_domain,
    is_assistant_engine_request,
    ie_routing,
    invocation_source,
    IIF(
        siri_response IS NOT NULL,
        convertToSiriResponseId(siri_response),
        NULL
    ),
    IIF(
        siri_response IS NOT NULL,
        getSiriResponseCategory(convertToSiriResponseId(siri_response)),
        NULL
    ),
    treatment_id,
    experiment_id,
    deployment_id