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

global_dim_agg AS (
    SELECT
        logic_clock_id AS dfc_logic_clock_id,
        MIN(logical_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='ephemeralToAggregationIdentifierMap', P_GET(eventdata, anyeventtype, 'ephemeralToAggregationIdentifierMap.deviceAggregationId.value'), NULL)) AS device_aggregation_id
    FROM base
    WHERE component_name = 'DIM_CLIENT_EVENT'
    GROUP BY dfc_logic_clock_id
),

-- SADClientEvent: IntelligenceFeatureAvailabilityDetailedStatus
sad_availability_detailed_status AS (
    SELECT
        logic_clock_id AS sad_logic_clock_id,
        logical_timestamp_ns AS eventLogicalTimestampNs,
        JSON_EXTRACT(P_JSON(eventdata, anyeventtype), '$.intelligenceFeatureAvailabilityDetailedStatus') AS availabilityDetailedStatus
    FROM base
    WHERE event_name = 'intelligenceFeatureAvailabilityDetailedStatus'
),

-- SADClientEvent: IntelligenceFeatureAvailabilityChangeStatus
sad_availability_change_status AS (
    SELECT
        logic_clock_id AS sad_logic_clock_id,
        logical_timestamp_ns AS eventLogicalTimestampNs,
        JSON_EXTRACT(P_JSON(eventdata, anyeventtype), '$.intelligenceFeatureAvailabilityChangeStatus') AS availabilityChangeStatus
    FROM base
    WHERE event_name = 'intelligenceFeatureAvailabilityChangeStatus'
),

-- Unified change and detailed events
sad_availability_events AS (
    SELECT
        sad_logic_clock_id,
        eventLogicalTimestampNs,
        JSON(availabilityChangeStatus) AS availabilityChangeStatus,
        CAST(NULL AS JSON) AS availabilityDetailedStatus
    FROM sad_availability_change_status
    UNION ALL
    SELECT
        sad_logic_clock_id,
        eventLogicalTimestampNs,
        CAST(NULL AS JSON) AS availabilityChangeStatus,
        JSON(availabilityDetailedStatus) AS availabilityDetailedStatus
    FROM sad_availability_detailed_status
),

-- Enrich with device-fixed context anchor and dimensions; one row per event
include_event_time AS (
    SELECT
        g.time_interval_since1970 + (e.eventLogicalTimestampNs - g.min_clock_ns) / 1000000000.0 AS eventTimestampSecondsSince1970,
        -- Compute the event time from logical timestamp in nanoseconds and convert to milliseconds to match the final ODD event timestamp format
        CAST(
            CAST(g.time_interval_since1970 * 1000 AS INTEGER)
            + CAST((e.eventLogicalTimestampNs - g.min_clock_ns) / 1000000 AS INTEGER)
            AS INTEGER
        ) AS eventTimestampInMsSince1970,
        g.device_aggregation_id AS deviceAggregationId,
        g.system_build AS systemBuild,
        g.device_type AS deviceType,
        g.program_code AS programCode,
        g.siri_input_locale AS siriInputLocale,
        g.data_sharing_opt_in_state AS dataSharingOptInState,
        e.availabilityChangeStatus AS availabilityChangeStatus,
        e.availabilityDetailedStatus AS availabilityDetailedStatus
    FROM sad_availability_events e
    JOIN global_dim_agg g
        ON e.sad_logic_clock_id = g.dfc_logic_clock_id
)

SELECT
    DISTINCT -- Guard against duplicate rows
    DATE(eventTimestampSecondsSince1970, 'unixepoch') AS eventDate,
    eventTimestampInMsSince1970,
    deviceAggregationId,
    systemBuild,
    deviceType,
    programCode,
    siriInputLocale,
    dataSharingOptInState,
    availabilityChangeStatus,
    availabilityDetailedStatus
FROM include_event_time
-- Guard potential invalid event timestamps
WHERE eventTimestampInMsSince1970 IS NOT NULL
  AND eventTimestampInMsSince1970 > 0
  AND eventTimestampInMsSince1970 <= CAST(strftime('%s','now') AS INTEGER) * 1000
  AND eventTimestampSecondsSince1970 <= CAST(strftime('%s','now') AS INTEGER)
ORDER BY eventTimestampSecondsSince1970;