-- =====================================================
-- Phase 1: 受注残管理表レポート用インデックス作成
-- 対象ストアドプロシージャ: sp_generate_backorder_report
-- 作成日: 2025-12-15
-- =====================================================
--
-- 【実行前の確認事項】
-- 1. 本番環境では負荷の低い時間帯に実行すること
-- 2. 実行前にバックアップを取得すること
-- 3. 各ALTER TABLE文は個別に実行し、エラーがないか確認すること
--
-- 【期待される効果】
-- - ストアドプロシージャの実行時間を大幅に短縮（7分→1分以下目標）
-- - Table Full Scanの削減
-- - 相関サブクエリの効率化
-- =====================================================

-- ----------------------------------------------------
-- 1. order_details テーブル
-- ----------------------------------------------------

-- 受注残検索用インデックス（Step 1で使用）
-- WHERE句: backorder_qty > 0, product_id絞り込み
-- JOIN: order_id でorder_headersと結合
ALTER TABLE order_details
ADD INDEX idx_od_backorder_search (backorder_qty, product_id, order_id);

-- product_idでのパーティション用（Window関数最適化）
ALTER TABLE order_details
ADD INDEX idx_od_product_id (product_id);

-- ----------------------------------------------------
-- 2. order_headers テーブル
-- ----------------------------------------------------

-- order_detailsとのJOIN + 顧客絞り込み用
ALTER TABLE order_headers
ADD INDEX idx_oh_customer_id (customer_id);

-- パターン①: allocation_policy + shipment_date絞り込み用
ALTER TABLE order_headers
ADD INDEX idx_oh_allocation_policy (allocation_policy, shipment_date);

-- Window関数のORDER BY最適化
ALTER TABLE order_headers
ADD INDEX idx_oh_order_sort (allocation_policy, order_date, slip_number);

-- ----------------------------------------------------
-- 3. purchase_order_details テーブル
-- ----------------------------------------------------

-- 発注残検索用インデックス（Step 2で使用）
-- WHERE句: backorder_qty > 0, product_id絞り込み
ALTER TABLE purchase_order_details
ADD INDEX idx_pod_backorder_search (backorder_qty, product_id, purchase_order_id);

-- product_idでのパーティション用（Window関数最適化）
ALTER TABLE purchase_order_details
ADD INDEX idx_pod_product_id (product_id);

-- 入荷予定日検索用（相関サブクエリ最適化 - 最重要）
-- パターン①: arrival_date DESC、パターン②: arrival_date ASC
ALTER TABLE purchase_order_details
ADD INDEX idx_pod_product_arrival (product_id, public_expected_receipt_date, expected_receipt_date);

-- ----------------------------------------------------
-- 4. purchase_order_headers テーブル
-- ----------------------------------------------------

-- purchase_order_detailsとのJOIN用
-- （PKの id は既にインデックスがあるため追加不要の可能性あり）
-- 念のため明示的に追加
ALTER TABLE purchase_order_headers
ADD INDEX idx_poh_id (id);

-- ----------------------------------------------------
-- 5. products テーブル
-- ----------------------------------------------------

-- order_details/purchase_order_detailsとのJOIN用
ALTER TABLE products
ADD INDEX idx_p_id (id);

-- categoriesテーブルとのJOIN用
ALTER TABLE products
ADD INDEX idx_p_category_lv2 (category_lv2_id);

-- ----------------------------------------------------
-- 6. order_category_sales_reps テーブル
-- ----------------------------------------------------

-- order_headersとのLEFT JOIN + 担当者絞り込み用
ALTER TABLE order_category_sales_reps
ADD INDEX idx_ocsr_order_sales (order_id, sales_rep_id);

-- =====================================================
-- インデックス確認用クエリ
-- =====================================================
-- 作成後に以下で確認できます:
-- SHOW INDEX FROM order_details;
-- SHOW INDEX FROM order_headers;
-- SHOW INDEX FROM purchase_order_details;
-- SHOW INDEX FROM purchase_order_headers;
-- SHOW INDEX FROM products;
-- SHOW INDEX FROM order_category_sales_reps;
