/**
 * Session Analytics Stored Procedures
 *
 * Batch processing for session journey building and metrics aggregation
 *
 * @package AI_eShop_Optimizer
 * @version 3.5.0
 */

-- ===========================================================================
-- PROCEDURE 1: Build Session Journeys (Batched)
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Build_Session_Journeys`//

CREATE PROCEDURE `wp_AIEO_Build_Session_Journeys`(
    IN _batch_size INT,
    IN _batch_offset INT,
    IN _days_back INT
)
BEGIN
    DECLARE _cutoff_date DATETIME;
    DECLARE _cutoff_timestamp BIGINT;

    -- Calculate cutoff date
    SET _cutoff_date = DATE_SUB(NOW(), INTERVAL _days_back DAY);
    SET _cutoff_timestamp = UNIX_TIMESTAMP(_cutoff_date) * 1000;

    -- Build journeys with window functions for sequencing
    INSERT INTO `wp_aieo_session_journeys` (
        `session_id`,
        `user_id`,
        `product_id`,
        `product_name`,
        `event_type`,
        `event_timestamp`,
        `event_sequence`,
        `total_events_in_session`,
        `is_first_event`,
        `is_last_event`,
        `journey_position`,
        `time_on_page`,
        `max_scroll_depth`,
        `click_count`,
        `device_type`,
        `session_duration`,
        `session_event_count`,
        `did_add_to_cart`,
        `add_to_cart_sequence`,
        `time_to_cart`
    )
    SELECT
        s.session_id,
        s.user_id,

        -- Extract product_id from JSON event_data
        CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_id')) AS UNSIGNED) AS product_id,
        JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_name')) AS product_name,
        s.event_type,
        s.timestamp AS event_timestamp,

        -- SEQUENCE CALCULATION (like ItemSequence in order analysis)
        ROW_NUMBER() OVER (
            PARTITION BY s.session_id
            ORDER BY s.timestamp
        ) AS event_sequence,

        COUNT(*) OVER (PARTITION BY s.session_id) AS total_events_in_session,

        -- POSITION FLAGS
        CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY s.session_id ORDER BY s.timestamp) = 1
            THEN 1
            ELSE 0
        END AS is_first_event,

        CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY s.session_id ORDER BY s.timestamp DESC) = 1
            THEN 1
            ELSE 0
        END AS is_last_event,

        -- JOURNEY POSITION (like JourneyLinearWeight in order analysis)
        CAST(
            ROW_NUMBER() OVER (PARTITION BY s.session_id ORDER BY s.timestamp) /
            NULLIF(COUNT(*) OVER (PARTITION BY s.session_id), 0)
            AS DECIMAL(5,4)
        ) AS journey_position,

        -- TIME ON PAGE (difference to next event in milliseconds, converted to seconds)
        COALESCE(
            CAST(
                (LEAD(s.timestamp) OVER (PARTITION BY s.session_id ORDER BY s.timestamp) - s.timestamp) / 1000
                AS UNSIGNED
            ),
            0
        ) AS time_on_page,

        -- ENGAGEMENT METRICS (from JSON)
        COALESCE(
            CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.scroll_depth')) AS UNSIGNED),
            0
        ) AS max_scroll_depth,

        COALESCE(
            CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.click_count')) AS UNSIGNED),
            0
        ) AS click_count,

        -- SESSION CONTEXT
        ss.device_type,
        ss.session_duration_seconds AS session_duration,
        ss.total_events AS session_event_count,

        -- CONVERSION TRACKING: Check if this product was added to cart later in session
        CASE
            WHEN EXISTS(
                SELECT 1
                FROM wp_aieo_sessions cart
                WHERE cart.session_id = s.session_id
                AND cart.event_type = 'add_to_cart'
                AND CAST(JSON_UNQUOTE(JSON_EXTRACT(cart.event_data, '$.product_id')) AS UNSIGNED) =
                    CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_id')) AS UNSIGNED)
            )
            THEN 1
            ELSE 0
        END AS did_add_to_cart,

        -- Sequence number when product was added to cart
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY cart.session_id ORDER BY cart.timestamp)
            FROM wp_aieo_sessions cart
            WHERE cart.session_id = s.session_id
            AND cart.event_type = 'add_to_cart'
            AND CAST(JSON_UNQUOTE(JSON_EXTRACT(cart.event_data, '$.product_id')) AS UNSIGNED) =
                CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_id')) AS UNSIGNED)
            LIMIT 1
        ) AS add_to_cart_sequence,

        -- Time from first view to cart (in seconds)
        (
            SELECT CAST((cart.timestamp - s.timestamp) / 1000 AS UNSIGNED)
            FROM wp_aieo_sessions cart
            WHERE cart.session_id = s.session_id
            AND cart.event_type = 'add_to_cart'
            AND CAST(JSON_UNQUOTE(JSON_EXTRACT(cart.event_data, '$.product_id')) AS UNSIGNED) =
                CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_id')) AS UNSIGNED)
            ORDER BY cart.timestamp
            LIMIT 1
        ) AS time_to_cart

    FROM wp_aieo_sessions s
    JOIN wp_aieo_sessions_statics ss ON ss.session_id = s.session_id
    -- Batching: JOIN a LIMITed derived set of session_ids rather than
    -- `s.session_id IN (SELECT ... LIMIT ...)`. The IN-with-LIMIT form is
    -- rejected by MariaDB 10.4-11.8 ("This version of MariaDB doesn't yet
    -- support 'LIMIT & IN/ALL/ANY/SOME subquery'"); LIMIT inside a derived
    -- table that is JOINed is portable across MariaDB 10.4-11.8 AND MySQL 8.
    -- `batch` carries DISTINCT session_id (one row per session) so the JOIN
    -- selects exactly the same rows without duplicating them.
    JOIN (
        SELECT DISTINCT session_id
        FROM wp_aieo_sessions
        WHERE timestamp >= _cutoff_timestamp
        ORDER BY session_id
        LIMIT _batch_offset, _batch_size
    ) batch ON batch.session_id = s.session_id
    WHERE s.event_type IN ('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist')
    AND s.timestamp >= _cutoff_timestamp
    AND JSON_EXTRACT(s.event_data, '$.product_id') IS NOT NULL
    AND CAST(JSON_UNQUOTE(JSON_EXTRACT(s.event_data, '$.product_id')) AS UNSIGNED) > 0
    ORDER BY s.session_id, s.timestamp;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 2: Aggregate Product Session Metrics (ATOMIC SWAP — DB #35)
-- ===========================================================================
-- Perf-review #35: the previous body mutated the LIVE metrics table in place
-- (DELETE stale rows -> upsert fresh aggregate -> 3 @rank ranking passes), so a
-- dashboard read landing between the DELETE and the rank passes saw missing rows
-- or stale ranks. We now build the whole result in a SHADOW table and publish it
-- with one atomic `RENAME TABLE` (MariaDB makes the multi-table rename atomic), so
-- readers ALWAYS see a complete, internally-consistent table. Ranking uses window
-- functions (ROW_NUMBER OVER) instead of the @rank-with-ORDER-BY-in-UPDATE trick
-- (unreliable under MySQL 8 / parallel readers). Semantics are preserved exactly:
-- carry existing rows forward, apply the fresh aggregate as an upsert, then prune
-- by cutoff — just on the shadow copy before it goes live.
-- This is the procedure the cron path actually CALLs
-- (AIEO_Session_Analytics_Processor::aggregate_session_metrics).

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Aggregate_Product_Session_Metrics`//

CREATE PROCEDURE `wp_AIEO_Aggregate_Product_Session_Metrics`(
    IN _days_back INT
)
BEGIN
    DECLARE _cutoff_date DATETIME;
    SET _cutoff_date = DATE_SUB(NOW(), INTERVAL _days_back DAY);

    -- Clean any leftovers from a prior interrupted run.
    DROP TABLE IF EXISTS wp_aieo_product_session_metrics_new;
    DROP TABLE IF EXISTS wp_aieo_product_session_metrics_old;

    -- Shadow table with identical structure (indexes, rank columns, etc.).
    CREATE TABLE wp_aieo_product_session_metrics_new
        LIKE wp_aieo_product_session_metrics;

    -- Carry forward current rows so non-active products keep their metrics
    -- (matches the original "only DELETE rows older than cutoff" behavior).
    INSERT INTO wp_aieo_product_session_metrics_new
        SELECT * FROM wp_aieo_product_session_metrics;

    -- Apply the fresh aggregate (same SELECT as before) as an upsert into the
    -- shadow table.
    INSERT INTO wp_aieo_product_session_metrics_new (
        `product_id`,
        `total_views`,
        `unique_sessions_viewed`,
        `avg_time_on_page`,
        `avg_scroll_depth`,
        `avg_clicks_per_view`,
        `times_first_page`,
        `times_last_page`,
        `times_only_page`,
        `total_add_to_cart`,
        `unique_sessions_added_to_cart`,
        `view_to_cart_rate`,
        `avg_time_to_cart`,
        `engagement_score`,
        `bounce_rate`,
        `last_calculated`
    )
    SELECT
        product_id,

        -- VIEW METRICS
        COUNT(*) AS total_views,
        COUNT(DISTINCT session_id) AS unique_sessions_viewed,
        AVG(time_on_page) AS avg_time_on_page,
        AVG(max_scroll_depth) AS avg_scroll_depth,
        AVG(click_count) AS avg_clicks_per_view,

        -- POSITION METRICS
        SUM(is_first_event) AS times_first_page,
        SUM(is_last_event) AS times_last_page,
        SUM(CASE WHEN is_first_event = 1 AND is_last_event = 1 THEN 1 ELSE 0 END) AS times_only_page,

        -- CONVERSION METRICS
        SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS total_add_to_cart,
        COUNT(DISTINCT CASE WHEN did_add_to_cart = 1 THEN session_id END) AS unique_sessions_added_to_cart,

        CASE
            WHEN COUNT(*) > 0
            THEN SUM(did_add_to_cart) / COUNT(*)
            ELSE 0
        END AS view_to_cart_rate,

        AVG(time_to_cart) AS avg_time_to_cart,

        -- ENGAGEMENT SCORE — CANONICAL formula, mirrored verbatim in PHP
        -- AIEO_Session_Tracker::calculate_engagement_score:
        --   scroll 40 + dwell 30 + clicks 20 + cart-intent 10, capped 0-100.
        ROUND(LEAST(100,
            LEAST(AVG(max_scroll_depth) * 0.40, 40) +
            LEAST((AVG(time_on_page) / 60) * 30, 30) +
            LEAST(AVG(click_count) * 4, 20) +
            LEAST((SUM(did_add_to_cart) / GREATEST(COUNT(*), 1)) * 10, 10)
        )) AS engagement_score,

        -- BOUNCE RATE
        CASE
            WHEN SUM(is_first_event) > 0
            THEN SUM(CASE WHEN is_first_event = 1 AND is_last_event = 1 THEN 1 ELSE 0 END) /
                 NULLIF(SUM(is_first_event), 0)
            ELSE 0
        END AS bounce_rate,

        NOW() AS last_calculated

    FROM wp_aieo_session_journeys
    WHERE created_at >= _cutoff_date
    GROUP BY product_id

    ON DUPLICATE KEY UPDATE
        total_views = VALUES(total_views),
        unique_sessions_viewed = VALUES(unique_sessions_viewed),
        avg_time_on_page = VALUES(avg_time_on_page),
        avg_scroll_depth = VALUES(avg_scroll_depth),
        avg_clicks_per_view = VALUES(avg_clicks_per_view),
        times_first_page = VALUES(times_first_page),
        times_last_page = VALUES(times_last_page),
        times_only_page = VALUES(times_only_page),
        total_add_to_cart = VALUES(total_add_to_cart),
        unique_sessions_added_to_cart = VALUES(unique_sessions_added_to_cart),
        view_to_cart_rate = VALUES(view_to_cart_rate),
        avg_time_to_cart = VALUES(avg_time_to_cart),
        engagement_score = VALUES(engagement_score),
        bounce_rate = VALUES(bounce_rate),
        last_calculated = NOW();

    -- Original prune: drop rows not refreshed within the window.
    DELETE FROM wp_aieo_product_session_metrics_new
    WHERE last_calculated < _cutoff_date;

    -- Rankings (computed on the shadow table — no live reader sees these
    -- transient states). Window functions instead of @rank session vars.
    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY view_to_cart_rate DESC, total_views DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.view_to_cart_rank = r.rnk;

    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY engagement_score DESC, total_views DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.engagement_rank = r.rnk;

    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY bounce_rate ASC, times_first_page DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.bounce_rank = r.rnk;

    -- Bump updated_at only for rows actually recomputed this run (preserves the
    -- original semantics; carried-forward rows keep their prior updated_at).
    UPDATE wp_aieo_product_session_metrics_new
    SET updated_at = NOW()
    WHERE last_calculated >= _cutoff_date;

    -- Publish atomically: one rename, readers never see a partial table.
    RENAME TABLE
        wp_aieo_product_session_metrics     TO wp_aieo_product_session_metrics_old,
        wp_aieo_product_session_metrics_new TO wp_aieo_product_session_metrics;

    DROP TABLE wp_aieo_product_session_metrics_old;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 2b: Aggregate Product Session Metrics FROM the daily rollup
-- ===========================================================================
-- Reconstructs wp_aieo_product_session_metrics by rolling up
-- wp_aieo_engagement_daily over the last _days_back days, instead of scanning
-- wp_aieo_session_journeys directly. engagement_daily is a per-day PARTITION of
-- the same journeys aggregate (built by AIEO_Aggregate_Cron::compute_engagement_daily),
-- so summing 30 daily rows is numerically equivalent to the journeys aggregate
-- for the SUM columns, and a views-weighted average reproduces the AVG columns.
-- Two advantages over PROCEDURE 2: (1) it is cheap (~30 rows/product, not the
-- whole event log), and (2) the daily rows persist after the raw firehose is
-- truncated, so the window is a TRUE 30 days rather than the <=7-day raw window.
-- Same shadow-table + atomic RENAME publish + ROW_NUMBER ranks as PROCEDURE 2.

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Aggregate_Product_Metrics_From_Daily`//

CREATE PROCEDURE `wp_AIEO_Aggregate_Product_Metrics_From_Daily`(
    IN _days_back INT
)
BEGIN
    DECLARE _cutoff_day DATE;
    DECLARE _cutoff_date DATETIME;
    SET _cutoff_day  = DATE_SUB(CURDATE(), INTERVAL _days_back DAY);
    SET _cutoff_date = DATE_SUB(NOW(), INTERVAL _days_back DAY);

    -- Clean any leftovers from a prior interrupted run.
    DROP TABLE IF EXISTS wp_aieo_product_session_metrics_new;
    DROP TABLE IF EXISTS wp_aieo_product_session_metrics_old;

    CREATE TABLE wp_aieo_product_session_metrics_new
        LIKE wp_aieo_product_session_metrics;

    -- Carry forward current rows so non-active products keep their metrics.
    INSERT INTO wp_aieo_product_session_metrics_new
        SELECT * FROM wp_aieo_product_session_metrics;

    -- Roll up the daily rows. The weighted averages are computed in a derived
    -- table so the engagement_score formula can reference them by name.
    INSERT INTO wp_aieo_product_session_metrics_new (
        `product_id`,
        `total_views`,
        `unique_sessions_viewed`,
        `avg_time_on_page`,
        `avg_scroll_depth`,
        `avg_clicks_per_view`,
        `times_first_page`,
        `times_last_page`,
        `times_only_page`,
        `total_add_to_cart`,
        `unique_sessions_added_to_cart`,
        `view_to_cart_rate`,
        `avg_time_to_cart`,
        `engagement_score`,
        `bounce_rate`,
        `last_calculated`
    )
    SELECT
        agg.product_id,
        agg.total_views,
        agg.unique_sessions_viewed,
        agg.avg_time_on_page,
        agg.avg_scroll_depth,
        agg.avg_clicks_per_view,
        agg.times_first_page,
        agg.times_last_page,
        agg.times_only_page,
        agg.total_add_to_cart,
        agg.unique_sessions_added_to_cart,
        agg.view_to_cart_rate,
        agg.avg_time_to_cart,

        -- ENGAGEMENT SCORE — CANONICAL formula, mirrored verbatim in PHP
        -- AIEO_Session_Tracker::calculate_engagement_score and PROCEDURE 2:
        --   scroll 40 + dwell 30 + clicks 20 + cart-intent 10, capped 0-100.
        ROUND(LEAST(100,
            LEAST(agg.avg_scroll_depth * 0.40, 40) +
            LEAST((agg.avg_time_on_page / 60) * 30, 30) +
            LEAST(agg.avg_clicks_per_view * 4, 20) +
            LEAST(agg.view_to_cart_rate * 10, 10)
        )) AS engagement_score,

        agg.bounce_rate,
        NOW() AS last_calculated
    FROM (
        SELECT
            product_id,
            SUM(views) AS total_views,
            SUM(unique_sessions) AS unique_sessions_viewed,
            CASE WHEN SUM(views) > 0 THEN SUM(avg_time_sec   * views) / SUM(views) ELSE 0 END AS avg_time_on_page,
            CASE WHEN SUM(views) > 0 THEN SUM(avg_scroll_pct * views) / SUM(views) ELSE 0 END AS avg_scroll_depth,
            CASE WHEN SUM(views) > 0 THEN SUM(avg_clicks     * views) / SUM(views) ELSE 0 END AS avg_clicks_per_view,
            SUM(landings) AS times_first_page,
            SUM(last_page) AS times_last_page,
            SUM(bounces) AS times_only_page,
            SUM(cart_adds) AS total_add_to_cart,
            SUM(unique_cart_sessions) AS unique_sessions_added_to_cart,
            CASE WHEN SUM(views) > 0 THEN SUM(cart_add_rate * views) / SUM(views) ELSE 0 END AS view_to_cart_rate,
            -- avg_time_to_cart weights by views (row count), matching the legacy
            -- AVG(time_to_cart) taken over ALL journey rows, not just cart rows.
            CASE WHEN SUM(views) > 0 THEN SUM(avg_time_to_cart * views) / SUM(views) ELSE 0 END AS avg_time_to_cart,
            CASE WHEN SUM(landings) > 0 THEN SUM(bounces) / SUM(landings) ELSE 0 END AS bounce_rate
        FROM wp_aieo_engagement_daily
        WHERE date >= _cutoff_day
        GROUP BY product_id
    ) agg

    ON DUPLICATE KEY UPDATE
        total_views = VALUES(total_views),
        unique_sessions_viewed = VALUES(unique_sessions_viewed),
        avg_time_on_page = VALUES(avg_time_on_page),
        avg_scroll_depth = VALUES(avg_scroll_depth),
        avg_clicks_per_view = VALUES(avg_clicks_per_view),
        times_first_page = VALUES(times_first_page),
        times_last_page = VALUES(times_last_page),
        times_only_page = VALUES(times_only_page),
        total_add_to_cart = VALUES(total_add_to_cart),
        unique_sessions_added_to_cart = VALUES(unique_sessions_added_to_cart),
        view_to_cart_rate = VALUES(view_to_cart_rate),
        avg_time_to_cart = VALUES(avg_time_to_cart),
        engagement_score = VALUES(engagement_score),
        bounce_rate = VALUES(bounce_rate),
        last_calculated = NOW();

    -- Drop rows not refreshed within the window (same prune as PROCEDURE 2).
    DELETE FROM wp_aieo_product_session_metrics_new
    WHERE last_calculated < _cutoff_date;

    -- Rankings on the shadow table (identical to PROCEDURE 2).
    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY view_to_cart_rate DESC, total_views DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.view_to_cart_rank = r.rnk;

    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY engagement_score DESC, total_views DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.engagement_rank = r.rnk;

    UPDATE wp_aieo_product_session_metrics_new m
    JOIN (
        SELECT product_id,
               ROW_NUMBER() OVER (ORDER BY bounce_rate ASC, times_first_page DESC) AS rnk
        FROM wp_aieo_product_session_metrics_new
    ) r ON r.product_id = m.product_id
    SET m.bounce_rank = r.rnk;

    UPDATE wp_aieo_product_session_metrics_new
    SET updated_at = NOW()
    WHERE last_calculated >= _cutoff_date;

    RENAME TABLE
        wp_aieo_product_session_metrics     TO wp_aieo_product_session_metrics_old,
        wp_aieo_product_session_metrics_new TO wp_aieo_product_session_metrics;

    DROP TABLE wp_aieo_product_session_metrics_old;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 3: Get Top/Bottom Products (Helper for UI)
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Get_Product_Rankings`//

CREATE PROCEDURE `wp_AIEO_Get_Product_Rankings`(
    IN _rank_type VARCHAR(50),  -- 'conversion', 'engagement', 'bounce'
    IN _top_bottom VARCHAR(10), -- 'top' or 'bottom'
    IN _limit INT
)
BEGIN
    DECLARE _order_dir VARCHAR(4);

    SET _order_dir = CASE _top_bottom WHEN 'top' THEN 'ASC' ELSE 'DESC' END;

    IF _rank_type = 'conversion' THEN
        SELECT
            m.product_id,
            p.post_title AS product_name,
            m.total_views,
            m.total_add_to_cart,
            ROUND(m.view_to_cart_rate * 100, 2) AS conversion_rate_pct,
            m.view_to_cart_rank
        FROM wp_aieo_product_session_metrics m
        LEFT JOIN wp_posts p ON p.ID = m.product_id
        WHERE m.total_views >= 10  -- Minimum views for statistical significance
        ORDER BY
            CASE _top_bottom
                WHEN 'top' THEN m.view_to_cart_rank
                ELSE -m.view_to_cart_rank
            END ASC
        LIMIT _limit;

    ELSEIF _rank_type = 'engagement' THEN
        SELECT
            m.product_id,
            p.post_title AS product_name,
            m.total_views,
            ROUND(m.engagement_score, 2) AS engagement_score,
            ROUND(m.avg_time_on_page, 1) AS avg_seconds_on_page,
            ROUND(m.avg_scroll_depth, 1) AS avg_scroll_pct,
            m.engagement_rank
        FROM wp_aieo_product_session_metrics m
        LEFT JOIN wp_posts p ON p.ID = m.product_id
        WHERE m.total_views >= 10
        ORDER BY
            CASE _top_bottom
                WHEN 'top' THEN m.engagement_rank
                ELSE -m.engagement_rank
            END ASC
        LIMIT _limit;

    ELSEIF _rank_type = 'bounce' THEN
        SELECT
            m.product_id,
            p.post_title AS product_name,
            m.total_views,
            m.times_first_page AS landing_count,
            m.times_only_page AS bounce_count,
            ROUND(m.bounce_rate * 100, 2) AS bounce_rate_pct,
            m.bounce_rank
        FROM wp_aieo_product_session_metrics m
        LEFT JOIN wp_posts p ON p.ID = m.product_id
        WHERE m.times_first_page >= 5  -- Minimum landings for statistical significance
        ORDER BY
            CASE _top_bottom
                WHEN 'top' THEN m.bounce_rank
                ELSE -m.bounce_rank
            END ASC
        LIMIT _limit;

    END IF;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 4: Cleanup Old Journey Data
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Cleanup_Old_Journeys`//

CREATE PROCEDURE `wp_AIEO_Cleanup_Old_Journeys`(
    IN _retention_days INT
)
BEGIN
    DECLARE _cutoff_date DATETIME;

    SET _cutoff_date = DATE_SUB(NOW(), INTERVAL _retention_days DAY);

    -- Delete old journey records
    DELETE FROM wp_aieo_session_journeys
    WHERE created_at < _cutoff_date;

    -- Optionally delete old session statics
    DELETE FROM wp_aieo_sessions_statics
    WHERE created_at < _cutoff_date;

    -- Return cleanup summary
    SELECT
        _cutoff_date AS cutoff_date,
        _retention_days AS retention_days,
        ROW_COUNT() AS rows_deleted;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 5: Get Session Analytics Summary
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Get_Analytics_Summary`//

CREATE PROCEDURE `wp_AIEO_Get_Analytics_Summary`()
BEGIN
    -- Overview of all analytics data
    SELECT
        (SELECT COUNT(*) FROM wp_aieo_sessions_statics) AS total_sessions,
        (SELECT COUNT(*) FROM wp_aieo_sessions) AS total_events,
        (SELECT COUNT(*) FROM wp_aieo_session_journeys) AS total_journey_records,
        (SELECT COUNT(*) FROM wp_aieo_product_session_metrics) AS products_with_metrics,
        (SELECT MIN(created_at) FROM wp_aieo_sessions_statics) AS oldest_session,
        (SELECT MAX(created_at) FROM wp_aieo_sessions_statics) AS newest_session,
        (SELECT MAX(last_calculated) FROM wp_aieo_product_session_metrics) AS metrics_last_updated;

END//

DELIMITER ;
