<?php

if (!defined('ABSPATH')) {
    exit;
}

class AIEO_Session_Analytics_Processor {

    
    private static $instance = null;

    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    
    private function __construct() {
        
        add_action('aieo_export_stages', array($this, 'add_export_stages'));
    }

    
    public function add_export_stages($stages) {
        $stages['process_session_journeys'] = array(
            'name' => __('Process Session Journeys', 'ai-eshop-optimizer'),
            'callback' => array($this, 'process_session_journeys_batch'),
            'description' => __('Build session journey sequences with engagement metrics', 'ai-eshop-optimizer')
        );

        $stages['aggregate_session_metrics'] = array(
            'name' => __('Aggregate Session Metrics', 'ai-eshop-optimizer'),
            'callback' => array($this, 'aggregate_session_metrics'),
            'description' => __('Calculate product-level session analytics', 'ai-eshop-optimizer')
        );

        return $stages;
    }

    
    public function create_analytics_tables() {
        global $wpdb;

        $sql_file = plugin_dir_path(__FILE__) . 'sql/session-analytics-schema.sql';

        if (!file_exists($sql_file)) {
            return new WP_Error('file_not_found', __('Schema file not found', 'ai-eshop-optimizer'));
        }

        $sql = file_get_contents($sql_file);

        
        $sql = str_replace('wp_', $wpdb->prefix, $sql);

        
        $statements = array_filter(array_map('trim', explode(';', $sql)));

        foreach ($statements as $statement) {
            if (!empty($statement) && strpos($statement, '--') !== 0) {
                
                $wpdb->query($statement);
            }
        }

        return true;
    }

    
    public function create_stored_procedures() {
        
        
        static $installed = false;
        if ($installed) {
            return true;
        }
        if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) {
            return false; 
        }

        $base  = plugin_dir_path(__FILE__) . 'sql/';
        $files = array(
            'session-analytics-procedures.sql',
            'session-analytics-procedures-optimized.sql',
        );
        $found = false;
        foreach ($files as $f) {
            if (file_exists($base . $f)) {
                $this->install_procedures_file($base . $f);
                $found = true;
            }
        }

        if (!$found) {
            return new WP_Error('file_not_found', __('Procedures file not found', 'ai-eshop-optimizer'));
        }

        $installed = true;
        return true;
    }

    
    private function install_procedures_file($path) {
        global $wpdb;

        $sql = file_get_contents($path);
        if ($sql === false) {
            return;
        }
        
        $sql = str_replace('wp_', $wpdb->prefix, $sql);
        
        $sql = preg_replace('/^\s*DELIMITER\s+\S+\s*$/mi', '', $sql);

        foreach (explode('//', $sql) as $part) {
            $keep = array();
            foreach (preg_split('/\r?\n/', $part) as $ln) {
                $t = ltrim($ln);
                if ($t === '' || substr($t, 0, 2) === '--') {
                    continue;
                }
                $keep[] = $ln;
            }
            $stmt = trim(implode("\n", $keep));
            if ($stmt === '') {
                continue;
            }
            
            $wpdb->query($stmt);
        }
    }

    
    public function process_session_journeys_batch($batch_offset = 0, $progress_step = 0) {
        global $wpdb;

        $batch_size = 1000;  
        $days_back = 30;     

        
        $this->create_stored_procedures();

        
        $use_optimized = $this->has_optimized_columns();

        
        if ($progress_step === 0) {
            $cutoff_timestamp = (time() - ($days_back * 86400)) * 1000;
            $total_sessions = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id)
                 FROM {$wpdb->prefix}aieo_sessions
                 WHERE timestamp >= %d",
                $cutoff_timestamp
            ));

            
            set_transient('aieo_journey_total_sessions', $total_sessions, 3600);
        } else {
            $total_sessions = get_transient('aieo_journey_total_sessions');
        }

        
        $procedure_name = $use_optimized ? 'AIEO_Build_Session_Journeys_Optimized' : 'AIEO_Build_Session_Journeys';

        
        $wpdb->query($wpdb->prepare(
            "CALL {$wpdb->prefix}{$procedure_name}(%d, %d, %d)",
            $batch_size,
            $batch_offset,
            $days_back
        ));

        $current_processed = $batch_offset + $batch_size;
        $is_complete = $current_processed >= $total_sessions;

        
        $response = array(
            'complete' => $is_complete,
            'current' => $current_processed,
            'total' => $total_sessions,
            'batch_offset' => $current_processed,
            'progress_step' => $progress_step + 1,
            'message' => sprintf(
                
                __('Processed %1$d of %2$d sessions%3$s', 'ai-eshop-optimizer'),
                min($current_processed, $total_sessions),
                $total_sessions,
                $use_optimized ? ' (optimized)' : ''
            )
        );

        
        if ($is_complete) {
            delete_transient('aieo_journey_total_sessions');
        }

        return $response;
    }

    
    private function has_optimized_columns() {
        global $wpdb;

        $column = $wpdb->get_results($wpdb->prepare(
            "SHOW COLUMNS FROM {$wpdb->prefix}aieo_sessions LIKE %s",
            'product_id'
        ));

        return !empty($column);
    }

    
    public function aggregate_session_metrics($batch_offset = 0, $progress_step = 0) {
        global $wpdb;

        $days_back = 30;  

        
        $this->create_stored_procedures();

        
        
        
        
        
        
        if ( ! class_exists( 'AIEO_Aggregate_Cron' ) ) {
            $cron_file = plugin_dir_path( dirname( __FILE__ ) ) . 'includes/cron/class-aieo-aggregate-cron.php';
            if ( file_exists( $cron_file ) ) { require_once $cron_file; }
        }
        if ( class_exists( 'AIEO_Aggregate_Cron' ) ) {
            AIEO_Aggregate_Cron::compute_engagement_daily_range( $days_back );
        }

        
        
        if ( $this->procedure_exists( $wpdb->prefix . 'AIEO_Aggregate_Product_Metrics_From_Daily' ) ) {
            aieo_call_sproc(
                $wpdb->prepare(
                    "CALL {$wpdb->prefix}AIEO_Aggregate_Product_Metrics_From_Daily(%d)",
                    $days_back
                ),
                array( $this, 'create_stored_procedures' )
            );
        } else {
            aieo_call_sproc(
                $wpdb->prepare(
                    "CALL {$wpdb->prefix}AIEO_Aggregate_Product_Session_Metrics(%d)",
                    $days_back
                ),
                array( $this, 'create_stored_procedures' )
            );
        }

        
        $product_count = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_product_session_metrics"
        );

        return array(
            'complete' => true,
            'current' => $product_count,
            'total' => $product_count,
            'message' => sprintf(
                
                __('Calculated metrics for %d products', 'ai-eshop-optimizer'),
                $product_count
            )
        );
    }

    
    private function procedure_exists( $name ) {
        global $wpdb;
        $found = $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.ROUTINES
              WHERE ROUTINE_SCHEMA = DATABASE()
                AND ROUTINE_TYPE = 'PROCEDURE'
                AND ROUTINE_NAME = %s",
            $name
        ) );
        return (int) $found > 0;
    }

    
    public function get_product_rankings($rank_type = 'conversion', $top_bottom = 'top', $limit = 30) {
        global $wpdb;

        $results = aieo_call_sproc(
            $wpdb->prepare(
                "CALL {$wpdb->prefix}AIEO_Get_Product_Rankings(%s, %s, %d)",
                $rank_type,
                $top_bottom,
                $limit
            ),
            array( $this, 'create_stored_procedures' ),
            'get_results'
        );

        return $results;
    }

    
    public function get_analytics_summary() {
        global $wpdb;

        $summary = aieo_call_sproc(
            "CALL {$wpdb->prefix}AIEO_Get_Analytics_Summary()",
            array( $this, 'create_stored_procedures' ),
            'get_row'
        );

        return $summary;
    }

    
    public function cleanup_old_journeys($retention_days = 30) {
        global $wpdb;

        $result = aieo_call_sproc(
            $wpdb->prepare(
                "CALL {$wpdb->prefix}AIEO_Cleanup_Old_Journeys(%d)",
                $retention_days
            ),
            array( $this, 'create_stored_procedures' ),
            'get_row'
        );

        return $result;
    }

    
    public function analytics_tables_exist() {
        global $wpdb;

        $tables = array(
            $wpdb->prefix . 'aieo_sessions_statics',
            $wpdb->prefix . 'aieo_session_journeys',
            $wpdb->prefix . 'aieo_product_session_metrics'
        );

        foreach ($tables as $table) {
            $exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
            if ($exists !== $table) {
                return false;
            }
        }

        return true;
    }

    
    public function migrate_existing_data() {
        global $wpdb;

        
        if (!$this->analytics_tables_exist()) {
            $this->create_analytics_tables();
        }

        
        $wpdb->query("
            INSERT IGNORE INTO {$wpdb->prefix}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 {$wpdb->prefix}aieo_sessions WHERE session_id = s.session_id ORDER BY timestamp LIMIT 1) AS landing_page,
                (SELECT referrer FROM {$wpdb->prefix}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 {$wpdb->prefix}aieo_sessions s
            GROUP BY s.session_id
        ");

        $migrated_sessions = $wpdb->rows_affected;

        return array(
            'success' => true,
            'sessions_migrated' => $migrated_sessions,
            
            'message' => sprintf(__('Migrated %d sessions to new schema', 'ai-eshop-optimizer'), $migrated_sessions)
        );
    }

    
    public function schedule_analytics_cron() {
        
        if (!wp_next_scheduled('aieo_process_session_journeys')) {
            wp_schedule_event(strtotime('02:00:00'), 'daily', 'aieo_process_session_journeys');
        }

        
        if (!wp_next_scheduled('aieo_aggregate_session_metrics')) {
            wp_schedule_event(strtotime('Sunday 03:00:00'), 'weekly', 'aieo_aggregate_session_metrics');
        }

        
        if (!wp_next_scheduled('aieo_cleanup_old_journeys')) {
            wp_schedule_event(strtotime('first day of next month 04:00:00'), 'monthly', 'aieo_cleanup_old_journeys');
        }

        
        
        
        
        add_action('aieo_process_session_journeys', array($this, 'cron_process_journeys_scheduled'));
        
        
        add_action('aieo_process_session_journeys_continue', array($this, 'cron_process_journeys_scheduled'));
        add_action('aieo_aggregate_session_metrics', array($this, 'cron_aggregate_metrics'));
        add_action('aieo_cleanup_old_journeys', array($this, 'cron_cleanup_old_data'));
    }

    
    private function session_tracking_on() {
        return '1' === (string) get_option('aieo_session_tracking_enabled', '0');
    }

    
    public function cron_process_journeys_scheduled($budget = 20.0) {
        if (!$this->session_tracking_on()) { return; }
        $this->cron_process_journeys($budget);
    }

    
    public static function bootstrap() {
        $instance = self::get_instance();
        $instance->schedule_analytics_cron();

        if (is_admin()) {
            
            
            
            
            add_action('wp_ajax_aieo_session_analytics_rebuild', array($instance, 'ajax_rebuild_now'));
            add_action('wp_ajax_aieo_session_analytics_export', array($instance, 'ajax_export'));
        }
    }

    
    public function ajax_export() {
        
        
        
        if ( ! current_user_can( 'manage_woocommerce' ) && ! current_user_can( 'manage_options' ) ) {
            wp_die( esc_html__( 'Permission denied.', 'ai-eshop-optimizer' ), '', array( 'response' => 403 ) );
        }
        $nonce = isset( $_GET['_wpnonce'] ) ? sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ) : '';
        if ( ! wp_verify_nonce( $nonce, 'aieo_session_analytics' ) ) {
            wp_die( esc_html__( 'Expired link — reload the Session Analytics page and try again.', 'ai-eshop-optimizer' ), '', array( 'response' => 403 ) );
        }
        $type = isset( $_GET['type'] ) ? sanitize_key( wp_unslash( $_GET['type'] ) ) : 'conversion';
        if ( ! in_array( $type, array( 'conversion', 'engagement', 'bounce' ), true ) ) {
            $type = 'conversion';
        }

        if ( ! class_exists( '\Rooster\Admin\Export' ) && function_exists( 'rooster_admin_builders' ) ) {
            rooster_admin_builders();
        }
        if ( ! class_exists( '\Rooster\Admin\Export' ) ) {
            wp_die( esc_html__( 'XLSX writer unavailable.', 'ai-eshop-optimizer' ) );
        }

        
        
        
        
        $rows = $this->get_export_rows( $type );

        
        $id      = array( 'type' => 'number' );
        $count   = array( 'format' => array( 'decimals' => 0 ) );
        $decimal = array( 'format' => array( 'decimals' => 2 ) );
        $columns = array(
            'conversion' => array(
                'name'  => 'Product',
                'pid'   => array( 'label' => 'Product ID' ) + $id,
                'views' => array( 'label' => 'Views' ) + $count,
                'adds'  => array( 'label' => 'Add-to-cart' ) + $count,
                'rate'  => array( 'label' => 'Conv. rate %' ) + $decimal,
            ),
            'engagement' => array(
                'name'   => 'Product',
                'pid'    => array( 'label' => 'Product ID' ) + $id,
                'views'  => array( 'label' => 'Views' ) + $count,
                'score'  => array( 'label' => 'Engagement' ) + $decimal,
                'secs'   => array( 'label' => 'Avg sec' ) + $decimal,
                'scroll' => array( 'label' => 'Avg scroll %' ) + $decimal,
            ),
            'bounce'     => array(
                'name'     => 'Product',
                'pid'      => array( 'label' => 'Product ID' ) + $id,
                'views'    => array( 'label' => 'Views' ) + $count,
                'landings' => array( 'label' => 'Landings' ) + $count,
                'bounces'  => array( 'label' => 'Bounces' ) + $count,
                'rate'     => array( 'label' => 'Bounce %' ) + $decimal,
            ),
        );
        $titles = array(
            'conversion' => __( 'Top converters', 'ai-eshop-optimizer' ),
            'engagement' => __( 'Most engaging', 'ai-eshop-optimizer' ),
            'bounce'     => __( 'Lowest bounce', 'ai-eshop-optimizer' ),
        );

        $lines = array();
        foreach ( (array) $rows as $r ) {
            $pid  = (int) ( $r->product_id ?? 0 );
            $line = array(
                'name'  => ! empty( $r->product_name ) ? $r->product_name : ( '#' . $pid ),
                'pid'   => $pid,
                'views' => (int) ( $r->total_views ?? 0 ),
            );
            if ( 'conversion' === $type ) {
                $line += array( 'adds' => (int) ( $r->total_add_to_cart ?? 0 ), 'rate' => (float) ( $r->conversion_rate_pct ?? 0 ) );
            } elseif ( 'engagement' === $type ) {
                $line += array( 'score' => (float) ( $r->engagement_score ?? 0 ), 'secs' => (float) ( $r->avg_seconds_on_page ?? 0 ), 'scroll' => (float) ( $r->avg_scroll_pct ?? 0 ) );
            } else {
                $line += array( 'landings' => (int) ( $r->landing_count ?? 0 ), 'bounces' => (int) ( $r->bounce_count ?? 0 ), 'rate' => (float) ( $r->bounce_rate_pct ?? 0 ) );
            }
            $lines[] = $line;
        }

        \Rooster\Admin\Export::xlsx( 'session-analytics-' . $type . '-' . gmdate( 'Ymd' ) . '.xlsx', array( array( 'name' => $titles[ $type ], 'columns' => $columns[ $type ], 'rows' => $lines ) ) );
        exit;
    }

    
    private function get_export_rows( $type ) {
        global $wpdb;
        $m = $wpdb->prefix . 'aieo_product_session_metrics';
        $p = $wpdb->posts;

        if ( 'engagement' === $type ) {
            $sql = "SELECT m.product_id, pp.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
                      FROM {$m} m
                      LEFT JOIN {$p} pp ON pp.ID = m.product_id
                  ORDER BY m.engagement_rank ASC, m.total_views DESC";
        } elseif ( 'bounce' === $type ) {
            $sql = "SELECT m.product_id, pp.post_title AS product_name, m.total_views,
                           m.times_first_page AS landing_count,
                           m.times_only_page  AS bounce_count,
                           ROUND(m.bounce_rate * 100, 2) AS bounce_rate_pct
                      FROM {$m} m
                      LEFT JOIN {$p} pp ON pp.ID = m.product_id
                  ORDER BY m.bounce_rank ASC, m.total_views DESC";
        } else {
            $sql = "SELECT m.product_id, pp.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
                      FROM {$m} m
                      LEFT JOIN {$p} pp ON pp.ID = m.product_id
                  ORDER BY m.view_to_cart_rank ASC, m.total_views DESC";
        }

        
        return $wpdb->get_results( $sql );
    }

    
    public function ajax_rebuild_now() {
        check_ajax_referer('aieo_session_analytics', '_wpnonce');
        
        
        if (!current_user_can('manage_woocommerce') && !current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }
        global $wpdb;

        
        
        
        
        
        if (!empty($_POST['restart'])) {
            delete_option('aieo_journeys_build_offset');
            delete_option('aieo_journeys_build_total');
        }

        $this->cron_process_journeys(4.0); 

        
        
        
        
        if ( ! class_exists( 'AIEO_Content_Vitals' ) && defined( 'AIEO_DIR' ) && file_exists( AIEO_DIR . 'includes/class-aieo-content-vitals.php' ) ) {
            require_once AIEO_DIR . 'includes/class-aieo-content-vitals.php';
        }
        if ( class_exists( 'AIEO_Content_Vitals' ) ) {
            AIEO_Content_Vitals::compute_daily( gmdate( 'Y-m-d', time() - DAY_IN_SECONDS ) );
            AIEO_Content_Vitals::compute_daily( gmdate( 'Y-m-d' ) );
            AIEO_Content_Vitals::build_vitals();
        }

        $complete  = (false === get_option('aieo_journeys_build_offset'));
        $total     = (int) get_option('aieo_journeys_build_total', 0);
        $offset    = (int) get_option('aieo_journeys_build_offset', 0);
        $journeys  = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}aieo_session_journeys");
        $metrics   = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}aieo_product_session_metrics");
        $processed = $complete ? $journeys : min($offset, $total);

        wp_send_json_success(array(
            'complete'  => $complete,
            'total'     => $total,
            'processed' => $processed,
            'journeys'  => $journeys,
            'metrics'   => $metrics,
            'message'   => $complete
                ? sprintf(
                    
                    __('Rebuild complete — %1$s journeys, %2$s product metrics.', 'ai-eshop-optimizer'),
                    number_format_i18n($journeys),
                    number_format_i18n($metrics)
                )
                : sprintf(
                    
                    __('Building… %1$s / %2$s sessions, %3$s journeys', 'ai-eshop-optimizer'),
                    number_format_i18n(min($offset, $total)),
                    number_format_i18n($total),
                    number_format_i18n($journeys)
                ),
        ));
    }

    
    public function cron_process_journeys($budget = 20.0) {
        global $wpdb;

        $lock = $wpdb->get_var("SELECT GET_LOCK('aieo_build_journeys', 0)");
        if ($lock !== '1' && $lock !== 1) {
            return; 
        }

        $this->create_stored_procedures();

        $journeys_table = $wpdb->prefix . 'aieo_session_journeys';
        $use_optimized  = $this->has_optimized_columns();
        $procedure      = $use_optimized ? 'AIEO_Build_Session_Journeys_Optimized' : 'AIEO_Build_Session_Journeys';
        $batch_size     = 1000;
        $days_back      = 30;
        $cutoff_ms      = (time() - ($days_back * 86400)) * 1000;

        $offset = (int) get_option('aieo_journeys_build_offset', 0);

        if ($offset === 0) {
            
            
            $wpdb->query("TRUNCATE TABLE {$journeys_table}");
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aieo_sessions WHERE timestamp >= %d",
                $cutoff_ms
            ));
            update_option('aieo_journeys_build_total', $total, false);
        } else {
            $total = (int) get_option('aieo_journeys_build_total', 0);
        }

        $started = microtime(true); 
        do {
            
            $wpdb->query($wpdb->prepare(
                "CALL {$wpdb->prefix}{$procedure}(%d, %d, %d)",
                $batch_size,
                $offset,
                $days_back
            ));
            $offset += $batch_size;
            update_option('aieo_journeys_build_offset', $offset, false);
        } while ($offset < $total && (microtime(true) - $started) < $budget);

        if ($offset >= $total) {
            
            delete_option('aieo_journeys_build_offset');
            delete_option('aieo_journeys_build_total');
            
            
            
            wp_clear_scheduled_hook('aieo_process_session_journeys_continue');
            $this->aggregate_session_metrics();
            if ( defined('WP_DEBUG') && WP_DEBUG ) { error_log('AIEO: Session journeys rebuild + aggregate complete (' . (int) $total . ' sessions).'); }
        } else {
            
            
            if (!wp_next_scheduled('aieo_process_session_journeys_continue')) {
                wp_schedule_single_event(time() + 60, 'aieo_process_session_journeys_continue');
            }
        }

        $wpdb->query("SELECT RELEASE_LOCK('aieo_build_journeys')");
    }

    
    public function cron_aggregate_metrics() {
        if (!$this->session_tracking_on()) { return; }
        $result = $this->aggregate_session_metrics();
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Session metrics aggregation completed. ' . $result['message']); }
    }

    
    public function cron_cleanup_old_data() {
        if (!$this->session_tracking_on()) { return; }
        $retention_days = (int) get_option('aieo_session_journey_retention_days', 90);
        $result = $this->cleanup_old_journeys($retention_days);
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Cleanup completed. Deleted ' . $result->rows_deleted . ' old journey records.'); }
    }
}