-- ========================================
-- DATABASE INDEX ANALYSIS & VERIFICATION
-- For Product Listing Optimization
-- ========================================

-- 1. CHECK CURRENT INDEXES ON PRODUCTS TABLE
-- ========================================
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS,
    INDEX_TYPE,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'products'
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE
ORDER BY INDEX_NAME;

-- 2. CHECK TABLE SIZE AND ROW COUNT
-- ========================================
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Size_MB',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 'Data_MB',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 'Index_MB'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('products', 'organizations', 'users', 'product_quantity_list', 'wishlists', 'categories');

-- 3. TEST QUERY PERFORMANCE BEFORE INDEXES
-- ========================================
EXPLAIN 
SELECT 
    products.id,
    products.user_id,
    products.name,
    products.published,
    products.product_published_date
FROM products
JOIN organizations ON organizations.user_id = products.user_id
JOIN users ON products.user_id = users.id
WHERE products.published = 3
  AND products.product_published_date IS NOT NULL
  AND users.deleted_at IS NULL
ORDER BY products.product_published_date DESC
LIMIT 20;

-- 4. CHECK IF INDEXES ALREADY EXIST
-- ========================================
SELECT 
    'products' AS table_name,
    'idx_products_published_date' AS expected_index,
    CASE WHEN EXISTS(
        SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS 
        WHERE TABLE_SCHEMA = DATABASE() 
          AND TABLE_NAME = 'products' 
          AND INDEX_NAME = 'idx_products_published_date'
    ) THEN '✓ EXISTS' ELSE '✗ MISSING' END AS status
UNION ALL
SELECT 'products', 'idx_products_user_published',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_user_published') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'products', 'idx_products_category_published',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_category_published') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'products', 'idx_products_featured',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_featured') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'products', 'idx_products_price_type',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_price_type') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'products', 'idx_products_brand',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_products_brand') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'users', 'idx_user_verification',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_user_verification') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'organizations', 'idx_shops_user_id',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'organizations' AND INDEX_NAME = 'idx_shops_user_id') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'product_quantity_list', 'idx_qty_list_product_price',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'product_quantity_list' AND INDEX_NAME = 'idx_qty_list_product_price') THEN '✓ EXISTS' ELSE '✗ MISSING' END
UNION ALL
SELECT 'wishlists', 'idx_wishlist_user_product',
    CASE WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wishlists' AND INDEX_NAME = 'idx_wishlist_user_product') THEN '✓ EXISTS' ELSE '✗ MISSING' END;

-- 5. CHECK FOR VIRTUAL COLUMN (country_extracted)
-- ========================================
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    GENERATION_EXPRESSION,
    COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'products'
  AND COLUMN_NAME = 'country_extracted';

-- ========================================
-- MANUAL INDEX CREATION (If migration hasn't run)
-- ========================================
-- Uncomment and run these if migration fails or hasn't been executed

-- Products table indexes
-- ALTER TABLE products ADD INDEX idx_products_published_date (published, product_published_date);
-- ALTER TABLE products ADD INDEX idx_products_user_published (user_id, published);
-- ALTER TABLE products ADD INDEX idx_products_category_published (category_id, published);
-- ALTER TABLE products ADD INDEX idx_products_featured (featured);
-- ALTER TABLE products ADD INDEX idx_products_price_type (price_type);
-- ALTER TABLE products ADD INDEX idx_products_availability (availability);
-- ALTER TABLE products ADD INDEX idx_products_condition (condition);
-- ALTER TABLE products ADD INDEX idx_products_brand (brand_id);
-- ALTER TABLE products ADD INDEX idx_products_pub_feat_date (published, featured, product_published_date);
-- ALTER TABLE products ADD INDEX idx_products_type (product_type);
-- ALTER TABLE products ADD INDEX idx_products_manufacturer (manufacturer_year);

-- Users table indexes
-- ALTER TABLE users ADD INDEX idx_user_verification (email_verified_at, phone_verified);

-- Organizations table indexes
-- ALTER TABLE organizations ADD INDEX idx_shops_user_id (user_id);
-- ALTER TABLE organizations ADD INDEX idx_shops_slug_status (slug_status);

-- Product quantity list indexes
-- ALTER TABLE product_quantity_list ADD INDEX idx_qty_list_product_price (product_id, per_unit);

-- Product attributes relations indexes
-- ALTER TABLE product_attributes_relations ADD INDEX idx_attr_rel_product_price (product_id, price);

-- Wishlists table indexes
-- ALTER TABLE wishlists ADD INDEX idx_wishlist_user_product (user_id, product_id);

-- Categories table indexes
-- ALTER TABLE categories ADD INDEX idx_categories_parent_approval (parent_id, approval_status);
-- ALTER TABLE categories ADD INDEX idx_categories_level (level);

-- User plan transaction indexes
-- ALTER TABLE user_access_feature_plan_transcation ADD INDEX idx_user_plan_status (user_id, status);

-- Virtual column for JSON country extraction (MySQL 5.7.8+)
-- ALTER TABLE products ADD COLUMN country_extracted VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(caseData, '$.country'))) STORED;
-- CREATE INDEX idx_products_country ON products(country_extracted);

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

-- Test 1: Main product listing query
EXPLAIN ANALYZE
SELECT products.id, products.name, products.published, products.product_published_date
FROM products
WHERE published = 3 
  AND product_published_date IS NOT NULL
ORDER BY product_published_date DESC
LIMIT 20;

-- Test 2: Product listing with user filter
EXPLAIN ANALYZE
SELECT p.id, p.name, u.email_verified_at, u.phone_verified
FROM products p
JOIN users u ON p.user_id = u.id
WHERE p.published = 3 
  AND p.product_published_date IS NOT NULL
  AND u.deleted_at IS NULL
ORDER BY p.product_published_date DESC
LIMIT 20;

-- Test 3: Product with brand filter
EXPLAIN ANALYZE
SELECT id, name, brand_id
FROM products
WHERE published = 3
  AND brand_id IN (1, 2, 3, 4, 5)
  AND product_published_date IS NOT NULL
ORDER BY product_published_date DESC
LIMIT 20;

-- Test 4: Featured products
EXPLAIN ANALYZE
SELECT id, name, featured
FROM products
WHERE published = 3
  AND featured = 1
  AND product_published_date IS NOT NULL
ORDER BY product_published_date DESC
LIMIT 20;

-- Test 5: Price range query
EXPLAIN ANALYZE
SELECT p.id, p.name, pql.per_unit
FROM products p
JOIN product_quantity_list pql ON p.id = pql.product_id
WHERE p.published = 3
  AND pql.per_unit BETWEEN 100 AND 1000
ORDER BY p.product_published_date DESC
LIMIT 20;

-- ========================================
-- INDEX USAGE STATISTICS
-- ========================================
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    CONCAT(ROUND(CARDINALITY / TABLE_ROWS * 100, 2), '%') AS selectivity
FROM INFORMATION_SCHEMA.STATISTICS s
JOIN INFORMATION_SCHEMA.TABLES t 
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA 
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA = DATABASE()
  AND s.TABLE_NAME IN ('products', 'users', 'organizations')
  AND s.INDEX_NAME LIKE 'idx_%'
GROUP BY TABLE_NAME, INDEX_NAME, CARDINALITY, TABLE_ROWS
ORDER BY TABLE_NAME, INDEX_NAME;
