/**
 * 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!)
        COALESCE(s.scroll_depth, 0) AS max_scroll_depth,
        COALESCE(s.click_count, 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((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 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
    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
    -- Batching
    AND s.session_id IN (
        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
    )
    ORDER BY s.session_id, s.timestamp;

END//

DELIMITER ;


-- ===========================================================================
-- PROCEDURE 2: Aggregate Product Metrics (OPTIMIZED)
-- ===========================================================================

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

    -- Delete old metrics
    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)
        (
            (AVG(max_scroll_depth) * 0.4) +
            (LEAST(AVG(time_on_page) / 60, 100) * 0.3) +
            (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

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

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

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