# NOTE: This _v2 Tinybird pipe is NOT currently in use. To make non-breaking, additive changes to the current Tinybird endpoints, modify the un-suffixed "v1" versions of the pipes instead. The _v2 pipes will be removed in a future release.
# The _v2 pipes were added as part of a performance improvement experiment, which has been put on hold.

TOKEN "stats_page" READ
TOKEN "axis" READ

NODE timeseries
SQL >

    %
        {% set _single_day = defined(date_from) and defined(date_to) and day_diff(date_from, date_to) == 0 %}
        with
            {% if defined(date_from) %}
                toStartOfDay(
                    toDate(
                        {{
                            Date(
                                date_from,
                                description="Starting day for filtering a date range",
                                required=False,
                            )
                        }}
                    )
                ) as start,
            {% else %} toStartOfDay(timestampAdd(today(), interval -7 day)) as start,
            {% end %}
            {% if defined(date_to) %}
                toStartOfDay(
                    toDate(
                        {{
                            Date(
                                date_to,
                                description="Finishing day for filtering a date range",
                                required=False,
                            )
                        }}
                    )
                ) as end
            {% else %} toStartOfDay(today()) as end
            {% end %}
            {% if _single_day %}
                ,
                {% if defined(current_time) %}
                    toDateTime({{ String(current_time, description="Current time override for tests", required=False) }}, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})
                {% else %}
                    toTimezone(now(), {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})
                {% end %} as current_time,
                if(
                    toDate(end) = toDate(current_time),
                    least(
                        timestampAdd(end, interval 1 day),
                        timestampAdd(start, toIntervalHour(toHour(current_time) + 1))
                    ),
                    timestampAdd(end, interval 1 day)
                ) as end_exclusive
            {% end %}
        {% if _single_day %}
            select
                arrayJoin(
                    arrayMap(
                        x -> toDateTime(toString(toDateTime(x)), {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}}),
                        range(
                            toUInt32(toDateTime(start)), toUInt32(end_exclusive), 3600
                        )
                    )
                ) as date
        {% else %}
            select
                arrayJoin(
                    arrayMap(
                        x -> toDate(x),
                        range(toUInt32(start), toUInt32(timestampAdd(end, interval 1 day)), 24 * 3600)
                    )
                ) as date
        {% end %}


NODE session_data
DESCRIPTION >
    Read session data from AggregatingMergeTree MV using -Merge combinators

SQL >
    %
    SELECT
        site_uuid,
        session_id,
        countMerge(pageviews) as pageviews,
        minMerge(first_pageview) as first_pageview,
        maxMerge(last_pageview) as last_pageview
    FROM _mv_session_data_v2
    WHERE site_uuid = {{ String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True) }}
    GROUP BY site_uuid, session_id

NODE session_metrics
DESCRIPTION >
    Calculate session-level metrics (visits, pageviews, bounce rate, avg session duration)

SQL >

    %
        select
            site_uuid,
            {% if defined(date_from) and defined(date_to) and day_diff(date_from, date_to) == 0 %}
                toStartOfHour(toTimezone(first_pageview, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})) as date,
            {% else %}
                toDate(toTimezone(first_pageview, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})) as date,
            {% end %}
            sd.session_id,
            pageviews,
            pageviews = 1 as is_bounce,
            last_pageview - first_pageview as session_sec
        from session_data sd
            inner join filtered_sessions_v2 fs
                on fs.session_id = sd.session_id


NODE data
DESCRIPTION >
    Calculate KPIs per time period

SQL >

    select
        a.date,
        uniq(distinct s.session_id) as visits,
        sum(s.pageviews) as pageviews,
        truncate(avg(s.is_bounce), 2) as bounce_rate,
        truncate(avg(s.session_sec), 2) as avg_session_sec
    from timeseries a
    inner join session_metrics s on a.date = s.date
    group by a.date
    order by a.date


NODE pathname_pageviews
DESCRIPTION >
    Calculate pageviews for specific pathname with time granularity handling

SQL >

    %
            select
                {% if defined(date_from) and defined(date_to) and day_diff(date_from, date_to) == 0 %}
                    toStartOfHour(toTimezone(timestamp, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})) as date,
                {% else %}
                    toDate(toTimezone(timestamp, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}})) as date,
                {% end %}
                count() pageviews
            from timeseries a
            inner join _mv_hits h on
                {% if defined(date_from) and defined(date_to) and day_diff(date_from, date_to) == 0 %}
                    a.date = toStartOfHour(toTimezone(timestamp, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}}))
                {% else %}
                    a.date = toDate(toTimezone(timestamp, {{String(timezone, 'Etc/UTC', description="Site timezone", required=True)}}))
                {% end %}
            inner join filtered_sessions_v2 fs
                on fs.session_id = h.session_id
            where
                site_uuid = {{ String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True) }}
                {% if defined(member_status) %}
                    and member_status IN (
                        select arrayJoin(
                            {{ Array(member_status, "'undefined', 'free', 'paid'", description="Member status to filter on", required=False) }}
                            || if('paid' IN {{ Array(member_status) }}, ['comped', 'gift'], [])
                        )
                    )
                {% end %}
                {% if defined(location) %} and location = {{ String(location, description="Location to filter on", required=False) }} {% end %}
                {% if defined(pathname) %} and pathname = {{ String(pathname, description="Pathname to filter on", required=False) }} {% end %}
                {% if defined(post_uuid) %} and post_uuid = {{String(post_uuid, description="Post UUID to filter on", required=False) }} {% end %}
            group by date
            order by date


NODE finished_data
SQL >

    %
            select
                a.date as date,
                coalesce(b.visits, 0) as visits,
                {% if defined(pathname) or defined(post_uuid) %}coalesce(c.pageviews, 0){% else %}coalesce(b.pageviews, 0){% end %} as pageviews,
                coalesce(b.bounce_rate, 0) as bounce_rate,
                coalesce(b.avg_session_sec, 0) as avg_session_sec
            from timeseries a
            left join data b on a.date = b.date
            {% if defined(pathname) or defined(post_uuid) %}left join pathname_pageviews c on a.date = c.date{% end %}
TYPE ENDPOINT