-- ============================================================
-- FashCognitive Apparel MES - Phase 6: Production Logistics
-- (Level 5 of the MES: Logistics Execution)
--
-- The logistics layer does NOT need new operational tables: it
-- aggregates the existing outbound pipeline that was built across
-- the packing / FG-receipt / warehouse modules:
--
--   production_orders
--     -> finished_goods_receipts / finished_goods_receipt_items
--       -> packing_orders / packing_order_lines / cartons
--         -> dispatch_notes / dispatch_note_items
--           -> shipment_manifests / shipment_manifest_items
--
-- and tracks buyer-PO fulfillment against master_buyer_po_shipments
-- (the scheduled shipment lines for a PO).
--
-- This migration only adds the supporting indexes that the
-- Production Logistics Execution Console
-- (api/manufacturing/logistics.php) relies on.
-- ============================================================

-- Join manifest items back to production orders / FG receipts.
-- (shipment_manifest_items already has indexes on manifest_id,
--  dispatch_note_id, packing_order_id, finished_goods_receipt_id,
--  and factory_id; production_order_id was left unindexed.)
SET @db_name = DATABASE();

SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'shipment_manifest_items'
      AND INDEX_NAME = 'idx_mi_prod_order') = 0,
  'ALTER TABLE shipment_manifest_items ADD KEY idx_mi_prod_order (production_order_id)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Speed up carton rollups per packing order (packed/sealed cartons).
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'cartons'
      AND INDEX_NAME = 'idx_carton_status') = 0,
  'ALTER TABLE cartons ADD KEY idx_carton_status (packing_order_id, status)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
