# NOTE: This _v3 Tinybird experiment is NOT currently in use.
# It was added to test a faster unfiltered top-pages path using a daily materialized view.
# The router was intended to send filtered requests to api_top_pages and unfiltered requests to api_top_pages_v3 without changing Ghost's endpoint version wiring.
# The experiment has been put on hold, and these pipes may be removed in a future release.

# api_top_pages_v3 - Optimized top pages endpoint using daily materialized view
#
# WHY: The original api_top_pages scans all raw hits and joins with filtered_sessions,
# which becomes slow (500ms+) and hits memory limits at scale (10M+ rows).
#
# HOW: Uses _mv_daily_pages (pre-aggregated by day) for historical data, querying
# only ~100-200K MV rows instead of millions of raw hits. Today's data is queried
# fresh from _mv_hits to ensure real-time accuracy.
#
# PERFORMANCE (10M rows):
#   - 30-day query: 150ms vs 450ms (3x faster), 25K vs 12M rows scanned
#   - All-time query: 300ms vs MEMORY_LIMIT_EXCEEDED
#
# DIFFERENCES: v3 may show slightly lower counts (typically 1-2 per page) for pages
# where sessions cross midnight at the date range boundary. This is more accurate -
# v1 incorrectly counts page views outside the requested date range when sessions
# span multiple days.

TOKEN "stats_page" READ
TOKEN "axis" READ

NODE historical_data
DESCRIPTION >
    Query pre-aggregated daily data for complete days (excluding today).
    Uses uniqExactMerge to combine exact unique session states across days.
    Today is always excluded here and queried fresh from _mv_hits to avoid double-counting.

SQL >
    %
    SELECT
        post_uuid,
        pathname,
        uniqExactMerge(visits) as visits
    FROM _mv_daily_pages
    WHERE
        site_uuid = {{ String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True) }}
        {% if defined(date_from) %}
        AND day >= toDate({{ Date(date_from, description="Start date for filtering", required=False) }}, {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }})
        {% end %}
        AND day <= toDate({% if defined(date_to) %}{{ Date(date_to, description="End date for filtering", required=False) }}{% else %}now(){% end %}, {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }})
        AND day != toDate(now(), {{ String(timezone, 'Etc/UTC', description="Site timezone", 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(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 %}
        {% if defined(post_type) %}
            {% if post_type == 'post' %}
                AND post_type = 'post'
            {% else %}
                AND (post_type != 'post' OR post_type = '')
            {% end %}
        {% end %}
    GROUP BY post_uuid, pathname

NODE today_data
DESCRIPTION >
    Query raw _mv_hits for today's partial day to ensure freshness.

SQL >
    %
    SELECT
        case when post_uuid = 'undefined' then '' else post_uuid end as post_uuid,
        pathname,
        uniqExact(session_id) as visits
    FROM _mv_hits
    WHERE
        site_uuid = {{ String(site_uuid, 'mock_site_uuid', description="Tenant ID", required=True) }}
        AND toDate(timestamp, {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }}) = toDate(now(), {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }})
        {% if defined(date_from) %}
        AND timestamp >= toDateTime({{ Date(date_from, description="Start date for filtering", required=False) }}, {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }})
        {% end %}
        AND timestamp < toDateTime({% if defined(date_to) %}{{ Date(date_to, description="End date for filtering", required=False) }}{% else %}now(){% end %}, {{ String(timezone, 'Etc/UTC', description="Site timezone", required=True) }}) + interval 1 day
        {% 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(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 %}
        {% if defined(post_type) %}
            {% if post_type == 'post' %}
                AND post_type = 'post'
            {% else %}
                AND (post_type != 'post' OR post_type = '')
            {% end %}
        {% end %}
    GROUP BY post_uuid, pathname

NODE combined_results
DESCRIPTION >
    Combine historical pre-aggregated data with today's fresh data.

SQL >
    %
    SELECT
        post_uuid,
        pathname,
        sum(visits) as visits
    FROM (
        SELECT * FROM historical_data
        UNION ALL
        SELECT * FROM today_data
    )
    GROUP BY post_uuid, pathname
    ORDER BY visits DESC
    LIMIT {{ Int32(skip, 0) }}, {{ Int32(limit, 50) }}

TYPE ENDPOINT