SELECT
    dtSent AS dt,
    tsSent AS ts,
    status AS status,

    -- Определение типа
    multiIf(
        userDeviceOs = 1 AND extract(visitParamExtractString(sentPayload, 'message'), '"custom_type":"([^"]+)"') != '',
            extract(visitParamExtractString(sentPayload, 'message'), '"custom_type":"([^"]+)"'),
        userDeviceOs = 2 AND visitParamExtractString(sentPayload, 'custom_type') != '',
            visitParamExtractString(sentPayload, 'custom_type'),
        ''
    ) AS type,

    -- Определение подтипа
    multiIf(
        userDeviceOs = 1 AND sentDebug != 'huawei' AND extract(visitParamExtractString(sentPayload, 'message'), '"subType":"([^"]+)"') != '',
            extract(visitParamExtractString(sentPayload, 'message'), '"subType":"([^"]+)"'),
        userDeviceOs = 1 AND sentDebug = 'huawei' AND visitParamExtractString(sentPayload, 'subType') != '',
            visitParamExtractString(sentPayload, 'subType'),
        userDeviceOs = 2 AND visitParamExtractString(sentPayload, 'subType') != '',
            visitParamExtractString(sentPayload, 'subType'),
        pushType
    ) AS sub_type,

    title,
    text,
    toUInt64(userId) AS user_id,
    userUid AS user_uid,
    userType AS user_type,
    userDeviceOs AS user_device_os

FROM logs.pushes_sent AS ps
WHERE dtSent >= today()