-- ═══════════════════════════════════════════════════════════════════════
-- FashCognitive Digital Warehouse Migration — Phase 2 (Outbound slice)
-- AI Wave/Pick planning + Picking + Staging + Outbound Dock/Load + Dispatch
-- + Warehouse Optimization (bin utilization, re-slotting, put-away learning).
-- Builds on Phase 1: inventory_serials, rack_allocations, camera_events,
--   inventory_traceability, warehouse_stock/movements, stock_ledger, bins.
-- Convention: company_id scoped, utf8mb4_unicode_ci, enums, timestamps.
-- ═══════════════════════════════════════════════════════════════════════

START TRANSACTION;

-- ═══════════════════════════════════════════════════════════════════════
-- 1. OUTBOUND ORDERS (standalone EWM-style demand)
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS outbound_orders (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  outbound_no varchar(50) NOT NULL,
  order_type enum('customer','transfer','material_issue','fg','return') NOT NULL DEFAULT 'customer',
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  ref_no varchar(100) DEFAULT NULL,
  customer_id int unsigned DEFAULT NULL,
  warehouse_id int unsigned NOT NULL,
  destination varchar(255) DEFAULT NULL,
  expected_qty decimal(14,4) DEFAULT NULL,
  expected_cartons int unsigned DEFAULT NULL,
  priority enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  status enum('draft','planned','picking','staged','loading','dispatched','cancelled') NOT NULL DEFAULT 'draft',
  planned_dispatch_at datetime DEFAULT NULL,
  notes text DEFAULT NULL,
  created_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_obd_no (outbound_no, company_id),
  KEY idx_obd_status (status),
  KEY idx_obd_type (order_type),
  KEY idx_obd_ref (ref_type, ref_id),
  KEY idx_obd_company (company_id),
  CONSTRAINT fk_obd_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  CONSTRAINT fk_obd_customer FOREIGN KEY (customer_id) REFERENCES master_buyers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outbound_order_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  outbound_order_id int unsigned NOT NULL,
  inventory_serial_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  uom_id int unsigned DEFAULT NULL,
  status enum('pending','picked','staged','loaded','dispatched','cancelled') NOT NULL DEFAULT 'pending',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ooi_order (outbound_order_id),
  KEY idx_ooi_serial (inventory_serial_id),
  KEY idx_ooi_status (status),
  KEY idx_ooi_company (company_id),
  CONSTRAINT fk_ooi_order FOREIGN KEY (outbound_order_id) REFERENCES outbound_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_ooi_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 2. PICK WAVES + TASKS
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS pick_waves (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  wave_no varchar(50) NOT NULL,
  warehouse_id int unsigned NOT NULL,
  status enum('created','released','picking','completed','cancelled') NOT NULL DEFAULT 'created',
  priority enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  total_tasks int unsigned NOT NULL DEFAULT 0,
  completed_tasks int unsigned NOT NULL DEFAULT 0,
  ai_plan json DEFAULT NULL,
  created_by int unsigned DEFAULT NULL,
  released_at datetime DEFAULT NULL,
  completed_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_wave_no (wave_no, company_id),
  KEY idx_wave_status (status),
  KEY idx_wave_company (company_id),
  CONSTRAINT fk_wave_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pick_wave_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  wave_id int unsigned NOT NULL,
  outbound_order_id int unsigned DEFAULT NULL,
  outbound_order_item_id int unsigned DEFAULT NULL,
  inventory_serial_id int unsigned DEFAULT NULL,
  item_id int unsigned DEFAULT NULL,
  qty decimal(14,4) NOT NULL DEFAULT 0.0000,
  bin_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  pick_sequence int unsigned DEFAULT NULL,
  status enum('pending','picked','cancelled') NOT NULL DEFAULT 'pending',
  camera_verified tinyint(1) NOT NULL DEFAULT 0,
  verified_at datetime DEFAULT NULL,
  picked_by int unsigned DEFAULT NULL,
  picked_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pwi_wave (wave_id),
  KEY idx_pwi_serial (inventory_serial_id),
  KEY idx_pwi_status (status),
  KEY idx_pwi_company (company_id),
  CONSTRAINT fk_pwi_wave FOREIGN KEY (wave_id) REFERENCES pick_waves(id) ON DELETE CASCADE,
  CONSTRAINT fk_pwi_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 3. STAGING + OUTBOUND DOCK + DISPATCH DOCUMENT
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS staging_locations (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  code varchar(30) NOT NULL,
  capacity decimal(14,4) DEFAULT NULL,
  notes varchar(255) DEFAULT NULL,
  is_active tinyint(1) NOT NULL DEFAULT 1,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_stg_code (code, warehouse_id),
  KEY idx_stg_company (company_id),
  CONSTRAINT fk_stg_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS staging_assignments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  staging_location_id int unsigned DEFAULT NULL,
  outbound_order_id int unsigned DEFAULT NULL,
  inventory_serial_id int unsigned DEFAULT NULL,
  status enum('assigned','staged','released') NOT NULL DEFAULT 'assigned',
  assigned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  staged_at datetime DEFAULT NULL,
  released_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sa_location (staging_location_id),
  KEY idx_sa_order (outbound_order_id),
  KEY idx_sa_serial (inventory_serial_id),
  KEY idx_sa_status (status),
  KEY idx_sa_company (company_id),
  CONSTRAINT fk_sa_location FOREIGN KEY (staging_location_id) REFERENCES staging_locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_sa_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outbound_dock_assignments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  outbound_order_id int unsigned NOT NULL,
  dock_no varchar(20) NOT NULL,
  warehouse_id int unsigned NOT NULL,
  status enum('assigned','loading','complete','cancelled') NOT NULL DEFAULT 'assigned',
  assigned_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at datetime DEFAULT NULL,
  ended_at datetime DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_oda_order (outbound_order_id),
  KEY idx_oda_status (status),
  KEY idx_oda_company (company_id),
  CONSTRAINT fk_oda_order FOREIGN KEY (outbound_order_id) REFERENCES outbound_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_oda_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outbound_documents (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  doc_no varchar(50) NOT NULL,
  outbound_order_id int unsigned NOT NULL,
  vehicle_no varchar(50) DEFAULT NULL,
  driver_name varchar(255) DEFAULT NULL,
  driver_phone varchar(50) DEFAULT NULL,
  transporter_name varchar(255) DEFAULT NULL,
  container_no varchar(50) DEFAULT NULL,
  seal_no varchar(50) DEFAULT NULL,
  total_qty decimal(14,4) DEFAULT NULL,
  total_cartons int unsigned DEFAULT NULL,
  status enum('draft','dispatched','in_transit','delivered','cancelled') NOT NULL DEFAULT 'draft',
  dispatched_at datetime DEFAULT NULL,
  gate_pass_no varchar(50) DEFAULT NULL,
  eway_bill_no varchar(50) DEFAULT NULL,
  notes text DEFAULT NULL,
  created_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_odoc_no (doc_no, company_id),
  KEY idx_odoc_order (outbound_order_id),
  KEY idx_odoc_status (status),
  KEY idx_odoc_company (company_id),
  CONSTRAINT fk_odoc_order FOREIGN KEY (outbound_order_id) REFERENCES outbound_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 4. OPTIMIZATION (bin utilization, re-slotting, put-away learning)
-- ═══════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bin_utilization_snapshot (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  zone_id int unsigned DEFAULT NULL,
  rack_id int unsigned DEFAULT NULL,
  bin_id int unsigned DEFAULT NULL,
  bin_code varchar(30) DEFAULT NULL,
  capacity decimal(14,4) DEFAULT NULL,
  used decimal(14,4) DEFAULT NULL,
  occupancy_pct decimal(6,2) DEFAULT NULL,
  captured_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bus_zone (zone_id),
  KEY idx_bus_rack (rack_id),
  KEY idx_bus_bin (bin_id),
  KEY idx_bus_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS re_slot_suggestions (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  inventory_serial_id int unsigned DEFAULT NULL,
  from_bin_id int unsigned DEFAULT NULL,
  from_bin_code varchar(30) DEFAULT NULL,
  to_bin_id int unsigned DEFAULT NULL,
  to_bin_code varchar(30) DEFAULT NULL,
  reason varchar(255) DEFAULT NULL,
  score decimal(6,2) DEFAULT NULL,
  status enum('suggested','applied','dismissed') NOT NULL DEFAULT 'suggested',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_rs_serial (inventory_serial_id),
  KEY idx_rs_status (status),
  KEY idx_rs_company (company_id),
  CONSTRAINT fk_rs_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS putaway_feedback (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  allocation_id int unsigned DEFAULT NULL,
  inventory_serial_id int unsigned DEFAULT NULL,
  suggested_zone_id int unsigned DEFAULT NULL,
  suggested_bin_id int unsigned DEFAULT NULL,
  ai_score decimal(5,2) DEFAULT NULL,
  outcome enum('confirmed','skipped','cancelled') NOT NULL DEFAULT 'confirmed',
  decision_seconds int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pf_alloc (allocation_id),
  KEY idx_pf_serial (inventory_serial_id),
  KEY idx_pf_outcome (outcome),
  KEY idx_pf_company (company_id),
  CONSTRAINT fk_pf_alloc FOREIGN KEY (allocation_id) REFERENCES rack_allocations(id) ON DELETE SET NULL,
  CONSTRAINT fk_pf_serial FOREIGN KEY (inventory_serial_id) REFERENCES inventory_serials(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════
-- 5. EXTEND EXISTING ENUMS + TABLES (additive)
-- ═══════════════════════════════════════════════════════════════════════

ALTER TABLE inventory_traceability
  MODIFY COLUMN station enum('supplier','po','asn','gate','dock','grn','inspection','putaway','issue','production','fg','packing','shipment','pick','staged','loaded','dispatched') NOT NULL;

ALTER TABLE camera_events
  MODIFY COLUMN event_type enum('vehicle_ocr','barcode_detected','qr_detected','carton_count','damage_detected','wrong_rack','wrong_label','seal_check','forklift_verified','loading_sequence','pick_confirm','load_confirm','dispatch_confirm') NOT NULL DEFAULT 'qr_detected';

ALTER TABLE stock_ledger
  MODIFY COLUMN movement_type enum('purchase_in','transfer_in','transfer_out','issue','return','adjustment','consumption','qc_hold','qc_release','reservation','unreservation','dispatch') NOT NULL;

-- ═══════════════════════════════════════════════════════════════════════
-- 6. DEMO SEED
-- ═══════════════════════════════════════════════════════════════════════

-- Staging locations (warehouse 1)
INSERT INTO staging_locations (company_id, warehouse_id, code, capacity, notes)
SELECT 1, 1, v.code, v.capacity, v.notes
FROM (SELECT 'STG-01' code, 500 capacity, 'Export staging bay 1' notes
      UNION ALL SELECT 'STG-02', 500, 'Export staging bay 2'
      UNION ALL SELECT 'STG-03', 400, 'Dispatch staging bay 3'
      UNION ALL SELECT 'STG-04', 400, 'Dispatch staging bay 4'
      UNION ALL SELECT 'STG-05', 300, 'Transit staging bay 5'
      UNION ALL SELECT 'STG-06', 300, 'Transit staging bay 6') v
WHERE NOT EXISTS (SELECT 1 FROM staging_locations WHERE warehouse_id = 1 AND code = v.code);

-- Bin utilization snapshot from current bin state
INSERT INTO bin_utilization_snapshot (company_id, warehouse_id, zone_id, rack_id, bin_id, bin_code, capacity, used, occupancy_pct, captured_at)
SELECT b.company_id, b.warehouse_id, b.zone_id, b.rack_id, b.id, b.bin_code,
  b.max_qty, COALESCE(b.current_qty, 0),
  ROUND(COALESCE(b.current_qty, 0) / NULLIF(b.max_qty, 0) * 100, 2),
  NOW()
FROM warehouse_bins b
WHERE b.company_id = 1
  AND NOT EXISTS (SELECT 1 FROM bin_utilization_snapshot s WHERE s.bin_id = b.id AND DATE(s.captured_at) = CURDATE());

-- Demo outbound order (planned) + items for located fabric serials
INSERT INTO outbound_orders (id, company_id, outbound_no, order_type, ref_type, ref_no, customer_id, warehouse_id, destination, expected_qty, priority, status, planned_dispatch_at, notes, created_by)
SELECT 1, 1, 'OBD-2026-00001', 'customer', 'dispatch_notes', 'DSP-2026-0001',
  (SELECT MIN(id) FROM master_buyers WHERE company_id = 1), 1, 'Export CFS, Chittagong',
  (SELECT COALESCE(SUM(qty), 0) FROM inventory_serials s WHERE s.company_id = 1 AND s.id IN (1,2,3)),
  'high', 'planned', DATE_ADD(NOW(), INTERVAL 1 DAY), 'Phase 2 demo outbound order', 1
WHERE NOT EXISTS (SELECT 1 FROM outbound_orders WHERE id = 1);

INSERT INTO outbound_order_items (company_id, outbound_order_id, inventory_serial_id, item_id, qty, status)
SELECT 1, 1, s.id, r.fabric_item_id, s.qty, 'pending'
FROM inventory_serials s
JOIN fabric_rolls r ON r.id = s.ref_id
WHERE s.ref_type = 'fabric_rolls' AND s.id IN (1,2,3)
  AND NOT EXISTS (SELECT 1 FROM outbound_order_items oi WHERE oi.outbound_order_id = 1 AND oi.inventory_serial_id = s.id);

-- Demo pick wave + tasks (created, all pending)
INSERT INTO pick_waves (id, company_id, wave_no, warehouse_id, status, priority, total_tasks, completed_tasks, ai_plan, created_by)
SELECT 1, 1, 'WAV-2026-00001', 1, 'created', 'high', 3, 0,
  JSON_ARRAY(JSON_OBJECT('pick_sequence', 1, 'serial_no', 'FAB-2028-000001', 'bin_code', NULL),
             JSON_OBJECT('pick_sequence', 2, 'serial_no', 'FAB-2028-000002', 'bin_code', NULL),
             JSON_OBJECT('pick_sequence', 3, 'serial_no', 'FAB-2028-000003', 'bin_code', NULL)),
  1
WHERE NOT EXISTS (SELECT 1 FROM pick_waves WHERE id = 1);

INSERT INTO pick_wave_items (company_id, wave_id, outbound_order_id, outbound_order_item_id, inventory_serial_id, item_id, qty, bin_id, bin_code, pick_sequence, status)
SELECT 1, 1, 1, oi.id, s.id, oi.item_id, oi.qty, s.bin_id, s.location_code, v.seq, 'pending'
FROM inventory_serials s
JOIN fabric_rolls r ON r.id = s.ref_id
JOIN outbound_order_items oi ON oi.outbound_order_id = 1 AND oi.inventory_serial_id = s.id
JOIN (SELECT 1 id, 1 seq UNION ALL SELECT 2, 2 UNION ALL SELECT 3, 3) v ON v.id = s.id
WHERE s.ref_type = 'fabric_rolls' AND s.id IN (1,2,3)
  AND NOT EXISTS (SELECT 1 FROM pick_wave_items p WHERE p.wave_id = 1 AND p.inventory_serial_id = s.id);

-- Put-away learning feedback derived from existing allocations
INSERT INTO putaway_feedback (company_id, allocation_id, inventory_serial_id, suggested_zone_id, suggested_bin_id, ai_score, outcome, created_at)
SELECT 1, ra.id, ra.inventory_serial_id, ra.zone_id, ra.bin_id, ra.ai_score, ra.status, ra.updated_at
FROM rack_allocations ra
WHERE ra.company_id = 1 AND ra.status IN ('confirmed','skipped','cancelled')
  AND NOT EXISTS (SELECT 1 FROM putaway_feedback pf WHERE pf.allocation_id = ra.id);

-- Bin-locate demo serials (4,5,6) into FAB-Z1 so occupancy / re-slot demos have real stock
UPDATE inventory_serials SET zone_id = 1, rack_id = 1, bin_id = 14, location_code = 'A01-01', updated_at = NOW() WHERE id = 4;
UPDATE inventory_serials SET zone_id = 1, rack_id = 2, bin_id = 13, location_code = 'A02-01', updated_at = NOW() WHERE id = 5;
UPDATE inventory_serials SET zone_id = 1, rack_id = 3, bin_id = 12, location_code = 'A03-01', updated_at = NOW() WHERE id = 6;

INSERT INTO warehouse_stock (company_id, warehouse_id, item_id, color, size, uom_id, qty, status, bin_id, zone_id, rack_id)
SELECT 1, 1, s.item_id, s.color, s.size, mi.uom_id, s.qty, 'available', s.bin_id, s.zone_id, s.rack_id
FROM inventory_serials s LEFT JOIN master_items mi ON mi.id = s.item_id
WHERE s.id IN (4,5,6) AND s.bin_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM warehouse_stock ws WHERE ws.bin_id = s.bin_id AND ws.item_id = s.item_id AND ws.status = 'available');

UPDATE warehouse_bins b JOIN inventory_serials s ON s.bin_id = b.id
SET b.is_occupied = 1, b.current_qty = (SELECT COALESCE(SUM(ws.qty), 0) FROM warehouse_stock ws WHERE ws.bin_id = b.id AND ws.status = 'available'),
    b.current_item_id = s.item_id, b.updated_at = NOW()
WHERE s.id IN (4,5,6);

-- Re-slot suggestion sample (serial 6 sits in a non-optimal FAB-Z1 bin)
INSERT INTO re_slot_suggestions (company_id, inventory_serial_id, from_bin_id, from_bin_code, to_bin_id, to_bin_code, reason, score, status)
SELECT 1, 6, 12, 'A03-01', 27, 'A01-02', 'Optimize replenishment distance to packing zone', 86.50, 'suggested'
WHERE NOT EXISTS (SELECT 1 FROM re_slot_suggestions rs WHERE rs.inventory_serial_id = 6);

-- Self-heal: link fabric rolls -> serials -> outbound items to master items (idempotent)
UPDATE fabric_rolls
SET fabric_item_id = CASE WHEN id BETWEEN 1 AND 4 THEN 1 WHEN id BETWEEN 5 AND 8 THEN 2 ELSE 3 END
WHERE fabric_item_id IS NULL;

UPDATE inventory_serials s
JOIN fabric_rolls fr ON fr.id = s.ref_id AND s.ref_type = 'fabric_rolls'
SET s.item_id = fr.fabric_item_id
WHERE s.item_id IS NULL;

UPDATE outbound_order_items oi
JOIN inventory_serials s ON s.id = oi.inventory_serial_id
SET oi.item_id = s.item_id
WHERE oi.item_id IS NULL;

COMMIT;
