/**
 * 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
    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
    -- Batching
    AND s.session_id IN (
        SELECT DISTINCT session_id
        FROM wp_aieo_sessions
        WHERE timestamp >= _cutoff_timestamp
        ORDER BY session_id
        LIMIT _batch_offset, _batch_size
    )
    ORDER BY s.session_id, s.timestamp;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 2: Aggregate Product 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;

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

    -- Clear existing metrics (or use INSERT ... ON DUPLICATE KEY UPDATE for incremental)
    DELETE FROM wp_aieo_product_session_metrics
    WHERE last_calculated < _cutoff_date;

    -- Aggregate all metrics per product
    INSERT INTO wp_aieo_product_session_metrics (
        `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 (weighted: 40% scroll + 30% time + 30% clicks)
        (
            (AVG(max_scroll_depth) * 0.4) +
            (LEAST(AVG(time_on_page) / 60, 100) * 0.3) +  -- Cap at 60 seconds = 100 points
            (LEAST(AVG(click_count) * 10, 100) * 0.3)
        ) 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

    -- Update existing rows or insert new ones
    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();

    -- Calculate rankings using session variables
    SET @rank = 0;
    UPDATE wp_aieo_product_session_metrics
    SET view_to_cart_rank = (@rank := @rank + 1)
    ORDER BY view_to_cart_rate DESC, total_views DESC;

    SET @rank = 0;
    UPDATE wp_aieo_product_session_metrics
    SET engagement_rank = (@rank := @rank + 1)
    ORDER BY engagement_score DESC, total_views DESC;

    SET @rank = 0;
    UPDATE wp_aieo_product_session_metrics
    SET bounce_rank = (@rank := @rank + 1)
    ORDER BY bounce_rate ASC, times_first_page DESC;  -- Lower bounce rate = better rank

    -- Update timestamp
    UPDATE wp_aieo_product_session_metrics
    SET updated_at = NOW()
    WHERE last_calculated >= _cutoff_date;

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.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 ;
