-- ============================================================
-- FIX PACKING PHASE 2: Create missing tables
-- ============================================================

-- 1. container_pallets (API references packing_container_pallets but table doesn't exist)
CREATE TABLE IF NOT EXISTS `container_pallets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `container_id` int unsigned NOT NULL,
  `pallet_id` int unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_container_pallet` (`container_id`, `pallet_id`),
  KEY `idx_container` (`container_id`),
  KEY `idx_pallet` (`pallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. carton_barcodes (individual garment barcodes per carton)
CREATE TABLE IF NOT EXISTS `carton_barcodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `carton_id` int unsigned NOT NULL,
  `carton_item_id` int unsigned DEFAULT NULL,
  `barcode` varchar(255) NOT NULL,
  `garment_sku` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size_name` varchar(50) DEFAULT NULL,
  `status` enum('active','scanned','verified','void') NOT NULL DEFAULT 'active',
  `scanned_at` timestamp NULL DEFAULT NULL,
  `verified_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_barcode` (`barcode`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_item` (`carton_item_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. carton_qrcodes (QR code data per carton)
CREATE TABLE IF NOT EXISTS `carton_qrcodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `carton_id` int unsigned NOT NULL,
  `qr_data` text NOT NULL,
  `qr_type` enum('carton_trace','pallet_trace','container_trace','custom') NOT NULL DEFAULT 'carton_trace',
  `encoded_url` varchar(500) DEFAULT NULL,
  `status` enum('generated','printed','scanned') NOT NULL DEFAULT 'generated',
  `scan_count` int unsigned NOT NULL DEFAULT 0,
  `last_scanned_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_carton` (`carton_id`),
  KEY `idx_type` (`qr_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
