/**
 * Session Analytics Schema
 *
 * Three-tier architecture for efficient session analytics:
 * 1. wp_aieo_sessions_statics - Session metadata (one row per session)
 * 2. wp_aieo_session_journeys - Preprocessed session sequences
 * 3. wp_aieo_product_session_metrics - Aggregated product metrics
 *
 * @package AI_eShop_Optimizer
 * @version 3.5.0
 */

-- ===========================================================================
-- TIER 1: SESSION STATICS (Separate metadata from events)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS `wp_aieo_sessions_statics` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `session_id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT(20) UNSIGNED DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `device_type` VARCHAR(50) DEFAULT NULL,
    `browser` VARCHAR(100) DEFAULT NULL,
    `os` VARCHAR(100) DEFAULT NULL,
    `landing_page` VARCHAR(512) DEFAULT NULL,
    `referrer` VARCHAR(512) DEFAULT NULL,
    `first_timestamp` BIGINT(20) UNSIGNED NOT NULL,
    `last_timestamp` BIGINT(20) UNSIGNED DEFAULT NULL,
    `total_events` INT(10) UNSIGNED DEFAULT 0,
    `total_product_views` INT(10) UNSIGNED DEFAULT 0,
    `total_add_to_cart` INT(10) UNSIGNED DEFAULT 0,
    `total_scroll_events` INT(10) UNSIGNED DEFAULT 0,
    `total_click_events` INT(10) UNSIGNED DEFAULT 0,
    `session_duration_seconds` INT(10) UNSIGNED DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_session_id` (`session_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_device_type` (`device_type`),
    KEY `idx_first_timestamp` (`first_timestamp`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
COMMENT='Session metadata - one row per session';


-- ===========================================================================
-- TIER 2: SESSION JOURNEYS (Preprocessed sequences with engagement metrics)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS `wp_aieo_session_journeys` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `session_id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT(20) UNSIGNED DEFAULT NULL,
    `product_id` BIGINT(20) UNSIGNED NOT NULL,
    `product_name` VARCHAR(512) DEFAULT NULL,
    `event_type` ENUM('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist') NOT NULL,

    -- TIMING & SEQUENCING
    `event_timestamp` BIGINT(20) UNSIGNED NOT NULL,
    `event_sequence` INT(10) UNSIGNED NOT NULL COMMENT 'Position in session (1st, 2nd, 3rd... event)',
    `total_events_in_session` INT(10) UNSIGNED NOT NULL,

    -- POSITION ANALYTICS
    `is_first_event` TINYINT(1) DEFAULT 0 COMMENT 'Landing page product',
    `is_last_event` TINYINT(1) DEFAULT 0 COMMENT 'Exit page product',
    `journey_position` DECIMAL(5,4) DEFAULT NULL COMMENT 'Normalized position (0.0-1.0)',

    -- ENGAGEMENT METRICS
    `time_on_page` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Seconds spent on this page',
    `max_scroll_depth` TINYINT(3) UNSIGNED DEFAULT 0 COMMENT 'Maximum scroll depth (0-100%)',
    `click_count` SMALLINT(5) UNSIGNED DEFAULT 0 COMMENT 'Number of clicks on this page',

    -- SESSION CONTEXT
    `device_type` VARCHAR(50) DEFAULT NULL,
    `session_duration` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Total session duration in seconds',
    `session_event_count` INT(10) UNSIGNED DEFAULT 0,

    -- CONVERSION TRACKING
    `did_add_to_cart` TINYINT(1) DEFAULT 0 COMMENT 'Was this product added to cart in this session?',
    `add_to_cart_sequence` INT(10) DEFAULT NULL COMMENT 'Sequence number when added to cart',
    `time_to_cart` INT(10) DEFAULT NULL COMMENT 'Seconds from first view to add_to_cart',

    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `idx_session_id` (`session_id`),
    KEY `idx_product_id` (`product_id`),
    KEY `idx_event_type` (`event_type`),
    KEY `idx_sequence` (`session_id`, `event_sequence`),
    KEY `idx_first_page` (`product_id`, `is_first_event`),
    KEY `idx_last_page` (`product_id`, `is_last_event`),
    KEY `idx_conversion` (`product_id`, `did_add_to_cart`),
    KEY `idx_timestamp` (`event_timestamp`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_product_event` (`product_id`, `event_type`, `event_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
COMMENT='Preprocessed session sequences with engagement metrics';


-- ===========================================================================
-- TIER 3: PRODUCT SESSION METRICS (Aggregated analytics per product)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS `wp_aieo_product_session_metrics` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` BIGINT(20) UNSIGNED NOT NULL,

    -- VIEW METRICS
    `total_views` INT(10) UNSIGNED DEFAULT 0,
    `unique_sessions_viewed` INT(10) UNSIGNED DEFAULT 0,
    `avg_time_on_page` DECIMAL(10,2) DEFAULT 0 COMMENT 'Average seconds',
    `avg_scroll_depth` DECIMAL(5,2) DEFAULT 0 COMMENT 'Average percentage',
    `avg_clicks_per_view` DECIMAL(5,2) DEFAULT 0,

    -- POSITION METRICS
    `times_first_page` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Landing page count',
    `times_last_page` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Exit page count',
    `times_only_page` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Bounce count (first AND last)',

    -- CONVERSION METRICS
    `total_add_to_cart` INT(10) UNSIGNED DEFAULT 0,
    `unique_sessions_added_to_cart` INT(10) UNSIGNED DEFAULT 0,
    `view_to_cart_rate` DECIMAL(5,4) DEFAULT 0 COMMENT 'Conversion rate (0.0-1.0)',
    `avg_time_to_cart` DECIMAL(10,2) DEFAULT 0 COMMENT 'Average seconds from view to cart',

    -- PERFORMANCE RANKINGS (updated during aggregation)
    `view_to_cart_rank` INT(10) UNSIGNED DEFAULT 0 COMMENT '1 = best converter',
    `engagement_score` DECIMAL(10,2) DEFAULT 0 COMMENT 'Weighted score (scroll+clicks+time)',
    `engagement_rank` INT(10) UNSIGNED DEFAULT 0 COMMENT '1 = most engaging',
    `bounce_rate` DECIMAL(5,4) DEFAULT 0 COMMENT 'times_only_page / times_first_page',
    `bounce_rank` INT(10) UNSIGNED DEFAULT 0 COMMENT '1 = lowest bounce rate',

    -- META
    `last_calculated` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_product_id` (`product_id`),
    KEY `idx_view_to_cart_rate` (`view_to_cart_rate` DESC),
    KEY `idx_engagement_score` (`engagement_score` DESC),
    KEY `idx_bounce_rate` (`bounce_rate` DESC),
    KEY `idx_view_to_cart_rank` (`view_to_cart_rank`),
    KEY `idx_engagement_rank` (`engagement_rank`),
    KEY `idx_bounce_rank` (`bounce_rank`),
    KEY `idx_last_calculated` (`last_calculated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
COMMENT='Aggregated session analytics per product';


-- ===========================================================================
-- MIGRATION HELPER: Extract existing session statics
-- ===========================================================================
-- Run this ONCE to migrate existing data from wp_aieo_sessions

INSERT IGNORE INTO `wp_aieo_sessions_statics` (
    `session_id`,
    `user_id`,
    `user_agent`,
    `device_type`,
    `browser`,
    `os`,
    `landing_page`,
    `referrer`,
    `first_timestamp`,
    `last_timestamp`,
    `total_events`,
    `total_product_views`,
    `total_add_to_cart`,
    `session_duration_seconds`,
    `created_at`,
    `updated_at`
)
SELECT
    s.session_id,
    MAX(s.user_id) AS user_id,
    MAX(s.user_agent) AS user_agent,
    MAX(s.device_type) AS device_type,
    MAX(s.browser) AS browser,
    MAX(s.os) AS os,
    (SELECT page_url FROM wp_aieo_sessions WHERE session_id = s.session_id ORDER BY timestamp LIMIT 1) AS landing_page,
    (SELECT referrer FROM wp_aieo_sessions WHERE session_id = s.session_id ORDER BY timestamp LIMIT 1) AS referrer,
    MIN(s.timestamp) AS first_timestamp,
    MAX(s.timestamp) AS last_timestamp,
    COUNT(*) AS total_events,
    SUM(CASE WHEN s.event_type = 'product_view' THEN 1 ELSE 0 END) AS total_product_views,
    SUM(CASE WHEN s.event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS total_add_to_cart,
    CAST((MAX(s.timestamp) - MIN(s.timestamp)) / 1000 AS UNSIGNED) AS session_duration_seconds,
    MIN(s.created_at) AS created_at,
    MAX(s.created_at) AS updated_at
FROM wp_aieo_sessions s
GROUP BY s.session_id;


-- ===========================================================================
-- INDEXES FOR PERFORMANCE
-- ===========================================================================

-- Optimize wp_aieo_sessions for journey building (if not already exists)
-- These indexes speed up window function queries

ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_session_event_time` (`session_id`, `event_type`, `timestamp`);

ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_product_event` (`event_type`, `event_data`(100), `timestamp`);
