-- ============================================================
-- FashCognitive - ASN Digital Shipment Passport (DSP) Migration
-- Phase 1: passport id, unit-level QR tracking, scan log,
-- over-shipment exceptions, approvals, supplier bridge.
-- ============================================================

-- -----------------------------------------------------------
-- 1. Add passport columns to asn table
-- -----------------------------------------------------------
SELECT COUNT(*) INTO @col_exists FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'asn' AND COLUMN_NAME = 'passport_id';
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `asn`
    ADD COLUMN `passport_id` varchar(50) DEFAULT NULL AFTER `asn_no`,
    ADD COLUMN `passport_qr` text DEFAULT NULL AFTER `passport_id`,
    ADD COLUMN `source` varchar(20) NOT NULL DEFAULT ''internal'' AFTER `passport_qr`',
  'SET @dummy = 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------
-- 2. ASN Units (per-unit QR passport for cartons / rolls / pieces)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_units` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `asn_item_id` int unsigned DEFAULT NULL,
  `unit_no` varchar(50) NOT NULL,
  `qr_code` varchar(500) DEFAULT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `unit_type` enum('carton','roll','piece','pallet') NOT NULL DEFAULT 'piece',
  `carton_no` varchar(50) DEFAULT NULL,
  `lot_no` varchar(50) DEFAULT NULL,
  `batch_no` varchar(50) DEFAULT NULL,
  `status` enum('generated','packed','in_transit','arrived','received','rejected') NOT NULL DEFAULT 'generated',
  `scanned_count` int unsigned NOT NULL DEFAULT 0,
  `last_scanned_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_au_asn` (`asn_id`),
  KEY `idx_au_asnitem` (`asn_item_id`),
  KEY `idx_au_qr` (`qr_code`(100)),
  UNIQUE KEY `uq_au_asn_unit` (`asn_id`, `unit_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 3. ASN Scan Log (gate / warehouse / supplier unit scans)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_scans` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `unit_id` int unsigned DEFAULT NULL,
  `asn_item_id` int unsigned DEFAULT NULL,
  `scan_type` varchar(50) NOT NULL DEFAULT 'unit',
  `scan_value` varchar(500) NOT NULL,
  `location` varchar(100) DEFAULT NULL,
  `device` varchar(100) DEFAULT NULL,
  `scanned_by` int unsigned DEFAULT NULL,
  `scanned_by_name` varchar(255) DEFAULT NULL,
  `result` enum('matched','unknown','duplicate','rejected') NOT NULL DEFAULT 'matched',
  `notes` varchar(255) DEFAULT NULL,
  `scanned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_as_asn` (`asn_id`),
  KEY `idx_as_unit` (`unit_id`),
  KEY `idx_as_scan` (`scan_value`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 4. ASN Exceptions (over-shipment, quantity mismatches, etc.)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_exceptions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `exception_type` varchar(50) NOT NULL DEFAULT 'over_shipment',
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  `message` varchar(255) NOT NULL,
  `detail` json DEFAULT NULL,
  `reference_value` decimal(16,4) DEFAULT NULL,
  `shipped_value` decimal(16,4) DEFAULT NULL,
  `tolerance_pct` decimal(8,2) DEFAULT NULL,
  `status` enum('open','approved','rejected','auto_resolved') NOT NULL DEFAULT 'open',
  `resolution_note` varchar(255) DEFAULT NULL,
  `resolved_by` int unsigned DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ae_asn` (`asn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 5. ASN Approvals (workflow for flagged exceptions)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `exception_id` int unsigned DEFAULT NULL,
  `approval_level` int NOT NULL DEFAULT 1,
  `role_name` varchar(50) DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `approver_name` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `comments` text,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ap_asn` (`asn_id`),
  KEY `idx_ap_exc` (`exception_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 6. Bridge supplier_profiles -> master_vendors
--    Links the quality supplier portal profile to the
--    procurement master vendor record (used for PO/ASN access).
-- -----------------------------------------------------------
SELECT COUNT(*) INTO @col_exists2 FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'supplier_profiles' AND COLUMN_NAME = 'master_vendor_id';
SET @sql2 = IF(@col_exists2 = 0,
  'ALTER TABLE `supplier_profiles`
    ADD COLUMN `master_vendor_id` int unsigned DEFAULT NULL AFTER `company_id`,
    ADD KEY `idx_sp_master_vendor` (`master_vendor_id`)',
  'SET @dummy = 0');
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
