CREATE TABLE IF NOT EXISTS `shipments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint UNSIGNED NOT NULL,
  `location_id` bigint UNSIGNED NOT NULL,
  `order_id` bigint UNSIGNED NOT NULL,
  `shipment_no` varchar(80) COLLATE utf8mb4_general_ci NOT NULL,
  `shipping_type` varchar(60) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shipping_detail` text COLLATE utf8mb4_general_ci,
  `shipping_address` text COLLATE utf8mb4_general_ci,
  `receiver_name` varchar(160) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `shipping_cost` decimal(14,2) NOT NULL DEFAULT '0.00',
  `status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending',
  `created_by` bigint UNSIGNED DEFAULT NULL,
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_shipments_order` (`order_id`),
  KEY `idx_shipments_scope` (`tenant_id`,`location_id`,`status`),
  KEY `idx_shipments_no` (`shipment_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `shipment_items` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `shipment_id` bigint UNSIGNED NOT NULL,
  `order_item_id` bigint UNSIGNED NOT NULL,
  `item_name_snapshot` varchar(180) COLLATE utf8mb4_general_ci NOT NULL,
  `variant_name_snapshot` varchar(160) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `qty_snapshot` decimal(14,3) NOT NULL DEFAULT '0.000',
  `item_note_snapshot` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_checked` tinyint(1) NOT NULL DEFAULT '0',
  `checked_at` datetime DEFAULT NULL,
  `checked_by` bigint UNSIGNED DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_shipment_item_order_item` (`shipment_id`,`order_item_id`),
  KEY `idx_shipment_items_scope` (`shipment_id`,`is_checked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;