alter table otel.otel_traces_url_local on cluster default
    add column ParentSpanId String default '';

alter table otel.otel_traces_url on cluster default
    add column ParentSpanId String default '';

drop table if exists otel_traces_url_local_mv on cluster default sync;

CREATE MATERIALIZED VIEW if not exists otel.otel_traces_url_local_mv
            on cluster default
            TO otel.otel_traces_url_local
            (
             `Timestamp` DateTime64(9),
             `TraceId` String,
             `SpanId` String,
             `ParentSpanId` String,
             `Route` String,
             `RouteRaw` String,
             `Path` String,
             `Query` String,
             `Target` String,
             `ProtocolName` String,
             `ProtocolVersion` String,
             `Method` String,
             `StatusCode` Int64,
             `Message` String,
             `UserAgent` String,
             `Duration` Int64,
             `ServiceName` LowCardinality(String),
             `AppAlias` String
                )
AS
WITH
    '\\b[a-fA-F0-9]{32,}\\b|\\b[a-fA-F0-9\\-]{36}\\b|\\b\\[1-9]\\d*\\b' AS varPattern, SpanAttributes['http.route'] AS route, lower(splitByString('.', splitByString('/', Path)[-1])[-1]) AS fileExt, multiIf(
        (SpanAttributes['url.path']) != '', SpanAttributes['url.path'], (SpanAttributes['http.target']) != '',
        path(SpanAttributes['http.target']), (SpanAttributes['http.url']) != '', path(SpanAttributes['http.url']),
        (SpanAttributes['u\nrl.full']) != '', path(SpanAttributes['url.full']), SpanAttributes['http.route']) AS path
SELECT Timestamp,
       TraceId,
       SpanId,
       ParentSpanId,
       if(route != '', route, replaceRegexpOne(Path, varPattern, '{:var}'))                                 AS Route,
       route                                                                                                AS RouteRaw,
       path                                                                                                 AS Path,
       if((SpanAttributes['url.query']) != '', SpanAttributes['url.query'],
          queryString(SpanAttributes['http.target']))                                                       AS Query,
       if((SpanAttributes['http.target']) != '', SpanAttributes['http.target'],
          if((SpanAttributes['url.path']) != '', SpanAttributes['url.path'], SpanAttributes['http.route'])) AS Target,
       multiIf((SpanAttributes['http.scheme']) != '', SpanAttributes['http.scheme'],
               (SpanAttributes['url.scheme']) != '', SpanAttributes['url.scheme'],
               SpanAttributes['network.protocol.name'])                                                     AS ProtocolName,
       if((SpanAttributes['http.flavor']) != '', SpanAttributes['http.flavor'],
          SpanAttributes['network.protocol.version'])                                                       AS ProtocolVersion,
       if((SpanAttributes['http.method']) != '', SpanAttributes['http.method'],
          SpanAttributes['http.request.method'])                                                            AS Method,
       toInt64OrZero(if((SpanAttributes['http.status_code']) != '', SpanAttributes['http.status_code'],
                        SpanAttributes['http.response.status_code']))                                       AS StatusCode,
       if(StatusMessage != '', StatusMessage, SpanAttributes['error.type'])                                 AS Message,
       if((SpanAttributes['http.user_agent']) != '', SpanAttributes['http.user_agent'],
          SpanAttributes['user_agent.original'])                                                            AS UserAgent,
       Duration,
       ServiceName,
       AppAlias
FROM otel.otel_traces_local
WHERE (SpanKindNumber = 2)
  AND (Path != '')
  AND (((SpanAttributes['http.method']) != '') OR ((SpanAttributes['http.request.method']) != ''))
  AND (fileExt != Path)
  AND (fileExt NOT IN
       ('css', 'gif', 'ico', 'jpg', 'jpeg', 'js', 'json', 'mp3', 'mp4', 'png', 'svg', 'ttf', 'txt', 'wasm', 'woff'));


truncate table otel.otel_traces_url_local on cluster default;