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

function aieo_apply_mariadb114_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) {
        $wpdb->query("ALTER TABLE `{$table_name}` DROP INDEX IF EXISTS `{$old_index}`");
        $results[] = "Removed old index '{$old_index}' (if existed)";
    }

    
    $indexes = array(
        
        array(
            'name' => 'idx_customer_stats',
            'columns' => 'EponymousId, OrderDate, OrderSize, OrderItemsTotal, OrderId',
            'comment' => 'Optimized for customer statistics calculations'
        ),

        
        array(
            'name' => 'idx_customer_date_analysis',
            'columns' => 'EponymousId, OrderDate, OrderItemsTotal',
            'comment' => 'Optimized for days-between-orders calculations'
        ),

        
        array(
            'name' => 'idx_product_stats',
            'columns' => 'ParentProductId, ProductId, PrimaryQty, Price, LineValue, OrderId',
            'comment' => 'Optimized for product statistics aggregations'
        ),

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

        
        array(
            'name' => 'idx_order_aggregation',
            'columns' => 'EponymousId, OrderId, OrderItemsTotal, OrderSize, PrimaryQty, LineValue',
            'comment' => 'Optimized for order-level summaries'
        ),

        
        array(
            'name' => 'idx_brand_analysis',
            'columns' => 'BrandId, ParentProductId, LineValue, PrimaryQty',
            'comment' => 'Optimized for brand performance analysis'
        ),

        
        array(
            'name' => 'idx_customer_totals',
            'columns' => 'EponymousId, PrimaryQty, LineValue, OrderId',
            'comment' => 'Optimized for customer total calculations'
        ),

        
        array(
            'name' => 'idx_favorite_products',
            'columns' => 'EponymousId, ParentProductId, PrimaryQty',
            'comment' => 'Optimized for finding customer favorites'
        ),

        
        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("ALTER TABLE `{$table_name}` STATS_PERSISTENT=1");
    $wpdb->query("ALTER TABLE `{$table_name}` STATS_AUTO_RECALC=1");
    $results[] = "✓ Enabled persistent statistics";

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

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

    return array(
        'success' => true,
        'message' => 'Index optimization completed - 20 indexes configured',
        'details' => $results
    );
}

function aieo_check_mariadb_version()
{
    global $wpdb;

    $version = $wpdb->get_var("SELECT VERSION()");
    $is_mariadb = stripos($version, 'mariadb') !== false;

    
    preg_match('/(\d+\.\d+\.\d+)/', $version, $matches);
    $version_number = $matches[1] ?? '0.0.0';

    list($major, $minor, $patch) = explode('.', $version_number);
    $version_code = $major * 10000 + $minor * 100 + $patch;

    if ($is_mariadb) {
        $target_code = 11 * 10000 + 4 * 100 + 0; 

        return array(
            'is_mariadb' => true,
            'is_mysql' => false,
            'version' => $version,
            'version_number' => $version_number,
            'can_optimize' => $version_code >= $target_code,
            'db_type' => 'mariadb',
            'message' => $version_code >= $target_code
                ? "Running MariaDB {$version_number} - Optimization recommended!"
                : "Running MariaDB {$version_number} - Upgrade to 11.4+ recommended for full optimization benefits"
        );
    } else {
        
        $mysql_target = 8 * 10000 + 0 * 100 + 0; 

        return array(
            'is_mariadb' => false,
            'is_mysql' => $version_code >= $mysql_target,
            'version' => $version,
            'version_number' => $version_number,
            'can_optimize' => $version_code >= $mysql_target,
            'db_type' => $version_code >= $mysql_target ? 'mysql8' : 'mysql_old',
            'message' => $version_code >= $mysql_target
                ? "Running MySQL {$version_number} - Optimization recommended!"
                : "Running MySQL {$version_number} - Upgrade to MySQL 8.0+ recommended for optimization benefits"
        );
    }
}

function aieo_apply_mariadb114_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' => 'Catalogue creation 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' => 'Catalogue batch SELECT covering index'
                ),
            )
        ),

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

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

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

    
    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 '{$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 '{$table_name}'";
            } else {
                $results[] = "✗ Failed to create index '{$index_config['name']}' on '{$table_name}': " . $wpdb->last_error;
            }
        }

        
        if ($table_exists) {
            $wpdb->query("ALTER TABLE `{$table_name}` STATS_PERSISTENT=1");
            $wpdb->query("ALTER TABLE `{$table_name}` STATS_AUTO_RECALC=1");
            $results[] = "✓ Enabled persistent statistics on '{$table_name}'";

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

    return array(
        'success' => true,
        'message' => 'Product vitals table optimization completed - 3 new indexes configured',
        'details' => $results
    );
}

function aieo_get_index_statistics()
{
    global $wpdb;

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

    $results = $wpdb->get_results($wpdb->prepare(
        "SELECT
            index_name,
            GROUP_CONCAT(column_name ORDER BY seq_in_index SEPARATOR ', ') as columns,
            MAX(cardinality) as cardinality,
            MAX(index_comment) as comment
        FROM information_schema.statistics
        WHERE table_schema = DATABASE()
        AND table_name = %s
        AND index_name != 'PRIMARY'
        GROUP BY index_name
        ORDER BY index_name",
        $table_name
    ), ARRAY_A);

    return $results;
}