-- ============================================================
-- FashCognitive - Identity Backbone Migration (Phase A)
-- 1. Opaque ASN secure code (FCASN-...) + opaque ASN QR
-- 2. inventory_serials.parent_id (split / genealogy lineage)
-- 3. inventory_traceability.station enum + 'split'
-- 4. inventory_serial_splits audit table
-- 5. Backfills: ASN secure codes, ITEM-... item identifiers
-- ============================================================

-- ------------------------------------------------------------
-- 1. asn.secure_code — opaque token identifying the shipment
-- ------------------------------------------------------------
SELECT COUNT(*) INTO @col_sc FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'asn' AND COLUMN_NAME = 'secure_code';
SET @sql = IF(@col_sc = 0,
  'ALTER TABLE `asn`
    ADD COLUMN `secure_code` varchar(64) DEFAULT NULL AFTER `passport_qr`,
    ADD UNIQUE KEY `uk_asn_secure_code` (`secure_code`)',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill secure codes (idempotent — only touches NULL/empty rows)
UPDATE asn
SET secure_code = CONCAT('FCASN-', UPPER(LEFT(REPLACE(UUID(), '-', ''), 20)))
WHERE secure_code IS NULL OR secure_code = '';

-- Make already-shipped ASN QR opaque (legacy payloads still resolve via
-- the other dsp-receive lookup branches)
UPDATE asn
SET passport_qr = secure_code
WHERE secure_code IS NOT NULL AND passport_qr IS NOT NULL AND passport_qr NOT LIKE 'FCASN-%';

-- ------------------------------------------------------------
-- 2. inventory_serials.parent_id — split / genealogy lineage
-- ------------------------------------------------------------
SELECT COUNT(*) INTO @col_pid FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_serials' AND COLUMN_NAME = 'parent_id';
SET @sql = IF(@col_pid = 0,
  'ALTER TABLE `inventory_serials`
    ADD COLUMN `parent_id` int unsigned DEFAULT NULL AFTER `id`,
    ADD KEY `idx_serial_parent` (`parent_id`),
    ADD CONSTRAINT `fk_serial_parent` FOREIGN KEY (`parent_id`) REFERENCES `inventory_serials`(`id`) ON DELETE SET NULL',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 3. inventory_traceability.station enum — add 'split'
-- ------------------------------------------------------------
SELECT COUNT(*) INTO @col_split FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'inventory_traceability'
    AND COLUMN_NAME = 'station' AND COLUMN_TYPE LIKE '%split%';
SET @sql = IF(@col_split = 0,
  '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'',''split'') NOT NULL',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- 4. inventory_serial_splits — audit log of split operations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_serial_splits` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `parent_serial_id` int unsigned NOT NULL,
  `child_serial_id` int unsigned NOT NULL,
  `split_qty` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `reason` varchar(255) DEFAULT NULL,
  `split_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_iss_parent` (`parent_serial_id`),
  KEY `idx_iss_child` (`child_serial_id`),
  KEY `idx_iss_company` (`company_id`),
  CONSTRAINT `fk_iss_parent` FOREIGN KEY (`parent_serial_id`) REFERENCES `inventory_serials`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_iss_child` FOREIGN KEY (`child_serial_id`) REFERENCES `inventory_serials`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. Backfill ITEM-... identifiers for items without any
-- ------------------------------------------------------------
SELECT COUNT(*) INTO @dummy FROM item_identifiers; -- warm-up only
SET @max_item_seq := 0;
SELECT COALESCE(MAX(CAST(SUBSTRING(identifier_value, 6) AS UNSIGNED)), 0) INTO @max_item_seq
FROM item_identifiers WHERE identifier_value LIKE 'ITEM-%';

INSERT INTO item_identifiers (company_id, item_id, identifier_type, identifier_value, is_active)
SELECT i.company_id, i.id, v.itype, CONCAT('ITEM-', LPAD(@max_item_seq := @max_item_seq + 1, 8, '0')), 1
FROM master_items i
CROSS JOIN (SELECT 'code128' AS itype UNION ALL SELECT 'qrcode') v
WHERE NOT EXISTS (SELECT 1 FROM item_identifiers idn WHERE idn.item_id = i.id)
  AND @max_item_seq < 99000000;

SET @dummy = 0;
