-- =====================================================
-- INDEX OPTIMIZATION FOR MARIADB 11.4
-- AI Eshop Optimizer Plugin
-- =====================================================
-- This file contains optimized indexes specifically
-- designed for MariaDB 11.4 performance features
-- =====================================================

-- TABLE: wp_aieo_temp_orders (Main Orders Table)
-- =====================================================

-- Drop old single-column indexes (will be replaced with composite indexes)
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_orderid_orders;
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_productid_orders;
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_parentproductid_orders;
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_brandid_orders;
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_eponymousid_orders;
ALTER TABLE wp_aieo_temp_orders DROP INDEX IF EXISTS aieo_idx_loyaltyid_orders;

-- COMPOSITE INDEXES FOR COMMON QUERY PATTERNS
-- =====================================================

-- 1. Customer Statistics Queries (most common pattern)
-- Used by: AIEO_UpdateCustomerCentricStdStats, AIEO_UpdateCustomerCentricAdvStats
-- Pattern: WHERE EponymousId <> 0 GROUP BY EponymousId ORDER BY OrderDate
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_customer_stats (EponymousId, OrderDate, OrderSize, OrderItemsTotal, OrderId)
COMMENT 'Optimized for customer statistics calculations';

-- 2. Customer Advanced Stats with Date Analysis
-- Used by: AIEO_UpdateCustomerCentricAdvStats
-- Pattern: ROW_NUMBER() OVER (PARTITION BY EponymousId ORDER BY OrderDate)
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_customer_date_analysis (EponymousId, OrderDate, OrderItemsTotal)
COMMENT 'Optimized for days-between-orders calculations with window functions';

-- 3. Product Statistics Queries
-- Used by: AIEO_UpdateProductCentricStats
-- Pattern: WHERE ParentProductId IS NOT NULL GROUP BY ParentProductId
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_product_stats (ParentProductId, ProductId, PrimaryQty, Price, LineValue, OrderId)
COMMENT 'Optimized for product statistics aggregations';

-- 4. Order-Level Aggregations (for v2 procedures)
-- Used by: New optimized customer stats v2
-- Pattern: GROUP BY EponymousId, OrderId
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_order_aggregation (EponymousId, OrderId, OrderItemsTotal, OrderSize, PrimaryQty, LineValue)
COMMENT 'Optimized for order-level summaries before customer aggregation';

-- 5. Brand Analysis
-- Pattern: GROUP BY BrandId for brand-level statistics
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_brand_analysis (BrandId, ParentProductId, LineValue, PrimaryQty)
COMMENT 'Optimized for brand performance analysis';

-- 6. Customer Totals Calculation
-- Pattern: WHERE EponymousId <> 0, aggregating PrimaryQty, LineValue, OrderId
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_customer_totals (EponymousId, PrimaryQty, LineValue, OrderId)
COMMENT 'Optimized for TotalCustomerItems, TotalCustomerOrders, TotalCustomerTurnover';

-- 7. Top Products Per Customer (Favorite Products)
-- Pattern: GROUP BY EponymousId, ParentProductId ORDER BY SUM(PrimaryQty) DESC
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_favorite_products (EponymousId, ParentProductId, PrimaryQty)
COMMENT 'Optimized for finding customer favorite products';

-- 8. Graph UUID Frequency Calculations
-- Pattern: GROUP BY various GraphUUID fields
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_graph_prod_epo (Prod_Epo_GraphUUID)
COMMENT 'Optimized for product-customer graph frequency';

ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_graph_o_pp (O_PP_GraphUUID)
COMMENT 'Optimized for order-parent product graph frequency';

ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_graph_o_prod (O_Prod_GraphUUID)
COMMENT 'Optimized for order-product graph frequency';

-- COVERING INDEXES FOR EXPORT QUERIES
-- =====================================================
-- These indexes include all columns needed by export queries
-- MariaDB 11.4 can use these to avoid table lookups entirely

-- 9. Customer Profile Export (covers most common customer fields)
ALTER TABLE wp_aieo_temp_orders
ADD INDEX idx_customer_export_covering (
    EponymousId,
    OrderId,
    CustomerName,
    CustomerEmail,
    TotalCustomerOrders,
    TotalCustomerTurnover,
    AvgSpend,
    MaxSpend,
    LastOrderSpend,
    CurrentDBO,
    Fav1Product,
    Fav2Product,
    Fav3Product
)
COMMENT 'Covering index for customer profile exports';

-- TABLE: wp_aieo_base_order_stats (v2 Temp Table)
-- =====================================================
-- Note: This table is created dynamically by v2 procedures
-- Indexes are defined in the procedure itself

-- TABLE: wp_aieo_cumulative_customer_stats (v2 Temp Table)
-- =====================================================
-- Note: This table is created dynamically by v2 procedures
-- Indexes are defined in the procedure itself

-- WORDPRESS POSTMETA TABLE OPTIMIZATION
-- =====================================================
-- Many joins use postmeta.meta_key lookups
-- Ensure WordPress has proper indexes

-- Check if WordPress postmeta index exists, create if not
-- This is safe to run multiple times
CREATE INDEX IF NOT EXISTS idx_postmeta_post_key
ON wp_postmeta(post_id, meta_key(50))
COMMENT 'Optimized for post metadata lookups by key';

-- For meta_value searches (used in some attribute queries)
CREATE INDEX IF NOT EXISTS idx_postmeta_key_value
ON wp_postmeta(meta_key(50), meta_value(100))
COMMENT 'Optimized for metadata searches by key and value';

-- WORDPRESS TERM TABLES OPTIMIZATION
-- =====================================================
-- Category and tag lookups are frequent

-- Ensure term_relationships has composite index
CREATE INDEX IF NOT EXISTS idx_term_rel_object_taxonomy
ON wp_term_relationships(object_id, term_taxonomy_id)
COMMENT 'Optimized for product-term lookups';

-- Ensure term_taxonomy has composite index
CREATE INDEX IF NOT EXISTS idx_term_tax_term_taxonomy
ON wp_term_taxonomy(term_id, taxonomy(50))
COMMENT 'Optimized for term lookups by taxonomy type';

-- MARIADB 11.4 SPECIFIC OPTIMIZATIONS
-- =====================================================

-- Enable persistent statistics for better query planning
-- Run these after index creation
ALTER TABLE wp_aieo_temp_orders STATS_PERSISTENT=1;
ALTER TABLE wp_aieo_temp_orders STATS_AUTO_RECALC=1;

-- Set histogram bucket count (MariaDB 11.4+ feature)
-- This helps optimizer make better decisions for skewed data
SET GLOBAL histogram_size = 254;

-- Analyze tables to update statistics with new indexes
ANALYZE TABLE wp_aieo_temp_orders PERSISTENT FOR ALL;
ANALYZE TABLE wp_postmeta PERSISTENT FOR ALL;
ANALYZE TABLE wp_term_relationships PERSISTENT FOR ALL;
ANALYZE TABLE wp_term_taxonomy PERSISTENT FOR ALL;
ANALYZE TABLE wp_terms PERSISTENT FOR ALL;

-- =====================================================
-- USAGE NOTES
-- =====================================================
-- 1. Run this file AFTER plugin activation
-- 2. Re-run ANALYZE after large data imports
-- 3. Monitor slow query log for new patterns
-- 4. MariaDB 11.4+ enables:
--    - Better window function optimization
--    - Improved GROUP BY performance
--    - Histogram-based query optimization
--    - Better covering index utilization
-- =====================================================

-- Performance monitoring query (save for future reference)
/*
SELECT
    table_name,
    index_name,
    seq_in_index,
    column_name,
    cardinality,
    sub_part,
    index_comment
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'wp_aieo_temp_orders'
ORDER BY table_name, index_name, seq_in_index;
*/
