-- ============================================================
-- FashCognitive - ASN -> Putaway Migration (Phase 5)
-- Tracks putaway of received asn_units into warehouse bins and
-- links each unit to its generated inventory serial.
-- ============================================================

-- 1. Add putaway columns to asn_units
SELECT COUNT(*) INTO @col_ps FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'asn_units' AND COLUMN_NAME = 'putaway_status';
SET @sql = IF(@col_ps = 0,
  'ALTER TABLE `asn_units`
    ADD COLUMN `zone_id` int unsigned DEFAULT NULL AFTER `status`,
    ADD COLUMN `rack_id` int unsigned DEFAULT NULL AFTER `zone_id`,
    ADD COLUMN `bin_id` int unsigned DEFAULT NULL AFTER `rack_id`,
    ADD COLUMN `location_code` varchar(100) DEFAULT NULL AFTER `bin_id`,
    ADD COLUMN `putaway_status` enum(''pending'',''suggested'',''put_away'') NOT NULL DEFAULT ''pending'' AFTER `location_code`,
    ADD COLUMN `putaway_at` datetime DEFAULT NULL AFTER `putaway_status`,
    ADD COLUMN `putaway_by` int unsigned DEFAULT NULL AFTER `putaway_at`,
    ADD COLUMN `inventory_serial_id` int unsigned DEFAULT NULL AFTER `putaway_by`,
    ADD KEY `idx_au_putaway` (`bin_id`),
    ADD KEY `idx_au_serial` (`inventory_serial_id`)',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
