-- ═══════════════════════════════════════════════════════════════════════
-- PHASE 4 — Warehouse Ops Smoothing (replenish / shortage / holds / returns)
--  1. master_items: min_stock + reorder_qty for replenishment thresholds
--  2. outbound_orders: allocation_status + shortage_note for partial allocation
--  3. stock_holds: quality hold / release workflow
--  4. warehouse_returns + warehouse_return_items: reverse logistics
--  Idempotent: safe to re-run (column adds are guarded).
-- ═══════════════════════════════════════════════════════════════════════
START TRANSACTION;

-- ---- 1. master_items replenishment thresholds (guarded add) ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'master_items' AND COLUMN_NAME = 'min_stock');
SET @sql := IF(@col = 0,
  'ALTER TABLE master_items ADD COLUMN min_stock decimal(14,4) NOT NULL DEFAULT 0.0000 AFTER uom_id, ADD COLUMN reorder_qty decimal(14,4) NOT NULL DEFAULT 0.0000 AFTER min_stock',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- 2. outbound_orders allocation status (guarded add) ----
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'outbound_orders' AND COLUMN_NAME = 'allocation_status');
SET @sql := IF(@col = 0,
  'ALTER TABLE outbound_orders ADD COLUMN allocation_status enum(''full'',''partial'',''short'') NOT NULL DEFAULT ''full'' AFTER status, ADD COLUMN shortage_note text NULL AFTER allocation_status',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- 3. stock_holds ----
CREATE TABLE IF NOT EXISTS stock_holds (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned DEFAULT 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,
  item_id int unsigned NOT NULL,
  item_code varchar(50) DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  lot_no varchar(100) DEFAULT NULL,
  batch_no varchar(100) DEFAULT NULL,
  hold_qty decimal(14,4) NOT NULL,
  reason varchar(200) NOT NULL,
  severity enum('advisory','hold') NOT NULL DEFAULT 'hold',
  reference_type varchar(50) DEFAULT NULL,
  reference_id int unsigned DEFAULT NULL,
  reference_no varchar(100) DEFAULT NULL,
  status enum('held','released','cancelled') NOT NULL DEFAULT 'held',
  held_by int unsigned DEFAULT NULL,
  held_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  released_by int unsigned DEFAULT NULL,
  released_at datetime DEFAULT NULL,
  release_notes varchar(200) DEFAULT NULL,
  notes text,
  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_holds_company (company_id),
  KEY idx_holds_status (status),
  KEY idx_holds_item (item_id),
  KEY idx_holds_bin (bin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 4. warehouse_returns + items ----
CREATE TABLE IF NOT EXISTS warehouse_returns (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  warehouse_id int unsigned NOT NULL,
  return_no varchar(50) NOT NULL,
  source_type enum('customer','internal','supplier') NOT NULL DEFAULT 'customer',
  source_name varchar(200) DEFAULT NULL,
  reference_no varchar(100) DEFAULT NULL,
  received_by int unsigned DEFAULT NULL,
  received_at datetime DEFAULT NULL,
  status enum('received','inspected','restocked','scrapped','cancelled') NOT NULL DEFAULT 'received',
  notes text,
  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_return_no (return_no, company_id),
  KEY idx_returns_company (company_id),
  KEY idx_returns_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warehouse_return_items (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  return_id int unsigned NOT NULL,
  item_id int unsigned NOT NULL,
  item_code varchar(50) DEFAULT NULL,
  color varchar(100) DEFAULT NULL,
  size varchar(100) DEFAULT NULL,
  lot_no varchar(100) DEFAULT NULL,
  batch_no varchar(100) DEFAULT NULL,
  uom_id int unsigned NOT NULL DEFAULT 1,
  qty decimal(14,4) NOT NULL,
  item_condition enum('good','damaged','expired','defective') NOT NULL DEFAULT 'good',
  disposition enum('pending','restock','scrap','quarantine') NOT NULL DEFAULT 'pending',
  restock_bin_id int unsigned DEFAULT NULL,
  inspected_by int unsigned DEFAULT NULL,
  inspected_at datetime DEFAULT NULL,
  notes text,
  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_ret_items (return_id),
  CONSTRAINT fk_ret_items FOREIGN KEY (return_id) REFERENCES warehouse_returns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- 5. Demo data ----

-- Replenishment thresholds on fabric items
UPDATE master_items SET min_stock = 150.0000, reorder_qty = 100.0000 WHERE code = 'FAB-001';
UPDATE master_items SET min_stock = 100.0000, reorder_qty = 100.0000 WHERE code = 'FAB-002';
UPDATE master_items SET min_stock = 1000.0000, reorder_qty = 500.0000 WHERE code = 'ACC-001';

-- Reserve pick-face source stock: FAB-002 in bin A02-02 (id 26), qty 500
INSERT INTO warehouse_stock (company_id, warehouse_id, bin_id, item_id, uom_id, qty, reserved_qty, blocked_qty, status)
SELECT 1, 1, b.id, i.id, i.uom_id, 500.0000, 0, 0, 'available'
FROM warehouse_bins b JOIN master_items i ON i.code = 'FAB-002'
WHERE b.bin_code = 'A02-02'
  AND NOT EXISTS (SELECT 1 FROM warehouse_stock ws WHERE ws.bin_id = b.id AND ws.item_id = i.id);

UPDATE warehouse_bins b
JOIN master_items i ON i.code = 'FAB-002'
SET b.current_qty = 500.0000, b.current_item_id = i.id, b.is_occupied = 1
WHERE b.bin_code = 'A02-02';

-- QA hold: 50 rolls of FAB-002 blocked in A02-01 (damaged during pick)
INSERT INTO stock_holds (company_id, warehouse_id, zone_id, rack_id, bin_id, bin_code, item_id, item_code, hold_qty, reason, severity, status, held_by, held_at)
SELECT 1, 1, b.zone_id, b.rack_id, b.id, b.bin_code, i.id, i.code, 50.0000,
       'Damaged rolls found during pick - QA hold pending review', 'hold', 'held', 1, NOW()
FROM warehouse_bins b JOIN master_items i ON i.code = 'FAB-002'
WHERE b.bin_code = 'A02-01'
  AND NOT EXISTS (SELECT 1 FROM stock_holds sh WHERE sh.bin_id = b.id AND sh.item_id = i.id AND sh.status = 'held');

UPDATE warehouse_stock ws
JOIN warehouse_bins b ON b.id = ws.bin_id
SET ws.blocked_qty = 50.0000, ws.status = 'blocked'
WHERE b.bin_code = 'A02-01' AND ws.item_id = (SELECT id FROM master_items WHERE code = 'FAB-002');

-- Demo customer return: 10 rolls good FAB-001 + 5 rolls damaged FAB-002, received & pending inspection
INSERT INTO warehouse_returns (company_id, warehouse_id, return_no, source_type, source_name, reference_no, received_by, received_at, status, notes)
SELECT 1, 1, 'RMA-2026-00001', 'customer', 'H&M Hennes & Mauritz', 'OBD-2026-00002', 1, NOW(), 'received', 'Customer return from export shipment - awaiting inspection'
WHERE NOT EXISTS (SELECT 1 FROM warehouse_returns WHERE return_no = 'RMA-2026-00001');

INSERT INTO warehouse_return_items (company_id, return_id, item_id, item_code, uom_id, qty, item_condition, disposition)
SELECT 1, r.id, i.id, i.code, i.uom_id, 10.0000, 'good', 'pending'
FROM warehouse_returns r JOIN master_items i ON i.code = 'FAB-001'
WHERE r.return_no = 'RMA-2026-00001'
  AND NOT EXISTS (SELECT 1 FROM warehouse_return_items wri WHERE wri.return_id = r.id AND wri.item_id = i.id);

INSERT INTO warehouse_return_items (company_id, return_id, item_id, item_code, uom_id, qty, item_condition, disposition)
SELECT 1, r.id, i.id, i.code, i.uom_id, 5.0000, 'damaged', 'pending'
FROM warehouse_returns r JOIN master_items i ON i.code = 'FAB-002'
WHERE r.return_no = 'RMA-2026-00001'
  AND NOT EXISTS (SELECT 1 FROM warehouse_return_items wri WHERE wri.return_id = r.id AND wri.item_id = i.id);

COMMIT;
