/**
 * Session Schema Migration - Add Optimized Columns
 *
 * This migration adds extracted columns to your EXISTING wp_aieo_sessions table
 * to improve query performance without breaking current functionality.
 *
 * DATABASE COMPATIBILITY NOTES:
 * =============================
 * - IF NOT EXISTS syntax requires MySQL 8.0+ or MariaDB 10.5+
 * - For older versions (MySQL 5.5-5.7, MariaDB 10.0-10.4):
 *   → Use the WordPress admin UI migration tool (AI eShop Optimizer → Schema Migration)
 *   → The PHP migrator checks for column existence before adding (compatible with all versions)
 * - If using this SQL file manually on older versions:
 *   → Remove "IF NOT EXISTS" from ALTER TABLE statements
 *   → Expect errors if columns already exist (safe to ignore)
 *
 * RECOMMENDED: Use the admin UI migration tool for best compatibility
 *
 * @package AI_eShop_Optimizer
 * @version 3.5.0
 */

-- ===========================================================================
-- STEP 1: Add Hot Fields to wp_aieo_sessions (Non-Breaking)
-- ===========================================================================

-- Add product_id column (extracted from event_data JSON)
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN IF NOT EXISTS `product_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Extracted from event_data for fast filtering' AFTER `event_type`;

-- Add category_id column
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN IF NOT EXISTS `category_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'For category_view events' AFTER `product_id`;

-- Add scroll_depth column
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN IF NOT EXISTS `scroll_depth` TINYINT(3) UNSIGNED DEFAULT NULL COMMENT 'Percentage 0-100' AFTER `category_id`;

-- Add click_count column
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN IF NOT EXISTS `click_count` SMALLINT(5) UNSIGNED DEFAULT NULL COMMENT 'Number of clicks on page' AFTER `scroll_depth`;

-- Add product_name for easier debugging (optional)
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN IF NOT EXISTS `product_name` VARCHAR(512) DEFAULT NULL COMMENT 'Cached product name' AFTER `product_id`;


-- ===========================================================================
-- STEP 2: Add Performance Indexes
-- ===========================================================================

-- Index for filtering by product
ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_product_id` (`product_id`);

-- Composite index for product analytics (most important!)
ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_product_event` (`product_id`, `event_type`, `timestamp`);

-- Index for category analytics
ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_category_id` (`category_id`);

-- Composite index for session-product queries
ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_session_product` (`session_id`, `product_id`, `event_type`);

-- Index for engagement queries
ALTER TABLE `wp_aieo_sessions`
ADD INDEX IF NOT EXISTS `idx_session_event_time` (`session_id`, `event_type`, `timestamp`);


-- ===========================================================================
-- STEP 3: Backfill Existing Data (Run in batches for large tables)
-- ===========================================================================

-- IMPORTANT: For tables with 100K+ rows, run these in batches using LIMIT
-- Example: UPDATE ... WHERE id BETWEEN 1 AND 10000

-- Extract product_id from JSON
UPDATE `wp_aieo_sessions`
SET `product_id` = CAST(JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.product_id')) AS UNSIGNED)
WHERE `event_type` IN ('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist')
AND `product_id` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.product_id') IS NOT NULL;

-- Extract product_name from JSON
UPDATE `wp_aieo_sessions`
SET `product_name` = JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.product_name'))
WHERE `event_type` IN ('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist')
AND `product_name` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.product_name') IS NOT NULL;

-- Extract category_id from JSON
UPDATE `wp_aieo_sessions`
SET `category_id` = CAST(JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.category_id')) AS UNSIGNED)
WHERE `event_type` = 'category_view'
AND `category_id` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.category_id') IS NOT NULL;

-- Extract scroll_depth from JSON
UPDATE `wp_aieo_sessions`
SET `scroll_depth` = CAST(JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.scroll_depth')) AS UNSIGNED)
WHERE `event_type` = 'scroll_depth'
AND `scroll_depth` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.scroll_depth') IS NOT NULL;

-- Alternative: Extract scroll_depth from nested depth field
UPDATE `wp_aieo_sessions`
SET `scroll_depth` = CAST(JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.depth')) AS UNSIGNED)
WHERE `event_type` = 'scroll_depth'
AND `scroll_depth` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.depth') IS NOT NULL;

-- Extract click_count from JSON
UPDATE `wp_aieo_sessions`
SET `click_count` = CAST(JSON_UNQUOTE(JSON_EXTRACT(`event_data`, '$.click_count')) AS UNSIGNED)
WHERE `event_type` = 'click'
AND `click_count` IS NULL
AND `event_data` IS NOT NULL
AND JSON_EXTRACT(`event_data`, '$.click_count') IS NOT NULL;


-- ===========================================================================
-- STEP 4: Update wp_aieo_sessions_statics (Add Summary Columns)
-- ===========================================================================

-- Add summary columns to statics table
ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `last_timestamp` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Last event timestamp' AFTER `first_timestamp`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `total_events` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Count of all events' AFTER `last_timestamp`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `total_product_views` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Count of product_view events' AFTER `total_events`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `total_add_to_cart` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Count of add_to_cart events' AFTER `total_product_views`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `total_scroll_events` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Count of scroll_depth events' AFTER `total_add_to_cart`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `total_click_events` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Count of click events' AFTER `total_scroll_events`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `session_duration_seconds` INT(10) UNSIGNED DEFAULT 0 COMMENT 'Session duration in seconds' AFTER `total_click_events`;

ALTER TABLE `wp_aieo_sessions_statics`
ADD COLUMN IF NOT EXISTS `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Add index for updated_at
ALTER TABLE `wp_aieo_sessions_statics`
ADD INDEX IF NOT EXISTS `idx_updated_at` (`updated_at`);


-- ===========================================================================
-- STEP 5: Backfill Summary Data in Statics Table
-- ===========================================================================

-- Update summary statistics for existing sessions
UPDATE `wp_aieo_sessions_statics` ss
JOIN (
    SELECT
        session_id,
        MAX(timestamp) AS last_timestamp,
        COUNT(*) AS total_events,
        SUM(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS total_product_views,
        SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS total_add_to_cart,
        SUM(CASE WHEN event_type = 'scroll_depth' THEN 1 ELSE 0 END) AS total_scroll_events,
        SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS total_click_events,
        CAST((MAX(timestamp) - MIN(timestamp)) / 1000 AS UNSIGNED) AS session_duration_seconds
    FROM `wp_aieo_sessions`
    GROUP BY session_id
) s ON s.session_id = ss.session_id
SET
    ss.last_timestamp = s.last_timestamp,
    ss.total_events = s.total_events,
    ss.total_product_views = s.total_product_views,
    ss.total_add_to_cart = s.total_add_to_cart,
    ss.total_scroll_events = s.total_scroll_events,
    ss.total_click_events = s.total_click_events,
    ss.session_duration_seconds = s.session_duration_seconds,
    ss.updated_at = NOW();


-- ===========================================================================
-- STEP 6 (OPTIONAL): Convert event_type to ENUM for storage efficiency
-- ===========================================================================

-- WARNING: This requires ALTER TABLE and will lock the table briefly
-- Only run during low-traffic periods

-- Check current distinct event types first:
-- SELECT DISTINCT event_type FROM wp_aieo_sessions ORDER BY event_type;

-- Uncomment to run:
/*
ALTER TABLE `wp_aieo_sessions`
MODIFY COLUMN `event_type` ENUM(
    'product_view',
    'category_view',
    'page_view',
    'add_to_cart',
    'remove_from_cart',
    'add_to_wishlist',
    'scroll_depth',
    'click'
) NOT NULL;
*/


-- ===========================================================================
-- STEP 7: Verify Migration Success
-- ===========================================================================

-- Check if columns were added
SHOW COLUMNS FROM `wp_aieo_sessions` LIKE 'product_id';
SHOW COLUMNS FROM `wp_aieo_sessions` LIKE 'scroll_depth';
SHOW COLUMNS FROM `wp_aieo_sessions_statics` LIKE 'total_events';

-- Check if data was backfilled
SELECT
    COUNT(*) AS total_events,
    COUNT(product_id) AS events_with_product_id,
    COUNT(scroll_depth) AS events_with_scroll_depth,
    ROUND(100.0 * COUNT(product_id) / COUNT(*), 2) AS product_id_coverage_pct
FROM `wp_aieo_sessions`
WHERE event_type IN ('product_view', 'add_to_cart');

-- Check statics summary
SELECT
    COUNT(*) AS total_sessions,
    AVG(total_events) AS avg_events_per_session,
    AVG(session_duration_seconds) AS avg_session_duration_sec,
    SUM(total_product_views) AS total_product_views_all_sessions,
    SUM(total_add_to_cart) AS total_add_to_cart_all_sessions
FROM `wp_aieo_sessions_statics`;


-- ===========================================================================
-- STEP 8: Update Existing Indexes (Analyze & Optimize)
-- ===========================================================================

-- Analyze tables to update index statistics
ANALYZE TABLE `wp_aieo_sessions`;
ANALYZE TABLE `wp_aieo_sessions_statics`;

-- Check index usage (run after some queries)
-- SHOW INDEX FROM `wp_aieo_sessions`;


-- ===========================================================================
-- ROLLBACK (if needed)
-- ===========================================================================

-- If you need to revert the changes, uncomment and run:

/*
-- Remove added columns
ALTER TABLE `wp_aieo_sessions`
DROP COLUMN IF EXISTS `product_id`,
DROP COLUMN IF EXISTS `product_name`,
DROP COLUMN IF EXISTS `category_id`,
DROP COLUMN IF EXISTS `scroll_depth`,
DROP COLUMN IF EXISTS `click_count`;

-- Remove added indexes
ALTER TABLE `wp_aieo_sessions`
DROP INDEX IF EXISTS `idx_product_id`,
DROP INDEX IF EXISTS `idx_product_event`,
DROP INDEX IF EXISTS `idx_category_id`,
DROP INDEX IF EXISTS `idx_session_product`,
DROP INDEX IF EXISTS `idx_session_event_time`;

-- Remove added columns from statics
ALTER TABLE `wp_aieo_sessions_statics`
DROP COLUMN IF EXISTS `last_timestamp`,
DROP COLUMN IF EXISTS `total_events`,
DROP COLUMN IF EXISTS `total_product_views`,
DROP COLUMN IF EXISTS `total_add_to_cart`,
DROP COLUMN IF EXISTS `total_scroll_events`,
DROP COLUMN IF EXISTS `total_click_events`,
DROP COLUMN IF EXISTS `session_duration_seconds`,
DROP COLUMN IF EXISTS `updated_at`;
*/


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

-- Test 1: Product views count (before vs after)

-- BEFORE (JSON parsing - slow):
-- SELECT COUNT(*)
-- FROM wp_aieo_sessions
-- WHERE JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.product_id')) = '123';

-- AFTER (indexed column - fast):
SELECT COUNT(*)
FROM `wp_aieo_sessions`
WHERE `product_id` = 123;

-- Test 2: Product view sequence (before vs after)

-- BEFORE (slow):
-- SELECT
--     session_id,
--     CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.product_id')) AS UNSIGNED) AS product_id,
--     ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY timestamp) AS seq
-- FROM wp_aieo_sessions
-- WHERE event_type = 'product_view';

-- AFTER (fast):
SELECT
    `session_id`,
    `product_id`,
    `product_name`,
    ROW_NUMBER() OVER (PARTITION BY `session_id` ORDER BY `timestamp`) AS seq
FROM `wp_aieo_sessions`
WHERE `event_type` = 'product_view'
AND `product_id` IS NOT NULL;

-- Test 3: Session summary with product views
SELECT
    ss.session_id,
    ss.device_type,
    ss.total_events,
    ss.total_product_views,
    ss.total_add_to_cart,
    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.total_product_views > 0
ORDER BY conversion_rate_pct DESC
LIMIT 100;


-- ===========================================================================
-- APPENDIX: Manual Migration for MySQL 5.5-5.7 / MariaDB 10.0-10.4
-- ===========================================================================

/*
For older database versions, remove "IF NOT EXISTS" and handle duplicate column errors manually:

-- Example: Adding columns without IF NOT EXISTS
ALTER TABLE `wp_aieo_sessions`
ADD COLUMN `product_id` BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Extracted from event_data' AFTER `event_type`;
-- If error "Duplicate column" - that's OK, column already exists

ALTER TABLE `wp_aieo_sessions`
ADD COLUMN `product_name` VARCHAR(512) DEFAULT NULL AFTER `product_id`;
-- If error "Duplicate column" - that's OK, column already exists

-- Example: Adding indexes without IF NOT EXISTS
ALTER TABLE `wp_aieo_sessions`
ADD INDEX `idx_product_id` (`product_id`);
-- If error "Duplicate key name" - that's OK, index already exists

-- However, RECOMMENDED: Use the WordPress admin UI migration tool instead
-- It handles all compatibility checks automatically
*/
