/**
 * Session Analytics Stored Procedures - OPTIMIZED VERSION
 *
 * This version uses extracted columns (product_id, scroll_depth, etc.)
 * instead of parsing JSON from event_data.
 *
 * Use this AFTER running session-schema-migration.sql
 *
 * @package AI_eShop_Optimizer
 * @version 3.5.0
 */

-- ===========================================================================
-- PROCEDURE 1: Build Session Journeys (OPTIMIZED - Uses Direct Columns)
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Build_Session_Journeys_Optimized`//

CREATE PROCEDURE `wp_AIEO_Build_Session_Journeys_Optimized`(
    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 using DIRECT COLUMNS (no JSON parsing!)
    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,
        s.product_id,          -- DIRECT COLUMN ACCESS (was JSON_EXTRACT)
        s.product_name,        -- DIRECT COLUMN ACCESS
        s.event_type,
        s.timestamp AS event_timestamp,

        -- SEQUENCE CALCULATION
        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
        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 (seconds until next event)
        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 (DIRECT COLUMNS!)
        -- scroll_depth is recorded on session-level `scroll_depth` events (no
        -- product_id), so the product_view row's own s.scroll_depth is always 0.
        -- Take the session's MAX scroll across all its events instead.
        COALESCE((SELECT MAX(x.scroll_depth) FROM wp_aieo_sessions x WHERE x.session_id = s.session_id), 0) AS max_scroll_depth,
        -- click_count is likewise a session-level running counter (recorded on
        -- click / page_exit / image-click events), not on the product_view row;
        -- take the session's peak (= total clicks in the session).
        COALESCE((SELECT MAX(x.click_count) FROM wp_aieo_sessions x WHERE x.session_id = s.session_id), 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 product was added to cart
        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 cart.product_id = s.product_id  -- DIRECT COMPARISON (no JSON!)
            )
            THEN 1
            ELSE 0
        END AS did_add_to_cart,

        -- Sequence number when 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 cart.product_id = s.product_id
            LIMIT 1
        ) AS add_to_cart_sequence,

        -- Time from first view to cart (seconds)
        (
            SELECT CAST(GREATEST(0, CAST(cart.timestamp AS SIGNED) - CAST(s.timestamp AS SIGNED)) / 1000 AS UNSIGNED)
            FROM wp_aieo_sessions cart
            WHERE cart.session_id = s.session_id
            AND cart.event_type = 'add_to_cart'
            AND cart.product_id = s.product_id
            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
        AND event_type IN ('product_view', 'add_to_cart')
        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 s.product_id IS NOT NULL  -- Only events with product_id
    ORDER BY s.session_id, s.timestamp;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 2: Aggregate Product Metrics (OPTIMIZED + ATOMIC SWAP — DB #35)
-- ===========================================================================
-- Same atomic shadow-swap fix as the non-optimized sibling (see
-- session-analytics-procedures.sql): build into a SHADOW table, then publish
-- with one atomic RENAME TABLE so dashboard readers never see a partial table
-- mid-rebuild. Ranking uses ROW_NUMBER() window functions. NOTE: the cron path
-- currently CALLs the non-`_Optimized` procedure; this `_Optimized` copy is kept
-- byte-for-byte consistent so either name behaves identically.

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Aggregate_Product_Session_Metrics_Optimized`//

CREATE PROCEDURE `wp_AIEO_Aggregate_Product_Session_Metrics_Optimized`(
    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.
    INSERT INTO wp_aieo_product_session_metrics_new
        SELECT * FROM wp_aieo_product_session_metrics;

    -- Aggregate all metrics per product (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). 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 3: Quick Analytics (Direct from Sessions Table)
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Quick_Product_Analytics`//

CREATE PROCEDURE `wp_AIEO_Quick_Product_Analytics`(
    IN _product_id BIGINT,
    IN _days_back INT
)
BEGIN
    DECLARE _cutoff_timestamp BIGINT;

    SET _cutoff_timestamp = UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL _days_back DAY)) * 1000;

    -- Quick analytics without building full journeys
    SELECT
        _product_id AS product_id,
        p.post_title AS product_name,

        -- View metrics
        COUNT(DISTINCT CASE WHEN s.event_type = 'product_view' THEN s.id END) AS total_views,
        COUNT(DISTINCT CASE WHEN s.event_type = 'product_view' THEN s.session_id END) AS unique_sessions,

        -- Conversion metrics
        COUNT(DISTINCT CASE WHEN s.event_type = 'add_to_cart' THEN s.id END) AS total_add_to_cart,
        COUNT(DISTINCT CASE WHEN s.event_type = 'add_to_cart' THEN s.session_id END) AS sessions_with_cart,

        -- Conversion rate
        ROUND(
            100.0 * COUNT(DISTINCT CASE WHEN s.event_type = 'add_to_cart' THEN s.session_id END) /
            NULLIF(COUNT(DISTINCT CASE WHEN s.event_type = 'product_view' THEN s.session_id END), 0),
            2
        ) AS conversion_rate_pct,

        -- Engagement metrics
        AVG(CASE WHEN s.event_type = 'product_view' THEN s.scroll_depth END) AS avg_scroll_depth,
        AVG(CASE WHEN s.event_type = 'product_view' THEN s.click_count END) AS avg_clicks

    FROM wp_aieo_sessions s
    LEFT JOIN wp_posts p ON p.ID = _product_id
    WHERE s.product_id = _product_id
    AND s.timestamp >= _cutoff_timestamp
    GROUP BY _product_id, p.post_title;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 4: Session Detail with Products
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Get_Session_Detail`//

CREATE PROCEDURE `wp_AIEO_Get_Session_Detail`(
    IN _session_id VARCHAR(255)
)
BEGIN
    -- Get session metadata
    SELECT
        ss.*,
        ROUND(ss.session_duration_seconds / 60.0, 2) AS session_duration_minutes,
        ROUND(100.0 * ss.total_add_to_cart / NULLIF(ss.total_product_views, 0), 2) AS conversion_rate_pct
    FROM wp_aieo_sessions_statics ss
    WHERE ss.session_id = _session_id;

    -- Get session events (product views and cart actions)
    SELECT
        s.id,
        s.event_type,
        s.product_id,
        s.product_name,
        p.post_title AS actual_product_name,
        s.scroll_depth,
        s.click_count,
        s.page_url,
        FROM_UNIXTIME(s.timestamp / 1000) AS event_time,
        ROW_NUMBER() OVER (ORDER BY s.timestamp) AS event_sequence
    FROM wp_aieo_sessions s
    LEFT JOIN wp_posts p ON p.ID = s.product_id
    WHERE s.session_id = _session_id
    ORDER BY s.timestamp;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 5: Real-time Product Rankings (Last 24 Hours)
-- ===========================================================================

DELIMITER //

DROP PROCEDURE IF EXISTS `wp_AIEO_Realtime_Product_Rankings`//

CREATE PROCEDURE `wp_AIEO_Realtime_Product_Rankings`(
    IN _limit INT
)
BEGIN
    DECLARE _cutoff_timestamp BIGINT;

    SET _cutoff_timestamp = UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 24 HOUR)) * 1000;

    -- Calculate rankings from raw events (last 24 hours)
    SELECT
        s.product_id,
        p.post_title AS product_name,

        COUNT(DISTINCT CASE WHEN s.event_type = 'product_view' THEN s.session_id END) AS views,
        COUNT(DISTINCT CASE WHEN s.event_type = 'add_to_cart' THEN s.session_id END) AS add_to_cart,

        ROUND(
            100.0 * COUNT(DISTINCT CASE WHEN s.event_type = 'add_to_cart' THEN s.session_id END) /
            NULLIF(COUNT(DISTINCT CASE WHEN s.event_type = 'product_view' THEN s.session_id END), 0),
            2
        ) AS conversion_rate_pct,

        AVG(CASE WHEN s.event_type = 'product_view' THEN s.scroll_depth END) AS avg_scroll_depth,
        AVG(CASE WHEN s.event_type = 'product_view' THEN s.click_count END) AS avg_clicks

    FROM wp_aieo_sessions s
    LEFT JOIN wp_posts p ON p.ID = s.product_id
    WHERE s.timestamp >= _cutoff_timestamp
    AND s.product_id IS NOT NULL
    AND s.event_type IN ('product_view', 'add_to_cart')
    GROUP BY s.product_id, p.post_title
    HAVING views >= 5  -- Minimum views for statistical significance
    ORDER BY conversion_rate_pct DESC
    LIMIT _limit;

END//

DELIMITER ;


-- ===========================================================================
-- TEST QUERIES
-- ===========================================================================

-- Test optimized journey building (should be much faster)
-- CALL wp_AIEO_Build_Session_Journeys_Optimized(1000, 0, 30);

-- Test metrics aggregation
-- CALL wp_AIEO_Aggregate_Product_Session_Metrics_Optimized(30);

-- Test quick analytics for a specific product
-- CALL wp_AIEO_Quick_Product_Analytics(123, 30);

-- Test session detail
-- CALL wp_AIEO_Get_Session_Detail('session_1234567890_abcdef');

-- Test real-time rankings
-- CALL wp_AIEO_Realtime_Product_Rankings(30);
