<?php
if (!defined('ABSPATH')) exit;

function aieo_apply_mysql8_optimized_indexes()
{
    global $wpdb;

    
    $table_name = $wpdb->prefix . 'aieo_temp_orders';

    
    $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");
    if (!$table_exists) {
        return array('success' => false, 'message' => 'Table does not exist yet');
    }

    $results = array();

    
    $add_index = function ($index_name, $columns, $comment = '') use ($wpdb, $table_name, &$results) {
        
        $index_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.statistics
             WHERE table_schema = DATABASE()
             AND table_name = %s
             AND index_name = %s",
            $table_name,
            $index_name
        ));

        if ($index_exists) {
            $results[] = "✓ Index '{$index_name}' already exists";
            return true;
        }

        
        $comment_sql = $comment ? "COMMENT '$comment'" : '';
        $sql = "ALTER TABLE `{$table_name}` ADD INDEX `{$index_name}` ({$columns}) {$comment_sql}";

        
        $result = $wpdb->query($sql);

        if ($result !== false) {
            $results[] = "✓ Created index '{$index_name}'";
            return true;
        } else {
            $results[] = "✗ Failed to create index '{$index_name}': " . $wpdb->last_error;
            return false;
        }
    };

    
    
    $old_indexes = array(
        'aieo_idx_orderid_orders',
        'aieo_idx_productid_orders',
        'aieo_idx_parentproductid_orders',
        'aieo_idx_brandid_orders',
        'aieo_idx_eponymousid_orders',
        'aieo_idx_loyaltyid_orders'
    );

    foreach ($old_indexes as $old_index) {
        
        $index_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE table_schema = DATABASE()
             AND table_name = %s
             AND index_name = %s",
            $table_name,
            $old_index
        ));

        if ($index_exists > 0) {
            $wpdb->query("ALTER TABLE `{$table_name}` DROP INDEX `{$old_index}`");
            $results[] = "Removed old index '{$old_index}'";
        } else {
            $results[] = "Old index '{$old_index}' not found (already removed or never existed)";
        }
    }

    
    $indexes = array(
        
        array(
            'name' => 'idx_customer_stats',
            'columns' => 'EponymousId, OrderDate DESC, OrderSize, OrderItemsTotal, OrderId',
            'comment' => 'MySQL 8: Customer stats with DESC index for recent orders'
        ),

        
        array(
            'name' => 'idx_customer_date_analysis',
            'columns' => 'EponymousId, OrderDate DESC, OrderItemsTotal',
            'comment' => 'MySQL 8: Days-between-orders with DESC for recency'
        ),

        
        array(
            'name' => 'idx_product_stats',
            'columns' => 'ParentProductId, ProductId, PrimaryQty, Price, LineValue, OrderId',
            'comment' => 'MySQL 8: Product statistics aggregations'
        ),

        
        array(
            'name' => 'idx_product_stats_filter',
            'columns' => 'ParentProductId, OriginId, LoyaltyID',
            'comment' => 'MySQL 8: Product stats WHERE clause filter optimization'
        ),

        
        array(
            'name' => 'idx_order_aggregation',
            'columns' => 'EponymousId, OrderId, OrderItemsTotal, OrderSize, PrimaryQty, LineValue',
            'comment' => 'MySQL 8: Order-level summaries with covering index'
        ),

        
        array(
            'name' => 'idx_brand_analysis',
            'columns' => 'BrandId, ParentProductId, LineValue, PrimaryQty',
            'comment' => 'MySQL 8: Brand performance analysis'
        ),

        
        array(
            'name' => 'idx_customer_totals',
            'columns' => 'EponymousId, PrimaryQty, LineValue, OrderId',
            'comment' => 'MySQL 8: Customer total calculations'
        ),

        
        array(
            'name' => 'idx_favorite_products',
            'columns' => 'EponymousId, ParentProductId, PrimaryQty DESC',
            'comment' => 'MySQL 8: Top products by quantity with DESC index'
        ),

        
        array(
            'name' => 'idx_graph_o_pp',
            'columns' => 'O_PP_GraphUUID',
            'comment' => 'Graph frequency: Order-Parent relationships'
        ),

        array(
            'name' => 'idx_graph_o_prod',
            'columns' => 'O_Prod_GraphUUID',
            'comment' => 'Graph frequency: Order-Product relationships'
        ),

        array(
            'name' => 'idx_graph_ppid',
            'columns' => 'PPIdGraphUUID',
            'comment' => 'Graph frequency: Parent Product UUIDs'
        ),

        array(
            'name' => 'idx_graph_orderid',
            'columns' => 'OrderIdGraphUUID',
            'comment' => 'Graph frequency: Order UUIDs'
        ),

        array(
            'name' => 'idx_graph_prodid',
            'columns' => 'ProdIdGraphUUID',
            'comment' => 'Graph frequency: Product UUIDs'
        ),

        array(
            'name' => 'idx_graph_epoid',
            'columns' => 'EpoIdGraphUUID',
            'comment' => 'Graph frequency: Customer UUIDs'
        ),

        array(
            'name' => 'idx_graph_o_epo',
            'columns' => 'O_Epo_GraphUUID',
            'comment' => 'Graph frequency: Order-Customer relationships'
        ),

        array(
            'name' => 'idx_graph_pp_epo',
            'columns' => 'PP_Epo_GraphUUID',
            'comment' => 'Graph frequency: Product-Customer relationships'
        ),

        array(
            'name' => 'idx_graph_prod_epo',
            'columns' => 'Prod_Epo_GraphUUID',
            'comment' => 'Graph frequency: Variant-Customer relationships'
        ),

        
        array(
            'name' => 'idx_eponymous_orderid',
            'columns' => 'EponymousId, OrderId',
            'comment' => 'V3 batched customer stats - base order stats creation'
        ),

        
        array(
            'name' => 'idx_eponymous_single',
            'columns' => 'EponymousId',
            'comment' => 'UUID generation for distinct EponymousId'
        ),

        
        array(
            'name' => 'idx_parent_product_join',
            'columns' => 'ParentProductId, ProductId',
            'comment' => 'Product catalog parent-child joins'
        ),

        
        array(
            'name' => 'idx_orderid_single',
            'columns' => 'OrderId',
            'comment' => 'Order lookups and merging'
        ),

        
        array(
            'name' => 'idx_productid_unsold',
            'columns' => 'ProductId',
            'comment' => 'Unsold products LEFT JOIN detection'
        ),
    );

    
    foreach ($indexes as $index) {
        $add_index($index['name'], $index['columns'], $index['comment']);
    }

    
    $wpdb->query("ANALYZE TABLE `{$table_name}`");
    $results[] = "✓ Analyzed table and updated statistics";

    
    
    $histogram_columns = array('EponymousId', 'OrderDate', 'ParentProductId', 'ProductId', 'OrderId', 'BrandId');
    $histogram_list = implode(', ', $histogram_columns);

    $histogram_result = $wpdb->query("ANALYZE TABLE `{$table_name}` UPDATE HISTOGRAM ON {$histogram_list}");

    if ($histogram_result !== false) {
        $results[] = "✓ Created histogram statistics for 6 key columns (MySQL 8 feature)";
        $results[] = "ℹ Histograms improve query optimizer accuracy for customer and product queries";
    } else {
        $results[] = "⚠ Histogram creation skipped (requires ANALYZE privilege)";
    }

    
    
    $results[] = "ℹ Helper tables have built-in indexes (created during export)";

    return array(
        'success' => true,
        'message' => 'MySQL 8 index optimization completed - 20 indexes + histogram statistics',
        'details' => $results
    );
}

function aieo_apply_mysql8_product_vitals_indexes()
{
    global $wpdb;

    $results = array();

    
    $tables_to_optimize = array(
        array(
            'table' => $wpdb->prefix . 'aieo_core_product_vitals',
            'indexes' => array(
                
                array(
                    'name' => 'idx_catalogue_parent_join',
                    'columns' => 'product_id, PPIdGraphUUID',
                    'comment' => 'MySQL 8: Catalogue parent product JOIN optimization'
                ),

                
                array(
                    'name' => 'idx_catalogue_batch_select',
                    'columns' => 'product_id, parent_product_id, sku, parent_sku, brand_id, menu_id, head_id, subcat_id',
                    'comment' => 'MySQL 8: Catalogue batch SELECT covering index'
                ),
            ),
            'histogram_columns' => array('product_id', 'parent_product_id', 'brand_id')
        ),

        array(
            'table' => $wpdb->prefix . 'aieo_core_product_attributes',
            'indexes' => array(
                
            ),
            'histogram_columns' => array('product_id', 'brand_id')
        ),

        array(
            'table' => $wpdb->prefix . 'aieo_core_variation_vitals',
            'indexes' => array(
                
                array(
                    'name' => 'idx_variation_parent_join',
                    'columns' => 'parent_id, product_id',
                    'comment' => 'MySQL 8: Variation to parent product JOIN optimization'
                ),
            ),
            'histogram_columns' => array('parent_id', 'product_id')
        ),

        array(
            'table' => $wpdb->prefix . 'aieo_core_product_category_hierarchy',
            'indexes' => array(
                
            ),
            'histogram_columns' => array('product_id', 'top_level_category_id')
        )
    );

    
    foreach ($tables_to_optimize as $table_config) {
        $table_name = $table_config['table'];

        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");
        if (!$table_exists) {
            $results[] = "⊘ Table '{$table_name}' does not exist yet (will be created during export)";
            continue;
        }

        
        foreach ($table_config['indexes'] as $index_config) {
            
            $index_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.statistics
                 WHERE table_schema = DATABASE()
                 AND table_name = %s
                 AND index_name = %s",
                $table_name,
                $index_config['name']
            ));

            if ($index_exists) {
                $results[] = "✓ Index '{$index_config['name']}' already exists on '{$table_name}'";
                continue;
            }

            
            $comment_sql = $index_config['comment'] ? "COMMENT '{$index_config['comment']}'" : '';
            $sql = "ALTER TABLE `{$table_name}` ADD INDEX `{$index_config['name']}` ({$index_config['columns']}) {$comment_sql}";

            
            $result = $wpdb->query($sql);

            if ($result !== false) {
                $results[] = "✓ Created index '{$index_config['name']}' on '{$table_name}'";
            } else {
                $results[] = "✗ Failed to create index '{$index_config['name']}' on '{$table_name}': " . $wpdb->last_error;
            }
        }

        
        if (!empty($table_config['histogram_columns'])) {
            $histogram_list = implode(', ', $table_config['histogram_columns']);
            $histogram_result = $wpdb->query("ANALYZE TABLE `{$table_name}` UPDATE HISTOGRAM ON {$histogram_list}");

            if ($histogram_result !== false) {
                $results[] = "✓ Created histogram statistics on '{$table_name}' (" . count($table_config['histogram_columns']) . " columns)";
            } else {
                $results[] = "⚠ Histogram creation skipped for '{$table_name}' (requires ANALYZE privilege)";
            }
        }

        
        $wpdb->query("ANALYZE TABLE `{$table_name}`");
        $results[] = "✓ Analyzed table '{$table_name}'";
    }

    return array(
        'success' => true,
        'message' => 'MySQL 8 product vitals optimization completed - 3 new indexes + histograms',
        'details' => $results
    );
}