AiDOPWarehouse/scripts/migrations/20260512_s8_exception_indexes.sql

-- ORDER-FLOW-S8-INTEGRATED-DOMAIN-RESET-1 t1c
-- Target table: ado_s8_exception
-- Purpose: add 3 indexes for order-flow related exception queries
--          (related_object_code aggregation, order_flow_code filter,
--           related+flow joint lookup for chain drill-down).
--
-- Path: A (SugarIndex Attribute in AdoS8Exception.cs) is primary.
-- This SQL file is path B: aidopdev fallback if CodeFirst skips
-- pre-existing tables. Idempotent via pre-check SELECT against
-- INFORMATION_SCHEMA.STATISTICS (MySQL 8 has no CREATE INDEX IF NOT EXISTS).
--
-- Usage:
--   1. Run the 3 pre-check SELECTs below; skip the matching CREATE INDEX
--      when the SELECT returns >0 rows.
--   2. Or run the 3 CREATE INDEX statements directly; MySQL will error
--      "Duplicate key name" if the index already exists, which is safe.
--   3. This file does NOT touch column types, data rows, or other indexes.

-- =========================================================================
-- Pre-check (run first; non-destructive)
-- =========================================================================

-- Pre-check A: idx_s8_exception_related_object_code
SELECT COUNT(*) AS exists_related_object_code
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'ado_s8_exception'
  AND INDEX_NAME = 'idx_s8_exception_related_object_code';

-- Pre-check B: idx_s8_exception_order_flow
SELECT COUNT(*) AS exists_order_flow
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'ado_s8_exception'
  AND INDEX_NAME = 'idx_s8_exception_order_flow';

-- Pre-check C: idx_s8_exception_related_flow
SELECT COUNT(*) AS exists_related_flow
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'ado_s8_exception'
  AND INDEX_NAME = 'idx_s8_exception_related_flow';

-- =========================================================================
-- Index creation (run each only when its pre-check returned 0)
-- =========================================================================

-- Index 1: order-by-order aggregation (exceptionCount per order_code).
-- Columns: tenant_id, factory_id, related_object_code, is_deleted
CREATE INDEX idx_s8_exception_related_object_code
ON ado_s8_exception (tenant_id, factory_id, related_object_code, is_deleted);

-- Index 2: order-flow-level filter (chain page baseline / drill-down).
-- Columns: tenant_id, factory_id, order_flow_code, status, is_deleted
CREATE INDEX idx_s8_exception_order_flow
ON ado_s8_exception (tenant_id, factory_id, order_flow_code, status, is_deleted);

-- Index 3: joint key for per-order per-stage lookup.
-- Columns: tenant_id, factory_id, related_object_code, order_flow_code
CREATE INDEX idx_s8_exception_related_flow
ON ado_s8_exception (tenant_id, factory_id, related_object_code, order_flow_code);

-- =========================================================================
-- Post-check (run after creation; expect 3 rows total)
-- =========================================================================

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'ado_s8_exception'
  AND INDEX_NAME IN (
    'idx_s8_exception_related_object_code',
    'idx_s8_exception_order_flow',
    'idx_s8_exception_related_flow'
  )
GROUP BY INDEX_NAME;